Re: performance approach PL/sql ?

How often do you need to perform this report? Does the data need to be real-time to the hundredth of a second or can there be some latency? If you have any flexibility ... consider a materialized view.

Or a re-design. A query that requires 20 tables likely indicates a need for a better architecture. (Hey I said likely).

Daniel Morgan

Nan 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.
> 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.
>
> Your opinions on this approach is highly appreciated..
>
> Nandu.
Received on Fri Apr 05 2002 - 15:11:09 CST