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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/38495bae572a8fef42321afc53d64601?s=64&d=mm&r=g) |
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 #496725 is a reply to message #496722] |
Wed, 02 March 2011 02:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Sep 09 09:54:39 CDT 2024
|