Re: Improving performance of queries

News wrote:
> Most of process time of ETL Jobs is due to simple minus queries against
> partitioned tables like this
>
> SELECT DISTINCT x
> FROM A
> MINUS
> SELECT y
> FROM B
>
> SELECT STATEMENT
> 0 6475
> MINUS
> 1 0 1
> SORT UNIQUE
> 2 1 1
> PARTITION RANGE ALL
> 3 2 1
> INDEX FAST FULL SCAN A_PK1
> 4 3 1
> SORT UNIQUE
> 5 1 2
> INDEX FAST FULL SCAN B_PK
> 6 5 1
>
> 6475 SELECT STATEMENT Cost= 6475
> 1 MINUS
> 1 SORT UNIQUE
> 1 PARTITION RANGE ALL
> 1 INDEX FAST FULL SCAN A_PK1
> 2 SORT UNIQUE
> 1 INDEX FAST FULL SCAN B_PK
>
> Such query takes 15 mn to run. There are lot of them. Is there a way to
> rewrite such query or improve plan in 10gr2 under aix 5.2 ?

Is there a way to rewrite the query? No quarantee that will help but having choices is a good thing.

select a.*
from a,b
where a.a_pk1=b.b_pk1(+) and b.b_pk1 is null;

select a.*
from a
where not exists (select 1 from b where b.b_pk1=a.a_pk1 ) ;

Check the plan and performances of those versions.

Ed
As Tim "the toolman" Taylor might say: more tools is always a good thing. Received on Wed Apr 05 2006 - 14:17:29 CDT