Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: performance approach PL/sql ?

Re: performance approach PL/sql ?

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 7 Apr 2002 20:43:07 -0500
Message-ID: <ubscun6fg.fsf@rcn.com>


On 5 Apr 2002, nandagopalj_at_hotmail.com wrote:
> I have 20 oracle tables containing in excess of million rows each. I
> have to write a query that joins the 20 tables. The query generates
> summary information from all the 20 tables. I have a driving table
> and join its key to some of the 19 tables and their key to other
> tables and so on.

The driving table's join column/s are different for each of the tables it is joining to?

If they aren't, then why were the tables being joined to separated?

> I have to do this for all the million records in driving table.

> I plan to write a PL/SQL that basically performs a join on all the
> records and associates the row IDs of the 19 tables with the driving
> table's key and store the relationships in a new oracle table. Once
> this script completes, I can just run my SQL off of the relationship
> table and get the info with a direct row id based query for all 19
> tables. This will improve performance of extracting all the 20 tables'
> data.

Since you are scanning driving table, an index on each of the other 19 tables would probably produce a rowid lookup to each one of those tables by using a nested loops join, which is close to what your approach's query plan will probably produce. Your query plan should see that the 19 other table's index scans are not part of its plan, so your way should be speedier than using an index scan per table combined with a rowid lookup. Your way is basically building your own little home grown index, with the added benefit of prejoined data, therefore no index scan to get to the rowid.

> Your opinions on this approach is highly appreciated..
>
> Nandu.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.
Received on Sun Apr 07 2002 - 20:43:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US