Improving performance of queries

From: News <Contact_404_at_hotmail.com>
Date: 5 Apr 2006 09:09:04 -0700
Message-ID: <1144253344.901327.163450@g10g2000cwb.googlegroups.com>

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 ? Received on Wed Apr 05 2006 - 11:09:04 CDT