Oracle PL/SQL where clause

[DonN_2]

Is there a maximum number of entries that can be included in a where element in ( ) statement?

I am using a nested where statement as follows:

select unique node_id from loc_node where version = 'x' and node_id not in (select p1_node from loc_geoline where version = 'x');

I know that there are some elements of node_id that will get filtered out using this select, but there are a number that should not be filtered, however the query returns 'No Rows Selected' I was able to get teh query to run successfuly by limiting the number of items in the sub select statement.

If there is a limit what is the number? I could not find one in any of my basic web searches.

Thanks,

Don

[TwoProc]

I don't see that you are running across some sort of limit. If you were, then you'd most certainly have a syntax error.


When since you're using "NOT IN" when you filtered the sub-select from loc_geoline to reduce the number of rows returned in the subselect - what you did was make the selected amount in the outer select NOT lose all of its matches.

What I'm saying is, you have more matches in top layer select because you didn't subtract them with the inner NOT IN selection statement.

simplified example of what you've got:


LOC_NODE has node_ids with version of 'x':

1 1 2 2 3 4 5 6
loc_geoline has p1_nodes with version of 'x'
6 5 3 4 5 5 2 1 6

When you run your query as above, you'll get "No Rows Returned", but if you limit the number of rows in the sub select...

select unique node_id from loc_node where version = 'x' and node_id not in (select p1_node from loc_geoline where version = 'x' and rownum < 6 );


Your now would have return values for two node_ids, 1 and 2.


And, while we're at it, the "NOT IN" tool has never been a good idea. Nowadays it is preferred to use the "NOT EXISTS" method. Often times, however, your optimizer may fix this up for you. Prior to "NOT EXISTS" the preferred method was "0 = select count(*)" show below, or another way was to just use set manipulation. Just for tuning suggestions, I've included some thoughts below, which really have nothing to do with your question, but I'm just suggesting as alternatives.


Set manipulation example:

select unique node_id as unique_node_id
from loc_node
where version = 'x'
MINUS
select unique p1_node as unique_node_id
from loc_geoline
where version = 'x';


Or, try this one which eliminates the not in clause (old way of eliminating not in):

select ln.unique node_id
from loc_node ln
where ln.version = 'x'
and 0 =
(select count(*)
from loc_geoline lg
where
lg.version = 'x'
and ln.loc_node=lg.p1_node
);


Similarly you can use not exists (preferred way of eliminating NOT IN filter):

select ln.unique node_id
from loc_node ln
where ln.version = 'x'
and NOT EXISTS
(select 'X'
from loc_geoline lg
where
lg.version = 'x'
and ln.loc_node=lg.p1_node
);

For the two above, depending on indexing,
you may have better luck with joining the .version columns of the two tables...

select ln.unique node_id
from loc_node ln
where ln.version = 'x'
and 0 =
(select count(*)
from loc_geoline lg
where
lg.version = ln.version
and ln.loc_node=lg.p1_node
);

select ln.unique node_id
from loc_node ln
where ln.version = 'x'
and NOT EXISTS
(select 'X'
from loc_geoline lg
where
lg.version = ln.version
and ln.loc_node=lg.p1_node
);


Hope this helps.

[Hein van den Heuvel]

Hmm,

This is 99.99% sure to be an Oracle question having noting to do with HPUX.

You may want to try an Oracle forum, after looking up existing topics on the subject matter.

Oracle does not have (silent) limits on selected rows, or it would generate and error

As you prepare your question for an oracle forum you may want to add how you limited the sub-query result set. And just to satisfy curiosity, you may want to indicate the Oracle version, and the order of magnitude of the source tables and results expected.
Are we talking thousands, millions or billions?

In fact, you may want to isolate the sub-query and just do a select count(*) to see if the results are as expected.

Along the same lines, generate a query plan and see whether it makes sense towards what you want to find.

Some suggested reading:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:442029737684

http://asktom.oracle.com/pls/asktom/f?p=100:11:2686627283373693::::P11_QUESTION_ID:7565644027229

Good luck,
Hein