Re: Improving performance of queries

On 5 Apr 2006 09:09:04 -0700, "News" <Contact_404_at_hotmail.com> 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 ?

I don't think so. This is the most efficient plan for the given query. The DISTINCT however is redundant, as MINUS is a SET operations and a set can't have duplicates.
This will remove 1 sort unique from the plan.

--
Sybrand Bakker, Senior Oracle DBA