Re: left outer join with full table scan

m.fangtao_at_genesis.co.nz a écrit :

> Thanks. We run Oracle 9205 on HP-UX (4 CPUs), no partitioned table. It
> runs in a batch and take about 1 mins. It's generated from Peoplesoft
> cystal report.

Hello,

Thank you for providing feedback about the context. At first sight, one minute is not that bad for a query on those big tables.
Your job is to ensure the stability of the exec plan to avoid exponential increase of the exec time along with the growth of the tables.
Try to make some mviews to precalculate expensive joins and enable query rewrite.
Keep on considering partitioning option also. Do you use the Entreprise Edition ?

For plan stability, there is the outline stuff. I've never used it myself but you might have a look to it.

Regards.

-- 
SL