Home » RDBMS Server » Performance Tuning » SQL Tuning
SQL Tuning [message #230480] Thu, 12 April 2007 02:00 Go to next message
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 #230481 is a reply to message #230480] Thu, 12 April 2007 02:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Please format your query and use code tags.

MHE
Re: SQL Tuning [message #230489 is a reply to message #230481] Thu, 12 April 2007 02:15 Go to previous messageGo to next message
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 #230492 is a reply to message #230489] Thu, 12 April 2007 02:19 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If "as_of_date" is a DATE, compare it like this:
WHERE           as_of_date = TO_DATE('31-Jan-2007','DD-Mon-YYYY')


MHE
Re: SQL Tuning [message #230495 is a reply to message #230492] Thu, 12 April 2007 02:27 Go to previous messageGo to next message
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 #230499 is a reply to message #230495] Thu, 12 April 2007 02:31 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And this bind variable :B, is that a DATE? If not, you should add a TO_DATE around it as well.

Furthermore, it's possible that the NOT IN is holding you back. You might want to test a MINUS construction.

MHE
Re: SQL Tuning [message #230518 is a reply to message #230499] Thu, 12 April 2007 02:59 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #230771 is a reply to message #230480] Thu, 12 April 2007 14:38 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Run SQL_TRACE and post TKPROF.
Re: SQL Tuning [message #231516 is a reply to message #230771] Tue, 17 April 2007 06:50 Go to previous messageGo to next message
kamkan
Messages: 27
Registered: April 2007
Location: Chennai, INDIA
Junior Member
Can try Function Based Indexes in the columns used in the WHERE clause.
Re: SQL Tuning [message #231544 is a reply to message #231516] Tue, 17 April 2007 07:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This might be a similar problem to your other post in which you had 6M rows with AS_OF_DATE = 31-jan-2007

Ross Leishman

[Updated on: Tue, 17 April 2007 07:55]

Report message to a moderator

Re: SQL Tuning [message #231668 is a reply to message #230480] Wed, 18 April 2007 00:31 Go to previous message
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.
Previous Topic: SQL Performance
Next Topic: SQL*Net message from client - Event
Goto Forum:
  


Current Time: Sat Dec 03 01:11:55 CST 2016

Total time taken to generate the page: 0.04685 seconds