1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Limitation of IN Operator In SQL

Discussion in 'SQL PL/SQL' started by jagadekara, Aug 6, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    Today I came to know that

    Maximum Number Of Expressions in a List is 1000 to use IN Operator in SQL.

    If we use 1001 expressions in the list then it shows the following error.

    ORA-01795: Maximum Number Of Expressions in a List is 1000

    example:

    SELECT * FROM ap_invoices_all aia
    where 1=1
    and invoice_id in (11099,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101
    ,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103
    ,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101
    ,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103
    ,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101
    ,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103
    ,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101
    ,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103
    ,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101
    ,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103
    ,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101
    ,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103
    ,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101
    ,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103
    ,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101
    ,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103
    ,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101
    ,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103
    ,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101
    ,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103
    ,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101
    ,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103
    ,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100
    ,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102
    ,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104
    ,11100,11101,11102,11103,11104,11100,11101,11102,11103,11104,11100,11101,11102,11103,1)
    ;


    So what will we do in this situation?

    Note: It's not my requirement. It's just to know and share the information.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Yes, this limitation in the system.
    To work around it, you need to use ...

    1) in (select id from tab ).....if a lot of values​​, then it is not the best solution

    2) Use a temporary table (gtt on commit preserve ) to pass parameters to a query
    Code (SQL):


    SET echo off
    rem ddl
    DROP TABLE params purge;
    CREATE global TEMPORARY TABLE params
    (param_name             varchar2(30 CHAR),
     param_num_value        NUMBER
    )ON commit preserve ROWS;
    CREATE INDEX param_i1 ON params(param_name);
    rem ins
    INSERT INTO params
    SELECT 'my_param' , level FROM dual CONNECT BY level < 1002;
    commit;
    rem SQL
    WITH
    simple_data AS (
    SELECT
        level id
    FROM dual
    CONNECT BY level < 11
    )
    SELECT
        p.param_num_value , sd.id
    FROM params p,simple_data sd
    WHERE p.param_num_value = sd.id
          AND
          p.param_name = 'my_param';
     
    3) use collection
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Sergey,

    I used like this...

    SELECT * FROM ap_invoices_all aia
    where 1=1
    and aia.invoice_id IN (select aia1.invoice_id from ap_invoices_all aia1 where rownum<1005);



    And I didn't understand your second point clearly. There is no IN parameter.

    How to do it by collection?
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Here option with IN use

    Code (SQL):


    SET echo off
    rem ddl
    DROP TABLE params purge;
    CREATE global TEMPORARY TABLE params
    (param_name             varchar2(30 CHAR),
     param_num_value        NUMBER
    )ON commit preserve ROWS;
    CREATE INDEX param_i1 ON params(param_name);
    rem ins
    INSERT INTO params
    SELECT 'my_param' , level FROM dual CONNECT BY level < 1002;
    commit;
    rem SQL
    WITH
    simple_data AS (
    SELECT
        level id
    FROM dual
    CONNECT BY level < 11
    )
    SELECT
        p.param_num_value , sd.id
    FROM simple_data sd
    WHERE sd.id IN (SELECT p.param_num_value FROM params WHERE   p.param_name = 'my_param');

     

    if a lot of values​​, then it is not the best solution...
    and it is better for JOIN to use, instead of SEMI-JOIN


    3)
    here I said that it is possible to transmit parameters through a collection.....

    Code (SQL):


    SET serveroutput ON
    CREATE OR REPLACE PROCEDURE prc_show_tab (p_tab sys.odcinumberlist )
    AS  
    BEGIN    
        FOR z IN(
            WITH simple_data AS (SELECT level id  FROM dual CONNECT BY level < 11)
            SELECT
                 id
            FROM simple_data WHERE id IN (SELECT column_value FROM TABLE(p_tab))      
            )
        loop
           dbms_output.put_line (z.id);
        END loop;  
    END;
    /
    EXEC prc_show_tab(sys.odcinumberlist(1,2,3,4,5,6,7))
       
     
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thank you very much Sergey.
     
  6. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Code (Text):
    So what will we do in this situation?
    There are always limits. Generally in Oracle, the limits are set so high that if you hit them (or even get close to them)... you probably are doing something that is really not a good idea. An IN-list with several hundred values is almost certain to perform poorly. Hitting the thousand-item limit then means that you have SQL that really ought to be written using some other logic. Sergey gave some theoretical examples, but in reality the answer is going to be dependent on the specific query you need to create that could theoretically be done (but shouldn't be) using an IN list of hundreds of individual values.
     
  7. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi jagadekar ,

    In this situation you can use 'Exists' operator instead of 'IN' operator .

    Code (SQL):
     SELECT *
    FROM     ap_invoices_all aia
    WHERE  1=1
     AND     EXISTS ( SELECT 'X' FROM ap_invoices_all aia1 WHERE aia1.invoice_id = aia.invoice_id);

    Regards
    Samba