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!

Dynamic SQL creation ?

Discussion in 'SQL PL/SQL' started by O Coder, Jan 23, 2014.

  1. O Coder

    O Coder Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have a table of 3 billion unique records. I access it in parallel full table scans by partitions since indexes are not available for my criteria and I do not have create index privileges. My criteria is a list of 1,000 alphanumeric codes which are each three characters in length. My list of codes is stored in a table as varchar2.

    The challenge is to CREATE a table (so PL/SQL is out) based on finding the presence of the codes within the large table. However, any one of those codes may exist in one or more of 12 attributes on the large table. Joining the large table to the code table on those 12 fields is simple enough, but is not practical with concern to resources. 12 "ORs" = 12 full table scans = real bad.

    The best I can think to do is to create a SELECT statement that turns the 1,000 codes into a concatenated string of text by doing a PIVOT. And then copy that string over as an input variable into my CREATE statement's WHERE clause and run it within a command window in PL/SQL Developer. Which seems to work, accept that the length of a string within an input variable is limited to 1000 characters or thereabouts. So it won't pass all of my codes. Remember, I'm having to wrap each three character code with parenthesis to use them in the IN () of the WHERE clause. I've tried breaking into multiple strings, each under 1000 characters in separate input variables and that works but is an absolute nightmare to maintain and update. The list of 1000 codes can and will change both in length and content over time. I'm so desperate that I'm on the verge of scripting something in Python to create the SQL script for me to run.

    Here's what that basically looks like:

    Code (SQL):
    DEFINE v_codes1=" '5pd','555','6rt','qw1' "
    DEFINE v_codes2=" 'epd','54a','zzt','007' "
    DEFINE v_codes3=" '2rd','p2p','wee','q24' "

    SELECT t.something
    FROM largetable t
    WHERE
    t.codefield1 IN (&v_codes1&v_codes2&v_codes3.) OR
    t.codefield2 IN (&v_codes1&v_codes2&v_codes3.) OR
    t.codefield3 IN (&v_codes1&v_codes2&v_codes3.) OR
    t.codefield4 IN (&v_codes1&v_codes2&v_codes3.) OR
    t.codefield5 IN (&v_codes1&v_codes2&v_codes3.) OR
    t.codefield6 IN (&v_codes1&v_codes2&v_codes3.) OR
    t.codefield7 IN (&v_codes1&v_codes2&v_codes3.) OR
    t.codefield8 IN (&v_codes1&v_codes2&v_codes3.) OR
    t.codefield9 IN (&v_codes1&v_codes2&v_codes3.) OR
    t.codefield10 IN (&v_codes1&v_codes2&v_codes3.) OR
    t.codefield11 IN (&v_codes1&v_codes2&v_codes3.) OR
    t.codefield12 IN (&v_codes1&v_codes2&v_codes3.)
    Any suggestions? Please?
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Is this 'challenge' to be a one time thing or repeatable? Just curious....
     
  3. O Coder

    O Coder Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Yeah, it needs to be repeatable. And will be repeated monthly. Ideally in a way that can be automated (but I can handle that part). I'd prefer to stay within Oracle, but as I said, I am open to other solutions/suggestions.
     
  4. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Well...I know nothing of analytics so I'll fall back on what I'm familiar with. You can't create an index and you indicate there are no indexes available...have you given any thought to using the ROWID? As you cannot create an index, can you add a flag column to the source table? For either approach, regardless of whether the code appears in the row you only need to tap the row if it hasn't been tapped before. This is more a conquer by attrition approach rather than one fell swoop.

    Note, this only applies if an analytics type solution isn't faster... ;-)
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    absolutely the universal decisions don't happen.....

    p.s.
    clause IN has restriction... it not the best method of the decision when is a lot of search elements


    for example (CBO hints only for this example)
    Code (SQL):


    DROP TABLE list_code;
    CREATE TABLE list_code AS
    SELECT
      dbms_random.string('x',3) code
    FROM dual CONNECT BY level < 1001;
    DROP TABLE big_table;
    CREATE TABLE big_table AS
    SELECT --+ no_merge no_unnest
       level id,
       dbms_random.string('x',3) attr1,
       dbms_random.string('x',3) attr2,
       dbms_random.string('x',3) attr3,
       dbms_random.string('x',3) attr4,
       dbms_random.string('x',3) attr5,
       dbms_random.string('x',3) attr6,
       dbms_random.string('x',3) attr7,
       dbms_random.string('x',3) attr8,      
       dbms_random.string('x',3) attr9,              
       dbms_random.string('x',3) attr10,
       dbms_random.string('x',3) attr11,
       dbms_random.string('x',3) attr12            
    FROM dual CONNECT BY level < 100001;
    SELECT --+ no_expand parallel (bt)
    lc.code,bt.* FROM big_table bt ,list_code lc
    WHERE lc.code IN (attr1,attr2,attr3,attr4,attr5,attr6,attr7,attr8,attr9,attr10,attr11,attr12)
    AND rownum <11;

    SQL>
     
    TABLE dropped
     
    TABLE created
     
    TABLE dropped
     
    TABLE created
     
    CODE                                                                                     ID ATTR1                                                                            ATTR2                                                                            ATTR3                                                                            ATTR4                                                                            ATTR5                                                                            ATTR6                                                                            ATTR7                                                                            ATTR8                                                                            ATTR9                                                                            ATTR10                                                                           ATTR11                                                                           ATTR12
    -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    HU4                                                                                   21000 35Y                                                                              B1F                                                                              ONP                                                                              8QY                                                                              RVX                                                                              OXN                                                                              NXG                                                                              BGZ                                                                              5LY                                                                              GF7                                                                              HU4                                                                              C7S
    QNX                                                                                   21001 V1N                                                                              J4N                                                                              NPB                                                                              GLV                                                                              4N8                                                                              17H                                                                              B8Q                                                                              XSH                                                                              N5I                                                                              UKX                                                                              QNX                                                                              K71
    0IV                                                                                   21020 3FJ                                                                              7PS                                                                              3KY                                                                              4FO                                                                              2Z2                                                                              JMI                                                                              Q07                                                                              HB4                                                                              6LH                                                                              9O4                                                                              JSQ                                                                              0IV
    LBD                                                                                   21024 SVF                                                                              WPE                                                                              ZHM                                                                              1KV                                                                              68W                                                                              IP2                                                                              QA9                                                                              LBD                                                                              BJX                                                                              VXK                                                                              ROO                                                                              ILW
    C2P                                                                                   21032 LYD                                                                              VDB                                                                              C2P                                                                              CNY                                                                              WPZ                                                                              NI3                                                                              29C                                                                              8CW                                                                              GWN                                                                              S7K                                                                              P8G                                                                              UEB
    E1H                                                                                   21049 1OG                                                                              EGK                                                                              JOR                                                                              L56                                                                              X57                                                                              PI5                                                                              2SN                                                                              84W                                                                              AAE                                                                              8YI                                                                              2YY                                                                              E1H
    GU5                                                                                   21055 UHJ                                                                              GU5                                                                              KRK                                                                              8CK                                                                              CPN                                                                              K41                                                                              RX7                                                                              EZ8                                                                              2E7                                                                              IAV                                                                              8UZ                                                                              E42
    EZ8                                                                                   21055 UHJ                                                                              GU5                                                                              KRK                                                                              8CK                                                                              CPN                                                                              K41                                                                              RX7                                                                              EZ8                                                                              2E7                                                                              IAV                                                                              8UZ                                                                              E42
    KZI                                                                                   21059 Q9M                                                                              KZI                                                                              LR3                                                                              117                                                                              DJY                                                                              844                                                                              SN3                                                                              OP0                                                                              W9T                                                                              GCR                                                                              QB3                                                                              ZQA
    XE5                                                                                   21069 SSU                                                                              LHE                                                                              9GI                                                                              Z40                                                                              S67                                                                              VOU                                                                              AXJ                                                                              YGG                                                                              91O                                                                              XE5                                                                              ONU                                                                              31W
     
    10 ROWS selected
     

     
     
    O Coder likes this.