Home » SQL & PL/SQL » SQL & PL/SQL » Problem with in operator
Problem with in operator [message #203462] Wed, 15 November 2006 03:53 Go to next message
bvrswamy
Messages: 4
Registered: November 2006
Junior Member
Hi,

I am using subquery applied the conditon on IN operator like:

<MQ1.Column> IN (Subquery).

When I tried to execute this query TOAD gets hang and it fine if I used the hardcoded values. Here MQ1.Column datatype is varchar2(32). Is there amy limitation for IN operator?. If so please let me know...

I would appriciate for any reply. Embarassed
Re: Problem with in operator [message #203465 is a reply to message #203462] Wed, 15 November 2006 03:59 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It's probably not "hung" - it's just very slow. You need to look at the plan of the query. For example, is it using an index to perform the subquery ?
Re: Problem with in operator [message #203466 is a reply to message #203462] Wed, 15 November 2006 04:00 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
So you have a slow response time if you use a subquery in your IN list. Is that it?

What does an explain plan show you?

MHE

[Updated on: Wed, 15 November 2006 04:00]

Report message to a moderator

Re: Problem with in operator [message #203470 is a reply to message #203466] Wed, 15 November 2006 04:13 Go to previous messageGo to next message
bvrswamy
Messages: 4
Registered: November 2006
Junior Member
Hi,

Thanks for all your replies..

Here the thing is If I used '=' operaor the response is with in seconds. Only the problem when I used the IN operator. Please help me.
Re: Problem with in operator [message #203474 is a reply to message #203466] Wed, 15 November 2006 04:19 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I repeat
What does an explain plan show you?


MHE
Re: Problem with in operator [message #203476 is a reply to message #203462] Wed, 15 November 2006 04:22 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You mean the "IN" operator with a subquery rather than a list of hard-coded values ? Then the problem is obviously the performance of that subquery. What tables is it accessing ? How big are they ? Is the subquery correlated with the main query ? If it is, is there an index on the joining columns ?

You've posted this in the SQL Experts forum, so we have to assume you know what you are doing. You should run both the query with the hard-coded values and the one with the subquery through SQL*Trace and look at the plans. This should show you which bit is giving poor performance.
Re: Problem with in operator [message #203488 is a reply to message #203462] Wed, 15 November 2006 04:54 Go to previous messageGo to next message
bvrswamy
Messages: 4
Registered: November 2006
Junior Member
Hi,

I can't execute the Explain plan statement due to the insufficiant privileges to create oupt table as PLAN_TABLE. Here is the SQL code which we are using:

 SELECT DISTINCT (IT.ACCT_KEY), IT.ACCT_MEMBER_KEY, TEMP_TABLE.BUS_CLASS_TP_CD
 FROM (SELECT ACC.ACCT_KEY, AM.ACCT_MEMBER_KEY, ACC.LGCY_BRANCH_OFFICE_BP_KEY 
 	  FROM oltp3.ACCOUNT ACC, oltp3.ACCOUNT_MEMBER AM 
 	  WHERE ACC.ACCT_KEY = AM.ACCT_KEY 
           AND AM.ACCT_MBR_ID = ACC.PRIM_ACCT_MBR_ID ) IT,
 oltp3.FS, oltp3.LEVEL_OF_PREP_TYPE LPT, oltp3.OFFICE OFC, oltp3.ACCOUNT_MEMBER ACCMEM, oltp3.PARTY PTY, oltp3.ORGANIZATION ORG,
 (SELECT SIC_ID, BUS_CLASS_TP_CD   
 FROM oltp3.SIC JOIN (SELECT CONTRCTR_TP_CD, BUS_CLASS_TP_CD   
 FROM oltp3.CONTRACTOR_TYPE   
 WHERE BUS_CLASS_TP_CD IS NOT NULL AND VALID_IND = 'Y') INNER_TABLE ON INNER_TABLE.CONTRCTR_TP_CD =   
                                                    SIC.CONTRCTR_TP_CD WHERE SIC.VALID_IND = 'Y'   
 ) TEMP_TABLE 	 
 WHERE IT.ACCT_MEMBER_KEY = FS.ACCT_MEMBER_KEY
 AND FS.LVL_OF_PREP_TP_CD = LPT.LVL_OF_PREP_TP_CD  
 AND FS.COMPLETED_IND = 'Y' 
 AND FS.FS_VERS_TP_CD =  '1'  
 AND LPT.CPA_IND = 'Y' 
 AND ACCMEM.PARTY_KEY = PTY.PARTY_KEY
 AND PTY.PARTY_KEY = ORG.ORG_KEY
 AND ORG.SIC_ID IS NOT NULL
 AND TEMP_TABLE.SIC_ID = ORG.SIC_ID 
 AND IT.ACCT_MEMBER_KEY [B]in [/B] 
(  
 	SELECT DISTINCT(ACCMEMBER.ACCT_MEMBER_KEY)
 	FROM oltp3.ACCOUNT ACCOUNT, oltp3.PARTY PARTY, oltp3.ORGANIZATION ORG, oltp3.OFFICE OFC,
 	oltp3.ACCOUNT_MEMBER ACCMEMBER
 	LEFT OUTER JOIN oltp3.FS FS ON  ACCMEMBER.ACCT_MEMBER_KEY = FS.ACCT_MEMBER_KEY
 	WHERE ACCOUNT.ACCT_KEY = ACCMEMBER.ACCT_KEY
 	AND ACCMEMBER.PARTY_KEY = PARTY.PARTY_KEY
 	AND PARTY.PARTY_KEY = ORG.ORG_KEY
 	AND ACCOUNT.LGCY_BRANCH_OFFICE_BP_KEY = OFC.OFFICE_BP_KEY 
 	AND OFC.OFFICE_TP_CD in ('10', '20', '40')  
 	AND ( 
 	(ACCMEMBER.ACCT_MBR_ID = ACCOUNT.PRIM_ACCT_MBR_ID AND
 	ACCOUNT.ACCT_STATUS_TP_CD =4 
 	)
 	OR (ACCMEMBER.ACCT_MBR_ID = ACCOUNT.PRIM_ACCT_MBR_ID AND ACCOUNT.ACCT_STATUS_TP_CD IN (1,2,5,6,7))
 	AND (FS.FS_VERS_TP_CD =1 AND FS.FS_DT>ADD_MONTHS(SYSDATE,-30))  
 	)	          
 ) 
Re: Problem with in operator [message #203503 is a reply to message #203488] Wed, 15 November 2006 05:48 Go to previous message
bvrswamy
Messages: 4
Registered: November 2006
Junior Member
Hi,

I got the solution by using the trim function which is applied for the filed in the subquery.
Previous Topic: A $ sign in PLSQL variables names or procedure name
Next Topic: NLS error with NCLOB (merged)
Goto Forum:
  


Current Time: Fri Dec 09 05:44:01 CST 2016

Total time taken to generate the page: 0.11735 seconds