| SQL Tuning [message #230480] |
Thu, 12 April 2007 02:00  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
In the following query, the table FEM_TERM_DEPOSITS has around 7 million records. I want to tune the query. Can anybody tell me what are the indexes required? This query goes for a FULL tablescan on fEM_TERM_DEPOSITS.
SELECT DISTINCT 4,SUBSTR(RM_COA_ID,1,5)||'999999','Overdue '|| SUBSTR(RM_COA_ID,1,5)
FROM FEM_TERM_DEPOSITS WHERE AS_OF_DATE = '31-Jan-2007' AND (NVL(OVERDUES_AMOUNT,0)!=0)
AND SUBSTR(RM_COA_ID,1, 5) || '999999' NOT IN (SELECT LEAF_NODE FROM OFSA_LEAF_DESC
WHERE LEAF_NUM_ID=4)
FEM_TERM_DEPOSITS already has a index starting with AS_OF_DATE.
Brayan.
|
|
|
|
|
|
| Re: SQL Tuning [message #230489 is a reply to message #230481] |
Thu, 12 April 2007 02:15  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
Hope this is readable.
SELECT DISTINCT 4
, SUBSTR (rm_coa_id, 1, 5) || '999999'
, 'Overdue ' || SUBSTR (rm_coa_id, 1, 5)
FROM fem_term_deposits
WHERE as_of_date = '31-Jan-2007'
AND (NVL (overdues_amount, 0) != 0)
AND SUBSTR (rm_coa_id, 1, 5) || '999999' NOT IN (
SELECT leaf_node
FROM ofsa_leaf_desc
WHERE leaf_num_id =
4)
Brayan.
[Updated on: Thu, 12 April 2007 02:16] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: SQL Tuning [message #230495 is a reply to message #230492] |
Thu, 12 April 2007 02:27 |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi ,
Actually the code is
INSERT INTO OFSA_DETAIL_OTHER_COA (LEAF_NUM_ID,LEAF_NODE,
COMMON_COA_ID,TAX_RATE,O_COA_ID,O_ORG_ID)
SELECT DISTINCT 4, SUBSTR(RM_COA_ID,1, 5) || '999999',
COMMON_COA_ID,0,-99100,-99100
FROM FEM_TERM_DEPOSITS
WHERE AS_OF_DATE = :B1
AND (NVL(OVERDUES_AMOUNT,0)!=0)
AND SUBSTR(RM_COA_ID,1, 5) || '999999'NOT IN
(SELECT LEAF_NODE FROM OFSA_DETAIL_OTHER_COA
WHERE LEAF_NUM_ID=4)
In the above query FEM_TERM_TERM_DEPOSITS is going for a FULL tablescan.
Brayan.
|
|
|
|
|
|
| Re: SQL Tuning [message #230518 is a reply to message #230499] |
Thu, 12 April 2007 02:59 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Disagree with the MINUS idea - NOT IN can perform a Hash Anti-Join, which can be orders of magnitude quicker than a MINUS in some circumstances.
Having said that, you have to amke sure it is performing a hash-anti-join.
How about giving us the Explain Plan.
How many rows in total with AS_OF_DATE = '31-Jan-2007'? And how many of those satisfy the OVERDUES_AMOUNT and the sub-query?
Ross Leishman
|
|
|
|
| Re: SQL Tuning [message #230573 is a reply to message #230518] |
Thu, 12 April 2007 04:05 |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi Ross,
I have created index on AS_OF_DATE, OVERDUES_AMOUNT, RM_COA_ID. Now it is selecting ANTI-Nested Loop.
Regards,
Ronald.
|
|
|
|
| Re: SQL Tuning [message #230613 is a reply to message #230518] |
Thu, 12 April 2007 05:56 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| rleishman wrote on Thu, 12 April 2007 17:59 | How about giving us the Explain Plan.
How many rows in total with AS_OF_DATE = '31-Jan-2007'? And how many of those satisfy the OVERDUES_AMOUNT and the sub-query?
Ross Leishman
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL Tuning [message #231668 is a reply to message #230480] |
Wed, 18 April 2007 00:31 |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi rleishman,
Following is the explain plan. And this issue is solved. Thanks for your valuable suggestions.
ID PARENT_ID OPERATION OBJECT_NAME
0 SELECT STATEMENT
1 0 SORT UNIQUE
2 1 NESTED LOOPS ANTI
3 2 INDEX RANGE SCAN FEM_TERM_DEPOSITS_T1
4 2 INDEX UNIQUE SCAN LEAF_DESC
Regards,
Brayan.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
