OraFAQ Forum: Performance Tuning » Tunning the query and 4000-5000 parameter in IN clause

Home » RDBMS Server » Performance Tuning » Tunning the query and 4000-5000 parameter in IN clause

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tunning the query and 4000-5000 parameter in IN clause [message #496700]

Wed, 02 March 2011 00:35

forroughuse
Messages: 26
Registered: November 2010
Location: us

Junior Member

Hi All,

I need urgent help. In my below query example , i have to pass more than 4000-5000 paramter in "a1.num" in below query.
Can you please suggest me what is the best way to handle this, also if I pass more than 2000 paramter , the query takes a long time to execute. How can we solve the performance issue as well how I can pass more parameter. Can some1 pls help me urgently by showing me some example.

Thanks in advance

SELECT c1, c2,
TO_CHAR (c3, 'HH24:MI'),
c4,
c5,
trunc(c6),
c7,
c8,
c9,
c10,
DECODE (aa,
ab, NULL,
abc
),
(CASE
WHEN as = 0 AND nb > 0
THEN 1
WHEN as = 0 AND nb < 0
THEN -1
WHEN as = 0 AND nb = 0
THEN 0
ELSE (absd)
END
) ap,
c11,
c12,
c13
FROM t1,
t2,
t3,
t4,
t5,
t6
WHERE (t1.cn1 IN (
SELECT s1
FROM a1,
a2,
a3,
a4,
a5,
a6,
a7,
a8,
a9,
a10
WHERE (a1.num IN
(n1,
n2,
n3,
n4,
n5,
n6,
n7,
n8,
.
.
.
.
.
n5000
)
)
AND a1.a=a2.a
AND a2.b=a2.b
GROUP BY g1,g2,g3,g4,g5,g6,g7)
)
AND t1.a = 'r'
AND t1.a=t2.a
AND t3.a=t2.a
AND t3.a=t4.a
AND t4.a=t5.a
AND t6.a=t1.a

Report message to a moderator

Re: Tunning the query and 4000-5000 parameter in IN clause [message #496707 is a reply to message #496700]

Wed, 02 March 2011 01:02

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Re: Tunning the query and 4000-5000 parameter in IN clause [message #496716 is a reply to message #496707]

Wed, 02 March 2011 01:58

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Which Oracle version do you use?

As far as I can tell, number of elements in IN condition's list is limited to 1000 (11gR2 IN condition). So - how do you plan to enter 5000 elements in there?

Perhaps you'd better store those values into a table, and then use it in a subquery, such as

select ...
from ...
where a1.num in (select num
                 from your_new_table_that_contains_5000_values 
                )

Report message to a moderator

Re: Tunning the query and 4000-5000 parameter in IN clause [message #496720 is a reply to message #496716]

Wed, 02 March 2011 02:06

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

You can con that by using a whacking great pairwise comparison as I recall. Suspect making a table is a better idea overall though.

Report message to a moderator

Re: Tunning the query and 4000-5000 parameter in IN clause [message #496722 is a reply to message #496720]

Wed, 02 March 2011 02:16

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Could you translate that to English, please? (the one children learn in school) What does "con that by using a whacking great pairwise comparison" mean? (I knew I should have paid more attention back then ...)

Report message to a moderator

Re: Tunning the query and 4000-5000 parameter in IN clause [message #496725 is a reply to message #496722]

Wed, 02 March 2011 02:34

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Con meaning trick, or in this case - bypass.

WHERE (1,<variable>) in ( (1,<InListVariable1>), (1,<InListVariable2>), (1,<InListVariable1001>))

I'm going from memory, let me check it works.

Edit, yeah just tested 1499

with t as 
(select level lvl,dummy from dual connect by level <1500)
select * from t
where
(lvl,dummy) in ((1,'X'),                                      
(2,'X'),                                      
(3,'X'),                                      
(4,'X'),                                      
(5,'X'),                                      
(6,'X'),                                      
(7,'X'),                                      
(8,'X'),                                      
(9,'X'),                                      
(10,'X'),                                    
(11,'X'),                                    
(12,'X'),                                    
(13,'X'),                                    
(14,'X'),                                    
(15,'X'),   
.
.
.
(1495,'X'),                                 
(1496,'X'),                                 
(1497,'X'),                                 
(1498,'X'),                                 
(1499,'X'))
;

Worked as intended

[Updated on: Wed, 02 March 2011 02:38]

Report message to a moderator

Re: Tunning the query and 4000-5000 parameter in IN clause [message #496729 is a reply to message #496725]

Wed, 02 March 2011 02:48

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Cool, didn't know that! Learned a few things in a matter of minutes. Thank you!

Report message to a moderator

Re: Tunning the query and 4000-5000 parameter in IN clause [message #496734 is a reply to message #496729]

Wed, 02 March 2011 03:02

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I can't comment as to the efficiency, any time I've done things like that it's been a bit of a rush job where best practice takes a back seat to the business shouting "NOW! WE WANT IT NAAAAAAAOOOOOOOWWWWW!!!!!" Smile