Re: performance strategies for many joins

Steve Wicklund 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.
>
> Thanks!

I would look at the possibility of using a materialized view to merge some of those tables into a single indexed entity.

I would definitely run explain plan and I would definitely try a minimum of six different strategies for retrieving the answer. One thing I have found of interest is that our normal gut instincts are often wrong. For example most people would not guess which of these is the faster query:

SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT DISTINCT srvr_id
FROM servers
WHERE srvr_id NOT IN (

   SELECT srvr_id
   FROM servers
   MINUS
   SELECT srvr_id
   FROM serv_inst);

Daniel Morgan Received on Fri Jan 31 2003 - 10:56:59 CST