Re: performance strategies for many joins

savant_7_at_hotmail.com (Steve Wicklund) wrote in message news:<5de96e4e.0301310552.6659fb89_at_posting.google.com>...
> 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.
>
> Thanks!

Well since you have indexes to support the join conditions try listing the tables in the FROM clause in desired join order and referencing the tables in the where clause conditions in the same order. This way if Oracle does not join the tables in the desired order and what the CBO chose does not appear to be superior then an ORDERED hint will probably adjust the optimizer's logic to the desired result.

HTH -- Mark D Powell -- Received on Fri Jan 31 2003 - 15:08:44 CST