Hi all I liked to have pointers on improving the performance of view. And also i liked to know to which extent indexing the table will help in improving the performance of view. Say like Table1, Table2. The view is created based on Table1 and Table2.
so whether i need to index the table for each and every combination of column or single index including all the columns will be sufficient. i.e say Table1 has 3 col, Col1, Col2 and Col3. so indexing the Table1 on Create index1 on Table1 (col1) Create index2 on Table1 (col1, col2) Create index3 on Table1 (col1,col2, col3)
will having the index like above will improve the view performance. or just the index3 having col1, col2 and col3 is enough.
Please note that i need to create view for 5 tables, each table with 7 column and combination of all the seven are considered unique. and also i had a million of records to return.
Parameswaran, It doesn't really make sense to talk about the overall performance of a view. Similarly, it doesn't make sense to talk about the overall performance of a table because you can't optimize all scenarios simultaneously. Making queries faster makes updates slower due to the extra work in maintaining the index. This may or may not be significant. But it needs to be considered.
So what are you trying to optimize? A specific query? Try writing out the query without the view and see what fields are being accessed or joined. Those fields would benefit from an index.