Forum Stats

  • 3,768,282 Users
  • 2,252,770 Discussions
  • 7,874,514 Comments

Discussions

not in

hemant_k
hemant_k Member Posts: 389 Bronze Badge
edited Nov 24, 2021 7:20AM in SQL & PL/SQL

hi

I have a query where in I have used 'not in ' construct

It takes a lot of time ..

any other way to get the result faster?

e.g.

select custno from cust_mast where custno not in (select custno from other_mast)

please help

Comments

  • User_H3J7U
    User_H3J7U Member Posts: 640 Silver Trophy

    minus.

    If custno can be null, the result will be different.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy

    There is the alternative of using not exists.

    select custno from cust_mast cm

    where not exists in (select 1 from other_mast om where cm.custno=om.custno)

    ;

    Eventually you may try the hint no_unnest, which may work reasonably if you have an index on custno in other_mast:

    select custno from cust_mast cm

    where not exists in (select /*+ no_unnest */ 1 from other_mast om where cm.custno=om.custno)

    ;

    Anyway, an index on custno in other_mast will help.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,474 Red Diamond

    The problem with "not in" is that for some thing to be determined as not being in a set of things, then everything in that set of things has to be checked. It's not like "in" or "exists" where you can stop looking as soon as you find a match.

    As BEDE says, having an index on custno will help.

    It depends what you're ultimately trying to achieve. MINUS, as user_<whatever> suggests is an option, but if you're actual query contains other columns then you could use a left outer join e.g.

    select m.custno, m.other_column ... etc.
    from cust_mast m
         left outer join other_mast o on (o.custno = m.custno)
    where o.custno is null -- where m.custno is not in o.custno set
    


  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,786 Gold Crown


    It's not possible to an appropriate answer to this question, because you haven't supplied know enough information. It's possible that the query is already running as quickly as it could - without seeing the execution plan, knowing the version, know what indexes exist, whether or not custno is declared NOT NULL on either table, and what uniqueness constraints are in place, the volume of data and what you're doing to get the data into a client program from the server we can't say much about "fastest".

    BluShadow has described why the the operation is necessary a relatively expensive one - but maybe Oracle is running extremely efficiently and the result set appears only slowly because you're fetching it one row at a time over a slow networks.

    Regards

    Jonathan Lewis

  • mathguy
    mathguy Member Posts: 10,153 Blue Diamond

    The problem with "not in" is that for some thing to be determined as not being in a set of things, then everything in that set of things has to be checked. It's not like "in" or "exists" where you can stop looking as soon as you find a match.

    That makes no sense.

    For any given condition, some rows will satisfy it and some won't, and the condition must be evaluated for ALL rows. For an "in" or "exists" conditions, you can stop looking as soon as you find a match - so that will save time for the rows that DO satisfy the condition, but it won't save any time for those that don't. To say with certainty that a row fails the condition, you still must check all the values in the IN list. Similarly for a "not in" or "not exists" condition, **you can stop looking as soon as you find a match** - that will save time for the rows that DO NOT satisfy the condition, but it won't save any time for those that do.

    The work required by a "not in" condition is exactly the same as for the "in" condition that has the same "in" ("not in") list.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,474 Red Diamond

    Apologies Mathguy, I wasn't clear (as usual LOL! 😀)

    If there is an index on the column in question then using IN will be able to use the index to lookup the value pretty efficiently. Whereas using NOT IN cannot use the index as efficiently and usually results in a full scan (depends on uniqueness of the value, so if a primary key it's more likely). At least that's what I was taught at Oracle University.... Can't honestly say I've gone ahead and put it to the test; I usually avoid NOT IN for most queries unless I know it's small sets of data. 😉