| IN clause limitation [message #174629] |
Tue, 30 May 2006 02:32  |
alibawa
Messages: 11
Registered: May 2006
|
Junior Member |
|
|
I am using Oracle for winwdows NT SQL Plus 3.3.
I need to serach records using IN clause .. There i have more than 1000 parameters to give in IN clause.
Is there any way i can give more than 1000 parameters in the IN clause, Because i have data in Bulk.. lets say i need to search records with 50000 parameters.So for this in current scenario i would have to spool and run the query 50 times and the procedure is very lengthy.
I want to do something like this...
For e.g.
select abc,xyz from test where xyz in (1,2,3,.......,1000,......2000,....5000)
Is there anyway i can do it???
|
|
|
|
|
|
|
|
|
|
|
|
| Re: IN clause limitation [message #174635 is a reply to message #174629] |
Tue, 30 May 2006 02:53  |
alibawa
Messages: 11
Registered: May 2006
|
Junior Member |
|
|
i presume you are suggesting something like this...
select abc,xyz from bbc where xyz in (1,2,3,4,.......,1000) or xyz in (1001,1002,.......,2000)
|
|
|
|
|
|
|
|
|
|
|
|
| Re: IN clause limitation [message #174647 is a reply to message #174645] |
Tue, 30 May 2006 03:05 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
Pity, as usual, Maaher's method would've been faster. Ah well.
1 other thing, are you able to use ranges of values (your sample code implies that you can)
Select abc,xyz from bbc where xyz in (1,2,3,4,.......,1000) or xyz in (1001,1002,.......,2000)
or
Select abc,xyz from bbc
where xyz between 1 and 4 or xyz in (1001,1002,.......,2000)
|
|
|
|
|
|
|
|
| Re: IN clause limitation [message #174653 is a reply to message #174643] |
Tue, 30 May 2006 03:15 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| JSI2001 wrote on Tue, 30 May 2006 18:00 |
Maaher's suggestion might be worth exploring tho' (can you create functions and types?)
|
Understatement of the year!
That many values in an IN-list will run like a three-legged, un-motivated, arthritic dog. The optimizer has two choices:
- For every row, go through the IN list until you find a matching value (hopefully it finds one early, otherwise it performs more comparisons). In the worst case, for one row only which does match any value in the list, it will perform 50,000 comparisons.
- For each value in the IN-list, it will perform a scan of the table using an index on that column. This will be somewhat equivalent to running 50,000 SQLs. Unless your base table contains 100M+ rows, this will be pretty inefficient.
If you load the values into a Nested Table or a Global Temporary Table and use and IN-sub-query, Oracle can do a single pass of the base table and a single pass of the IN values.
If you try the nested-table, don't forget to add a CARDINALITY hint to the sub-query. Someone in this forum supplied a link to a nice thread on this in AskTom recently. Run it through Explain Plan and ensure it is performing a HASH join, not a FILTER. Post your SQL and plan here if you need help.
Ross Leishman
|
|
|
|
| Re: IN clause limitation [message #175269 is a reply to message #174653] |
Thu, 01 June 2006 08:18 |
alibawa
Messages: 11
Registered: May 2006
|
Junior Member |
|
|
"If you load the values into a Nested Table or a Global Temporary Table and use and IN-sub-query, Oracle can do a single pass of the base table and a single pass of the IN values.
If you try the nested-table, don't forget to add a CARDINALITY hint to the sub-query. Someone in this forum supplied a link to a nice thread on this in AskTom recently. Run it through Explain Plan and ensure it is performing a HASH join, not a FILTER. Post your SQL and plan here if you need help.""
Can you forward me some informative link to the procedure you have just told so that i can grasp the concept of what you just said. I am a little clueless about Nested Tables and Global temporary Table.
Thanks
|
|
|
|
| Re: IN clause limitation [message #175403 is a reply to message #175269] |
Fri, 02 June 2006 02:50 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Here's an example of what I'm talking about
SQL> VARIABLE my_cur refcursor
SQL>
SQL> DECLARE
2 my_ary SYS.dbms_debug_vc2coll;
3 my_cur sys_refcursor;
4 BEGIN
5 my_ary := SYS.dbms_debug_vc2coll ( );
6
7 SELECT DISTINCT object_name
8 BULK COLLECT INTO my_ary
9 FROM user_objects;
10
11 my_ary.DELETE ( 1 );
12
13 OPEN :my_cur
14 FOR
15 SELECT *
16 FROM user_objects
17 WHERE object_name NOT IN ( SELECT /*+CARDINALITY(A,10000)*/ column_value
18 FROM TABLE ( CAST ( my_ary AS SYS.dbms_debug_vc2coll ) ) a
);
19
20 END;
21 /
PL/SQL procedure successfully completed.
SQL>
SQL> PRINT :my_cur
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
AA_OW_EXTRACT
31545 SYNONYM
11-MAR-04 11-MAR-04 2004-03-11:15:58:09 VALID N N N
SQL>
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
