Home » RDBMS Server » Performance Tuning » Tunning the query and 4000-5000 parameter in IN clause
Tunning the query and 4000-5000 parameter in IN clause [message #496700] Wed, 02 March 2011 00:35 Go to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59810
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 19816
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 
                )
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 Go to previous messageGo to next message
Roachcoach
Messages: 1225
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 19816
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1600/0/ 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 ...)
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 Go to previous messageGo to next message
Roachcoach
Messages: 1225
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 Go to previous messageGo to next message
Littlefoot
Messages: 19816
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!
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 Go to previous messageGo to next message
Roachcoach
Messages: 1225
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
Re: Tunning the query and 4000-5000 parameter in IN clause [message #496735 is a reply to message #496722] Wed, 02 March 2011 03:19 Go to previous messageGo to next message
John Watson
Messages: 4806
Registered: January 2010
Location: Global Village
Senior Member
@littlefoot
with regard to translation, you should be careful with this clause
Quote:
a whacking great pair

used by our friend Roachcoach, it is prone to mis-interpretation.
Re: Tunning the query and 4000-5000 parameter in IN clause [message #496739 is a reply to message #496735] Wed, 02 March 2011 03:35 Go to previous messageGo to next message
Littlefoot
Messages: 19816
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So - interpret it! (And mis-interpret it too.)
Re: Tunning the query and 4000-5000 parameter in IN clause [message #496742 is a reply to message #496739] Wed, 02 March 2011 03:41 Go to previous messageGo to next message
Roachcoach
Messages: 1225
Registered: May 2010
Location: UK
Senior Member
You chopped the "wise" off, I deliberately ran the words together Wink
Re: Tunning the query and 4000-5000 parameter in IN clause [message #496966 is a reply to message #496742] Thu, 03 March 2011 05:50 Go to previous messageGo to next message
forroughuse
Messages: 26
Registered: November 2010
Location: us
Junior Member
thanks all for the solution Smile
Re: Tunning the query and 4000-5000 parameter in IN clause [message #497008 is a reply to message #496966] Thu, 03 March 2011 12:12 Go to previous message
Littlefoot
Messages: 19816
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A solution? Which one did you choose?
Previous Topic: Collecting stats
Next Topic: performance tuning
Goto Forum:
  


Current Time: Thu Nov 27 07:46:48 CST 2014

Total time taken to generate the page: 0.12179 seconds