CODEWITHSUNDEEP
oracle-databaseperformance
Arnav
Arnav

Reputation: 11

Oracle performing full table scan

I have a query which joins two tables ISITEMFULL and Transaction (It actually searches for all the transactions in ISITEMFULL table whose invoice_date is not null, while invoice_date is defined in transaction table only.

SELECT MIN(T.TRANSACTION) TRANSACTION ,
ISIL.ITEMID ITEM_NUMBER,
ISIL.ITEMCOVER POLICY_NUMBER 
FROM TRANSACTION T,ISITEMFULL ISIL 
WHERE ISIL.SENT_FLG='T' 
AND T.TRANSACTION=ISIL.TRANSACTION
AND T.INVOICE_DAT IS NOT NULL 
GROUP BY ISIL.ITEMID,ISIL.ITEMCOVER;

Now, the size of ISITEMFULL is relatively smaller (3 million records) than TRANSACTION table, which has billions of records. But if I look for plan, it performs full scan of TRANSACTION table. Due to this, the query is taking a lot of time. The plan looks as follows:

 SELECT STATEMENT, GOAL = CHOOSE            Cost=2653711    Cardinality=1293564 Bytes=47861868
   HASH GROUP BY            Cost=2653711    Cardinality=1293564 Bytes=47861868
     HASH JOIN          Cost=2632362    Cardinality=1293564 Bytes=47861868
       TABLE ACCESS FULL    Object owner=PIE    Object name=ISITEMFULL  Cost=26845 Cardinality=1293564  Bytes=28458408
       TABLE ACCESS FULL    Object owner=PIE    Object name=TRANSACTION Cost=2312319 Cardinality=201443101  Bytes=3021646515

Even though, all columns have respective indexes, but those indexes are used, but rather, transaction table is scanned in full mode. Indexes defined on respective columns are as follows:

    --ISILMFULFILI4 - Transaction (in ISITEMFULL table)
    --ISILMFULFILI7 - ITEMID (in ISITEMFULL table)
    --ISILMFULFILI2 - ITEMCOVER (in ISITEMFULL table)
    --TRANSACTIONP1-    TRANSACTION (in TRANSACTION table)
    --TRANSACTIONI10 - INVOICE_DAT (in TRANSACTION table).

I have tried to use hints for these indexes but it has barely helped. Is there any other way through which I can stop full scan on transaction table. Even the query 

    Select  transaction from transaction TR where transaction in
    (
    Select  transaction from isclmfulfil IC WHERE sent_FLG='T') and invoice_dat is not null;

is taking a lot of time.

Is there any way, which can tell Oracle to perform in a better way. Also, stats on tables are up to date.

Upvotes: 1

Views: 423

Answers (1)

Anton Zaviriukhin
Anton Zaviriukhin

Reputation: 741

Have you tried such variant:

SELECT MIN(ISIL.TRANSACTION),
       ISIL.ITEMID ITEM_NUMBER,
       ISIL.ITEMCOVER POLICY_NUMBER 
  FROM ISITEMFULL ISIL 
 WHERE ISIL.SENT_FLG='T' 
   AND EXISTS(SELECT /*+ index(t TRANSACTIONP1) */ * 
                FROM TRANSACTION T 
               WHERE T.TRANSACTION=ISIL.TRANSACTION
                 AND T.INVOICE_DAT IS NOT NULL)
 GROUP BY ISIL.ITEMID,ISIL.ITEMCOVER;

?
But anyway 1.29M index probes in Transaction table will take a time.
(so Optimizer may be right to chose full scan).

You may also try parallel execution:

SELECT /*+ parallel(T, 4) */ 
MIN(T.TRANSACTION) TRANSACTION ,
ISIL.ITEMID ITEM_NUMBER,
ISIL.ITEMCOVER POLICY_NUMBER 
FROM TRANSACTION T,ISITEMFULL ISIL 
WHERE ISIL.SENT_FLG='T' 
AND T.TRANSACTION=ISIL.TRANSACTION
AND T.INVOICE_DAT IS NOT NULL 
GROUP BY ISIL.ITEMID,ISIL.ITEMCOVER;

You may try different degree of paralellism to check which perform faster.

You may try such hints to range scan TRANSACTIONI10 before hash join

SELECT /*+ first_rows ordered use_hash(ISIL T) index(T TRANSACTIONI10) */
MIN(T.TRANSACTION) TRANSACTION ,
ISIL.ITEMID ITEM_NUMBER,
ISIL.ITEMCOVER POLICY_NUMBER 
FROM ISITEMFULL ISIL, TRANSACTION T
WHERE ISIL.SENT_FLG='T' 
AND T.TRANSACTION=ISIL.TRANSACTION
AND T.INVOICE_DAT IS NOT NULL 
GROUP BY ISIL.ITEMID,ISIL.ITEMCOVER;

But probing TRANSACTION table 201M times will also take substantial time.

If you take SQL*Trace of these queries you'll have much more information about execution time on each step.
For example, your SQL may take a lot of time not only because of full scans, but also because of lack memory for hash workarea (in such case Oracle will write data to temp tablespace which significantly slow down execution). This also may be checked in V$SQL_WORKAREA_ACTIVE view (if NUMBER_PASSES=0 then all OK, 1 - slow but more or less acceptable, if more then 1, building hash table probubly take too much time)
(just in case, if you are using Exadata, Full scan will be much more efficient than large index range scan because of offloading and storage indexes)


I'd also suggest to create function index on TRANSACTION(nvl2(INVOICE_DAT,TRANSACTION, NULL)) it will be much more compact than combined index because it stores only TRANSACTION column and only for rows with not empty INVOICE_DAT so it will be scanned faster.
You will need to modify query to use it:

SELECT MIN(ISIL.TRANSACTION) TRANSACTION ,
ISIL.ITEMID ITEM_NUMBER,
ISIL.ITEMCOVER POLICY_NUMBER 
FROM TRANSACTION T,ISITEMFULL ISIL 
WHERE ISIL.SENT_FLG='T' 
AND ISIL.TRANSACTION = nvl2(INVOICE_DAT,TRANSACTION, NULL)
GROUP BY ISIL.ITEMID,ISIL.ITEMCOVER;

(reading of TRANSACTION table should be omitted in this case)

Upvotes: 1

Related Questions