Re: Improving performance of queries

On Wed, 05 Apr 2006 19:14:15 +0200, Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:

>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.

What about parallel queries and looking into IO? OP didn't state how big the tables are.

.......
We run Oracle 9.2.0.6 on RH4 AMD
remove NSPAM to email Received on Wed Apr 05 2006 - 13:44:36 CDT