|
-
Explain plan Query
I've got a query that takes 5 mins when a particular index is on and 1.5 hrs when it is dropped. I've pasted the explain plans and the only difference I can see is at step 32. When the index is there (1st explain plan posted) its a RANGE SCAN of ACCOUNTS_PAYABLE_FIX_IDX and when I drop that index its a FULL SCAN of ACCOUNTS_PAYABLE_IDX3 (2nd explain plan). The row counts are the same so I guess my question is why would a FULL SCAN make my query run for 1.5hrs instead of 5 mins. (The reason I want to drop this index is that its impacting a load of other jobs!)
Exp plan 1 - 5 mins
Code:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 203 | 1020 (0)|
| 1 | SORT ORDER BY | | 1 | 203 | 1020 (0)|
|* 2 | FILTER | | | | |
| 3 | NESTED LOOPS | | 1 | 203 | 1017 (0)|
| 4 | NESTED LOOPS | | 1 | 184 | 1016 (0)|
| 5 | NESTED LOOPS | | 1 | 159 | 1015 (0)|
| 6 | NESTED LOOPS | | 1 | 106 | 998 (0)|
| 7 | NESTED LOOPS | | 1 | 75 | 997 (0)|
|* 8 | TABLE ACCESS FULL | CHECK_REGISTER | 1278 | 61344 | 741 (0)|
|* 9 | TABLE ACCESS BY INDEX ROWID| VENDOR_ADDRESS | 1 | 27 | 2 (50)|
|* 10 | INDEX UNIQUE SCAN | P_VENDOR_ADD#SEQ_VEND_ADDRESS | 1 | | |
| 11 | TABLE ACCESS BY INDEX ROWID | VENDOR_MASTER | 1 | 31 | 2 (50)|
|* 12 | INDEX UNIQUE SCAN | P_VENDOR_MASTER#SEQ_VEND_ID | 1 | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | ACCOUNTS_PAYABLE | 1 | 53 | 17 (0)|
|* 14 | INDEX RANGE SCAN | ACC_PAY#SEQ_VEND_ID#AP_STATUS | 10 | | |
| 15 | VIEW | VU_CLAIM_DETAIL | 1 | 25 | |
| 16 | UNION-ALL PARTITION | | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | INST_CLAIM_DETAIL | 2 | 22 | 2 (50)|
|* 18 | INDEX RANGE SCAN | I_INST_CLM_DET#SEQ_AP_TRANS | 1 | | 1 (0)|
| 19 | TABLE ACCESS BY INDEX ROWID | PROFSVC_CLAIM_DETAIL | 1 | 13 | 2 (50)|
|* 20 | INDEX RANGE SCAN | I_PROF_CLM_DTL#SEQ_AP_TRANS | 1 | | 3 (0)|
| 21 | TABLE ACCESS BY INDEX ROWID | DENTAL_CLAIM_DETAIL | 1 | 13 | 2 (50)|
|* 22 | INDEX RANGE SCAN | I_DNTL_CLM_DTL#SEQ_AP_TRANS | 1 | | 3 (0)|
| 23 | VIEW | VU_CLAIM_HEADER | 1 | 19 | |
| 24 | UNION-ALL PARTITION | | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | INST_CLAIM_HEADER | 1 | 22 | 2 (50)|
|* 26 | INDEX UNIQUE SCAN | P_INST_CLAIM_HEAD#SEQ_CLAIM_ID | 58142 | | 1 (0)|
| 27 | TABLE ACCESS BY INDEX ROWID | PROFSVC_CLAIM_HEADER | 1 | 22 | 2 (50)|
|* 28 | INDEX UNIQUE SCAN | P_PROFSVC_CLAIM_H#SEQ_CLAIM_ID | 583K| | 2 (0)|
| 29 | TABLE ACCESS BY INDEX ROWID | DENTAL_CLAIM_HEADER | 1 | 22 | 2 (50)|
|* 30 | INDEX UNIQUE SCAN | P_DNTL_CLAIM_H#SEQ_CLAIM_ID | 214K| | 1 (0)|
|* 31 | INDEX RANGE SCAN | ACCOUNTS_PAYABLE3_IDX | 28 | 84 | 3 (0)|
|* 32 | INDEX RANGE SCAN | ACCOUNTS_PAYABLE_FIX_IDX | 72 | 216 | 3 (0)|
-----------------------------------------------------------------------------------------------------------
Explain plan 2 - 1.5hrs
Code:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 203 | 1020 (0)|
| 1 | SORT ORDER BY | | 1 | 203 | 1020 (0)|
|* 2 | FILTER | | | | |
| 3 | NESTED LOOPS | | 1 | 203 | 1017 (0)|
| 4 | NESTED LOOPS | | 1 | 184 | 1016 (0)|
| 5 | NESTED LOOPS | | 1 | 159 | 1015 (0)|
| 6 | NESTED LOOPS | | 1 | 106 | 998 (0)|
| 7 | NESTED LOOPS | | 1 | 75 | 997 (0)|
|* 8 | TABLE ACCESS FULL | CHECK_REGISTER | 1278 | 61344 | 741 (0)|
|* 9 | TABLE ACCESS BY INDEX ROWID| VENDOR_ADDRESS | 1 | 27 | 2 (50)|
|* 10 | INDEX UNIQUE SCAN | P_VENDOR_ADD#SEQ_VEND_ADDRESS | 1 | | |
| 11 | TABLE ACCESS BY INDEX ROWID | VENDOR_MASTER | 1 | 31 | 2 (50)|
|* 12 | INDEX UNIQUE SCAN | P_VENDOR_MASTER#SEQ_VEND_ID | 1 | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | ACCOUNTS_PAYABLE | 1 | 53 | 17 (0)|
|* 14 | INDEX RANGE SCAN | ACC_PAY#SEQ_VEND_ID#AP_STATUS | 10 | | |
| 15 | VIEW | VU_CLAIM_DETAIL | 1 | 25 | |
| 16 | UNION-ALL PARTITION | | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | INST_CLAIM_DETAIL | 2 | 22 | 2 (50)|
|* 18 | INDEX RANGE SCAN | I_INST_CLM_DET#SEQ_AP_TRANS | 1 | | 1 (0)|
| 19 | TABLE ACCESS BY INDEX ROWID | PROFSVC_CLAIM_DETAIL | 1 | 13 | 2 (50)|
|* 20 | INDEX RANGE SCAN | I_PROF_CLM_DTL#SEQ_AP_TRANS | 1 | | 3 (0)|
| 21 | TABLE ACCESS BY INDEX ROWID | DENTAL_CLAIM_DETAIL | 1 | 13 | 2 (50)|
|* 22 | INDEX RANGE SCAN | I_DNTL_CLM_DTL#SEQ_AP_TRANS | 1 | | 3 (0)|
| 23 | VIEW | VU_CLAIM_HEADER | 1 | 19 | |
| 24 | UNION-ALL PARTITION | | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | INST_CLAIM_HEADER | 1 | 22 | 2 (50)|
|* 26 | INDEX UNIQUE SCAN | P_INST_CLAIM_HEAD#SEQ_CLAIM_ID | 58142 | | 1 (0)|
| 27 | TABLE ACCESS BY INDEX ROWID | PROFSVC_CLAIM_HEADER | 1 | 22 | 2 (50)|
|* 28 | INDEX UNIQUE SCAN | P_PROFSVC_CLAIM_H#SEQ_CLAIM_ID | 583K| | 2 (0)|
| 29 | TABLE ACCESS BY INDEX ROWID | DENTAL_CLAIM_HEADER | 1 | 22 | 2 (50)|
|* 30 | INDEX UNIQUE SCAN | P_DNTL_CLAIM_H#SEQ_CLAIM_ID | 214K| | 1 (0)|
|* 31 | INDEX RANGE SCAN | ACCOUNTS_PAYABLE3_IDX | 28 | 84 | 3 (0)|
|* 32 | INDEX FULL SCAN | ACCOUNTS_PAYABLE3_IDX | 72 | 216 | 4433 (0)|
-----------------------------------------------------------------------------------------------------------
-
Hi
Can you please post the query and the defination of both the indexes i.e
ACCOUNTS_PAYABLE_FIX_IDX and ACCOUNTS_PAYABLE3_IDX
regards
Hrishy
-
The query is a bit of a beast:
Code:
SELECT
'N' SELECT_FLAG,
V.VENDOR_ID,
V.FULL_NAME,
VA.ADDRESS_LINE_1,
C.CHECK_NUMBER,
C.EFT_TRANS_NUMBER,
C.CHECK_DATE,
C.CHECK_AMT,
C.PAYMENT_METHOD,
A.SEQ_AP_TRANS,
A.FILE_TYPE,
A.NET_AMT,
-- SUM(a.net_amt) OVER (partition BY v.seq_vend_id ORDER BY a.seq_ap_trans RANGE UNBOUNDED PRECEDING) R_total,
'' R_total,
v.seq_vend_id,
A.AP_STATUS,
A.SELECT_FOR_PAYMENT,
H.CLAIM_NUMBER,
A.paid_net_amt,
a.company_code,
h.claim_type,
h.seq_claim_id,
d.line_number,
c.seq_ckprt_id,
d.claim_status,
d.sub_line_code,
a.POSTED_DATE
FROM
hsd_check_register C,
hsd_accounts_payable A,
HSD_VU_CLAIM_HEADER H,
HSD_VENDOR_MASTER V,
HSD_VENDOR_ADDRESS VA,
hsd_vu_claim_detail d
WHERE
(c.CHECK_NUMBER >0 OR c.eft_trans_number > 0)
AND c.PAYMENT_METHOD IN ('BEFT','BCHCK')
AND c.insert_process <> 'CONVERT'
AND (NOT EXISTS(SELECT NULL
FROM HSD_ACCOUNTS_PAYABLE AA
WHERE AA.CHECK_NUMBER = C.CHECK_NUMBER)
OR NOT EXISTS(SELECT NULL
FROM HSD_ACCOUNTS_PAYABLE AB
WHERE AB.EFT_TRANS_NUMBER = C.EFT_TRANS_NUMBER))
AND C.SEQ_VEND_ID = A.SEQ_VEND_ID
AND C.SEQ_VEND_ADDRESS = A.SEQ_VEND_ADDRESS
AND A.AP_STATUS = 'F'
AND A.SELECT_FOR_PAYMENT = 'C'
AND A.PAYMENT_METHOD IS NULL
AND A.SEQ_CLAIM_ID = H.SEQ_CLAIM_ID
AND V.SEQ_VEND_ID = c.SEQ_VEND_ID
AND VA.SEQ_VEND_ADDRESS = C.SEQ_VEND_ADDRESS
AND VA.SEQ_VEND_ID = C.SEQ_VEND_ID
AND a.seq_ap_trans = d.seq_ap_trans
ORDER BY
v.VENDOR_ID,
-- a.seq_ap_trans,
va.ADDRESS_LINE_1,
c.CHECK_DATE,
a.POSTED_DATE;
and the indexes are as follows:
Code:
Enter value for index_name: ACCOUNTS_PAYABLE3_IDX
COL
TABLE_NAME INDEX_NAME Unique? COLUMN_NAME pos
------------------------ ---------------------------------------- --------- ---------------------- ---
ACCOUNTS_PAYABLE ACCOUNTS_PAYABLE3_IDX NONUNIQUE CHECK_NUMBER 1
ACCOUNTS_PAYABLE ACCOUNTS_PAYABLE3_IDX NONUNIQUE EFT_TRANS_NUMBER 2
SQL> /
Enter value for index_name: ACCOUNTS_PAYABLE_FIX_IDX
COL
TABLE_NAME INDEX_NAME Unique? COLUMN_NAME pos
------------------------ ---------------------------------------- --------- ---------------------- ---
ACCOUNTS_PAYABLE ACCOUNTS_PAYABLE_FIX_IDX NONUNIQUE EFT_TRANS_NUMBER 1
Thanks
-
Hi
Can you please drop the index
ACCOUNTS_PAYABLE_FIX_IDX
and recreate a index like
ACTPAY_EFTRANS_CHK
with column order as (EFT_TRANS_NUMBER,CHECK_NUMBER) analyze this index and run the query.
regards
Hrishy
-
Beautiful! - Query now takes 1 sec! Can you explain why - cant seem to get my head round the reason
Thanks very much
Code:
SQL> drop index ACCOUNTS_PAYABLE_FIX_IDX;
Index dropped.
SQL> create index ACTPAY_EFTRANS_CHK on accounts_payable(EFT_TRANS_NUMBER, CHECK_NUMBER) tablespace diamidx;
Index created.
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 203 | 1020 (0)|
| 1 | SORT ORDER BY | | 1 | 203 | 1020 (0)|
|* 2 | FILTER | | | | |
| 3 | NESTED LOOPS | | 1 | 203 | 1017 (0)|
| 4 | NESTED LOOPS | | 1 | 184 | 1016 (0)|
| 5 | NESTED LOOPS | | 1 | 159 | 1015 (0)|
| 6 | NESTED LOOPS | | 1 | 106 | 998 (0)|
| 7 | NESTED LOOPS | | 1 | 75 | 997 (0)|
|* 8 | TABLE ACCESS FULL | CHECK_REGISTER | 1278 | 61344 | 741 (0)|
|* 9 | TABLE ACCESS BY INDEX ROWID| VENDOR_ADDRESS | 1 | 27 | 2 (50)|
|* 10 | INDEX UNIQUE SCAN | P_VENDOR_ADD#SEQ_VEND_ADDRESS | 1 | | |
| 11 | TABLE ACCESS BY INDEX ROWID | VENDOR_MASTER | 1 | 31 | 2 (50)|
|* 12 | INDEX UNIQUE SCAN | P_VENDOR_MASTER#SEQ_VEND_ID | 1 | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | ACCOUNTS_PAYABLE | 1 | 53 | 17 (0)|
|* 14 | INDEX RANGE SCAN | ACC_PAY#SEQ_VEND_ID#AP_STATUS | 10 | | |
| 15 | VIEW | VU_CLAIM_DETAIL | 1 | 25 | |
| 16 | UNION-ALL PARTITION | | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | INST_CLAIM_DETAIL | 2 | 22 | 2 (50)|
|* 18 | INDEX RANGE SCAN | I_INST_CLM_DET#SEQ_AP_TRANS | 1 | | 1 (0)|
| 19 | TABLE ACCESS BY INDEX ROWID | PROFSVC_CLAIM_DETAIL | 1 | 13 | 2 (50)|
|* 20 | INDEX RANGE SCAN | I_PROF_CLM_DTL#SEQ_AP_TRANS | 1 | | 3 (0)|
| 21 | TABLE ACCESS BY INDEX ROWID | DENTAL_CLAIM_DETAIL | 1 | 13 | 2 (50)|
|* 22 | INDEX RANGE SCAN | I_DNTL_CLM_DTL#SEQ_AP_TRANS | 1 | | 3 (0)|
| 23 | VIEW | VU_CLAIM_HEADER | 1 | 19 | |
| 24 | UNION-ALL PARTITION | | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | INST_CLAIM_HEADER | 1 | 22 | 2 (50)|
|* 26 | INDEX UNIQUE SCAN | P_INST_CLAIM_HEAD#SEQ_CLAIM_ID | 58142 | | 1 (0)|
| 27 | TABLE ACCESS BY INDEX ROWID | PROFSVC_CLAIM_HEADER | 1 | 22 | 2 (50)|
|* 28 | INDEX UNIQUE SCAN | P_PROFSVC_CLAIM_H#SEQ_CLAIM_ID | 583K| | 2 (0)|
| 29 | TABLE ACCESS BY INDEX ROWID | DENTAL_CLAIM_HEADER | 1 | 22 | 2 (50)|
|* 30 | INDEX UNIQUE SCAN | P_DNTL_CLAIM_H#SEQ_CLAIM_ID | 214K| | 1 (0)|
|* 31 | INDEX RANGE SCAN | ACCOUNTS_PAYABLE3_IDX | 28 | 84 | 3 (0)|
|* 32 | INDEX RANGE SCAN | ACTPAY_EFTRANS_CHK | 72 | 216 | 2 (0)|
-----------------------------------------------------------------------------------------------------------
-
Sorry that 1 sec should be 20 secs but still a vast improvement! This index also means that the queries that we're running slow because of the ACCOUNTS_PAYABLE_FIX_IDX being in place are now also running in an acceptable time. Great.
-
 Originally Posted by hrishy
Hi
Can you please drop the index
ACCOUNTS_PAYABLE_FIX_IDX
and recreate a index like
ACTPAY_EFTRANS_CHK
with column order as (EFT_TRANS_NUMBER,CHECK_NUMBER) analyze this index and run the query.
regards
Hrishy
Hrishy,
Your suggestion to recreate of index can hinder lot many other queries if check_number is a only column used for selectivity.. before you suggest some thing do mention its drawbacks.. coz you never know what their application is and how they run..
cleggfr,
The query is running fast coz it is now using Index Range Scan against Index Full Scan.. Check docs why Full Scan is the worst access path.
BTW, without even changing the order of the columns of that index you could have got better plan, had you re-analyzed ur indexes.. ( it would have gone of Index FFS or so ).
Rgds
Abhay.
Last edited by abhaysk; 07-04-2005 at 03:43 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote