Re: performance strategies for many joins

Steve Wicklund (savant_7_at_hotmail.com) wrote:
: hello all;
: I have utility being written that must join seven table in order to
: produce a report-style file out on oracle 8. One table is joined with
: seven others to bring in a siginifact amount of conected data such as
: the customers phone, address, etc. Also, the 'main' table as it were,
: must be Unioned with nearly identicle table that contains older
: records. My question is this. Are there any 'general' strategies for
: improving the performance of the query? I am not looking for someone
: to write the sql again, just some overall concepts that can be adhered
: to that tend to help with speed of execution. The design of the schema
: is not up for change, so that is not an option. All of the columns
: involved in the join have indexes. Just looking for a few 10,000 foot
: level concepts for making a query smarter/faster.

My first general rule of thumb is that you must be able to join the tables yourself using paper and pencil. In other words, if you can't plot out how to start with one table, and then access the data from the next table to merge the data efficiently yourself, then you can't assume that oracle can do any better (maybe it can, you just shouldn't depend on it).

A simple way that you could join tables if you had to do it by hand is to find the table that will give you the least amount of data and extract that data however you can (i.e. indexes based on the where clauses), but making sure the order of the rows corresponds to the ordering of the index on the next table. That allows you to scan the index of the second table as a merge, thereby efficiently finding the indexes you need (and hence the entire row). If all the data you need is in the index then of course you don't even need to look into the second table.

Based on the above observation of one possible strategy, another rule of thumb is that you want an index on a table to include _all_ the columns needed for a join (i.e. multiple indexes don't help, a single multi-column index does), with the columns of that index ordered so that the first columns are the ones you expect to restrict the data quickly, either because of the data in the table or because of the where clauses. (And if you don't include all the columns in the index, then at least include the ones that should be first in the list.)

Of the two tables being joined, only one of the multi-column "joining" indexes is used in the above description so only one table needs such an index, but if both have one then oracle gets to choose which ever one is best (assuming it doesn't have a better strategy).

Ask oracle what it actually did to see which if any of the indexes were useful, or if it is doing something unexpected then you can maybe think of a way to help oracle do that thing even better, such as trying an index on a column that oracle is looking at to help it restrict the data in that column or changing the ordering in a multi-column index to reflect the order that oracle is accessing the data (and then check if oracle cares)

Don't forget to analyse the tables and indexes. Received on Fri Feb 07 2003 - 18:39:52 CST