| alternate option for IN keyword [message #112633] |
Mon, 28 March 2005 08:07  |
rajuakula
Messages: 63
Registered: March 2005
|
Member |
|
|
Hi,
I have a string as ('a1','b1','c1','d1',......till 3000 values)
Now I have to pass this string in the where clause of the select query as
select * from temp where id in ('a1','b1','c1','d1',.....till 3000 values)
Heard that IN keyword does not support more than 1000 values, what shouldI use instead of IN keyword.Is there a alternate approach.I am using oracle 9i.
thanks,
|
|
|
|
|
|
| Re: alternate option for IN keyword [message #112641 is a reply to message #112635] |
Mon, 28 March 2005 09:03  |
rajuakula
Messages: 63
Registered: March 2005
|
Member |
|
|
My values may contain more than 4000 characters also and also
I dont want to use create type.How can I do it in a select query without create type option.
My IN values are ('afdfdf232','fdfd5454','cvjv88df',...till 3,000)
so they may be more than 4000 characters also.
thanks
|
|
|
|
| Re: alternate option for IN keyword [message #112645 is a reply to message #112641] |
Mon, 28 March 2005 09:21 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
Hi
I don't know if it has the same limits, but try:
select * from temp where id = any('a1','b1','c1','d1',.....till 3000 values)
I think that there is an added bonus in that IN does not use Indexes (if a column has been indexed) whereas = ANY will utilise indexes where available.
HTH
Jim
|
|
|
|
| Re: alternate option for IN keyword [message #112651 is a reply to message #112645] |
Mon, 28 March 2005 10:17 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
Hi,
I'm sorry, but this is wrong. IN and = ANY are the same and
are just different forms of in-list operator.
"IN does not use Indexes (if a column has been indexed) whereas = ANY will utilise indexes where available".
It's not so.
SQL> select object_id from nc_params where object_id in (1000,101,8989,829389);
OBJECT_ID
----------
101
101
101
1000
1000
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=31 Bytes=372)
1 0 INLIST ITERATOR
2 1 INDEX (RANGE SCAN) OF 'XIF11NC_PARAMS' (NON-UNIQUE) (Cos
t=3 Card=31 Bytes=372)
SQL> select object_id from nc_params where object_id = any (1000,101,8989,829389)
2 /
OBJECT_ID
----------
101
101
101
1000
1000
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=31 Bytes=372)
1 0 INLIST ITERATOR
2 1 INDEX (RANGE SCAN) OF 'XIF11NC_PARAMS' (NON-UNIQUE) (Cos
t=3 Card=31 Bytes=372)
Rgds.
|
|
|
|
|
|
| Re: alternate option for IN keyword [message #112678 is a reply to message #112641] |
Mon, 28 March 2005 12:15 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
| Quote: |
I don't want to use create type
|
Why not? These are generic types that you will be using time and time again. In fact I would be surprised if your application does not already have an 'array of VARCHAR2' collection type already defined.
Oracle already has a few such as sys.DBMS_DEBUG_VC2COLL or sys.KU$_VCNT (though of course those might change in later releases so perhaps not such a great idea).
SQL> SELECT column_value
2 FROM TABLE(sys.KU$_VCNT('x','y','z'));
COLUMN_VALUE
------------------------------
x
y
z
3 rows selected.
SQL> SELECT column_value
2 FROM TABLE(sys.DBMS_DEBUG_VC2COLL('x','y','z'))
3 /
COLUMN_VALUE
------------------------------
x
y
z
3 rows selected. |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
