File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Improving performance of view  Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Improving performance of view  " Watch "Improving performance of view  " New topic

Improving performance of view

Parameswaran Thangavel
Ranch Hand

Joined: Mar 01, 2005
Posts: 485
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.
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: Improving performance of view
It's not a secret anymore!