Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: SP data collection

    (i am trying to read TFM but ...)
    I am kinda new to collections.

    How could I throw the below data into one collection and then
    match the collection against another table?

    PHP Code:
    topicadm@Topic_Devselect distinct DATA_TYPE_SID from TATTRIBUTE;

    DATA_TYPE_SID
    -------------
                
    1
                2
                8
               10

    Elapsed
    00:00:00.00
    topicadm
    @Topic_Devselect distinct LIFECYCLE_STATE_SID from TATTRIBUTE;

    LIFECYCLE_STATE_SID
    -------------------
                    
    433
                    434 

    I wrote this code not knowing what the hell I am doing:
    PHP Code:
    declare
     
    type vtest is table of tlookup_value.lookup_value_sid%type;
     
    vlist vtest;
    begin

     select distinct DATA_TYPE_SID
      into vlist
       from TATTRIBUTE
    ;

     
    select distinct LIFECYCLE_STATE_SID
      into vlist
       from TATTRIBUTE
    ;

     for 
    i in 1..vlist.COUNT
     loop

      dbms_output
    .putline(to_char(vlist));
     
    end loop;

    end
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You would use BULK COLLECT INTO to fill an array:
    Code:
    select distinct DATA_TYPE_SID
     bulk collect into vlist
       from TATTRIBUTE;
    But your second select will overwrite your first.

    You could get them all like this:
    Code:
    select distinct DATA_TYPE_SID
     bulk collect into vlist
       from TATTRIBUTE
    UNION 
      select distinct LIFECYCLE_STATE_SID
       from TATTRIBUTE;
    Now what do you want to do with them?

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    I can't keep adding/inserting into the collection?????
    bah!! I might as well create a crazy cursor like I was already doing.

    I have about 50 columns of data that I want to create one collection for.
    At that point I need to cross reference that collection with a table column
    in order to get a list of all values that do not exist.

    ie:
    PHP Code:
    select lookup_value_sidlookup_value from tlookup_value 
    where lookup_value_sid not in 
    (select ATTRIBUTE_CLASS_SID from TATTRIBUTE); 
    where the subquery would actually be my collection.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    i got the test collection to spit out.
    interesting stuff

    corrected code below
    PHP Code:
     declare
      
    type vtable is table of tlookup_value.lookup_value_sid%type;
      
    vdata vtable;
     
    begin

      select distinct DATA_TYPE_SID
       bulk collect into vdata
        from TATTRIBUTE
    ;


      for 
    i in vdata.FIRST..vdata.LAST
      loop

       dbms_output
    .put_line(to_char(vdata(i)));
      
    end loop;

     
    end
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The easiest way would be:

    Code:
    select lookup_value_sid, lookup_value from tlookup_value
    where lookup_value_sid not in
    ( select ATTRIBUTE_CLASS_SID 
      from TATTRIBUTE
      UNION
      select LIFECYCLE_STATE_SID
      from TATTRIBUTE
    );
    If you want to use a collection in a SELECT then the collection type must be defined in the server. Here is an example using the good old EMP and DEPT tables:
    Code:
    SQL> create type deptno_tab_type as table of number;
      2  /
    
    Type created.
      1  declare
      2    deptno_tab deptno_tab_type;
      3  begin
      4    select deptno
      5    bulk collect into deptno_tab
      6    from dept;
      7    for r in (select ename from emp
      8              where deptno in (select * from table(cast(deptno_tab as deptno_tab_type))))
      9    loop
     10      dbms_output.put_line(r.ename);
     11    end loop;
     12* end;
    SQL> /
    CLARK
    KING
    MILLER
    SMITH
    ADAMS
    FORD
    SCOTT
    JONES
    ALLEN
    BLAKE
    MARTIN
    JAMES
    TURNER
    WARD
    
    PL/SQL procedure successfully completed.
    Not pretty is it? This is the crucial part:
    Code:
    select * from table(cast(deptno_tab as deptno_tab_type))
    Not really sure why Oracle insists that you cast a variable from its own type to the same type, but there you go.

    BTW, you can keep adding to a collection, but not via the INTO syntax. You could use a cursor for loop like this:

    Code:
    FOR r IN (select distinct LIFECYCLE_STATE_SID from TATTRIBUTE
    LOOP
      vlist(vlist.COUNT+1) := r.LIFECYCLE_STATE_SID;
    END LOOP;

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    thanks for the lesson.

    i've been using the union subquery as you mention.
    unfortunately, once you start 'unioning' tables with millions of rows
    it becomes VERY inneficient.

    At that point I started writing NOT EXISTS subqueries within each other like
    below which turned out to be much more effective performance-wise.

    I was hoping that with collections it might be faster to create the HUGE collection
    and then compare against the master table.
    PHP Code:
    select a.INTEROP_NOTE_SID from 
    (select a.INTEROP_NOTE_SID from 
    (select a.INTEROP_NOTE_SID from 
    (select a.INTEROP_NOTE_SID from 
    (select a.INTEROP_NOTE_SID from 
    (select a.INTEROP_NOTE_SID from 
    (select INTEROP_NOTE_SID from TINTEROPERABILITY_NOTES /* master table right here */ a     
    where not exists 
    (select null from TA_CONFIG_COMP_NOTE b 
                                where a
    .INTEROP_NOTE_SID b.INTEROP_NOTE_SID)) a
    where not exists 
    (select null from TCONFIG_COMP_NOTE b 
                                where a
    .INTEROP_NOTE_SID b.INTEROP_NOTE_SID)) a
    where not exists 
    (select null from TCOMP_ATTR_VAL_NOTE b 
                                where a
    .INTEROP_NOTE_SID b.INTEROP_NOTE_SID)) a
    where not exists 
    (select null from TCOMP_NOTE b
                                where a
    .INTEROP_NOTE_SID b.INTEROP_NOTE_SID)) a
    where not exists 
    (select null from TCONFIG_ATTR_VAL_NOTE b
                                where a
    .INTEROP_NOTE_SID b.INTEROP_NOTE_SID)) a
    where not exists 
    (select null from TCONFIG_COMP_ATTR_VAL_NOTE b
                                where a
    .INTEROP_NOTE_SID b.INTEROP_NOTE_SID)) a
    where not exists 
    (select null from TCONFIG_NOTE b
                                where a
    .INTEROP_NOTE_SID b.INTEROP_NOTE_SID); 
    Last edited by The_Duck; 07-28-04 at 13:37.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How about:

    select INTEROP_NOTE_SID from TINTEROPERABILITY_NOTES
    MINUS
    select INTEROP_NOTE_SID from TA_CONFIG_COMP_NOTE
    MINUS
    select INTEROP_NOTE_SID from TCOMP_ATTR_VAL_NOTE
    MINUS
    select INTEROP_NOTE_SID from TCOMP_NOTE
    MINUS
    select INTEROP_NOTE_SID from TCONFIG_ATTR_VAL_NOTE
    MINUS
    select INTEROP_NOTE_SID from TCONFIG_NOTE
    /

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    your query (takes longer, higher cost but less logical gets):
    Code:
    Elapsed: 00:01:47.00
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1152367 Card=4338 Bytes=371314810)
       1    0   MINUS
       2    1     MINUS
       3    2       MINUS
       4    3         MINUS
       5    4           MINUS
       6    5             SORT (UNIQUE) (Cost=14 Card=4338 Bytes=21690)
       7    6               INDEX (FAST FULL SCAN) OF 'PK_TINTEROPERABILITY_NOTES' (UNIQUE) (Cost=2 Card=4338 Byt
              s=21690)
    
       8    5             SORT (UNIQUE) (Cost=1138584 Card=72326950 Bytes=361634750)
       9    8               PARTITION HASH (ALL)
      10    9                 BITMAP CONVERSION (TO ROWIDS)
      11   10                   BITMAP INDEX (FULL SCAN) OF 'IDX_A_CFGCN_NOTE_SID'
      12    4           SORT (UNIQUE NOSORT) (Cost=3 Card=155 Bytes=775)
      13   12             INDEX (FULL SCAN) OF 'CAVN_IN_FK' (NON-UNIQUE) (Cost=1 Card=155 Bytes=775)
      14    3         SORT (UNIQUE NOSORT) (Cost=4 Card=472 Bytes=2360)
      15   14           INDEX (FULL SCAN) OF 'CPM_IN_FK' (NON-UNIQUE) (Cost=3 Card=472 Bytes=2360)
      16    2       SORT (UNIQUE) (Cost=7311 Card=1025962 Bytes=5129810)
      17   16         INDEX (FAST FULL SCAN) OF 'CFAVN_IN_FK' (NON-UNIQUE) (Cost=276 Card=1025962 Bytes=512981
      18    1     SORT (UNIQUE) (Cost=6451 Card=905085 Bytes=4525425)
      19   18       INDEX (FAST FULL SCAN) OF 'CFN_IN_FK' (NON-UNIQUE) (Cost=244 Card=905085 Bytes=4525425)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
          14852  consistent gets
              0  physical reads
              0  redo size
          16791  bytes sent via SQL*Net to client
           1172  bytes received via SQL*Net from client
            130  SQL*Net roundtrips to/from client
              4  sorts (memory)
              0  sorts (disk)
           1925  rows processed
    my query (takes one second but higher logical gets)
    Code:
    1515 rows selected.
    
    Elapsed: 00:00:01.04
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=1 Bytes=5)
       1    0   FILTER
       2    1     INDEX (FAST FULL SCAN) OF 'PK_TINTEROPERABILITY_NOTES' (UNIQUE) (Cost=2 Card=1 Bytes=5)
       3    1     INDEX (RANGE SCAN) OF 'CFN_IN_FK' (NON-UNIQUE) (Cost=10 Card=2702 Bytes=13510)
       4    1     INDEX (RANGE SCAN) OF 'CFCAVN_IN_FK' (NON-UNIQUE) (Cost=8 Card=2084 Bytes=10420)
       5    1     INDEX (RANGE SCAN) OF 'CFAVN_IN_FK' (NON-UNIQUE) (Cost=12 Card=3420 Bytes=17100)
       6    1     INDEX (RANGE SCAN) OF 'CPM_IN_FK' (NON-UNIQUE) (Cost=1 Card=2 Bytes=10)
       7    1     INDEX (RANGE SCAN) OF 'CAVN_IN_FK' (NON-UNIQUE) (Cost=1 Card=3 Bytes=15)
       8    1     INDEX (RANGE SCAN) OF 'CFCN_IN_FK' (NON-UNIQUE) (Cost=12 Card=3344 Bytes=16720)
       9    1     PARTITION HASH (ALL)
      10    9       BITMAP CONVERSION (TO ROWIDS)
      11   10         BITMAP INDEX (SINGLE VALUE) OF 'IDX_A_CFGCN_NOTE_SID'
    
    
    
    
    Statistics
    ----------------------------------------------------------
             49  recursive calls
              0  db block gets
          94677  consistent gets
              0  physical reads
              0  redo size
          13650  bytes sent via SQL*Net to client
            976  bytes received via SQL*Net from client
            102  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
           1515  rows processed
    Last edited by The_Duck; 07-28-04 at 13:50.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I can see why you might prefer your version...

    Still seems rather verbose though. What about:
    Code:
    select INTEROP_NOTE_SID from TINTEROPERABILITY_NOTES a
    where  not exists (select null from TA_CONFIG_COMP_NOTE b where a.INTEROP_NOTE_SID = b.INTEROP_NOTE_SID)
    and    not exists (select null from TCOMP_ATTR_VAL_NOTE b where a.INTEROP_NOTE_SID = b.INTEROP_NOTE_SID)
    and    not exists (select null from TCOMP_NOTE b where a.INTEROP_NOTE_SID = b.INTEROP_NOTE_SID)
    and    not exists (select null from TCONFIG_ATTR_VAL_NOTE b where a.INTEROP_NOTE_SID = b.INTEROP_NOTE_SID)
    and    not exists (select null from TCONFIG_NOTE b where a.INTEROP_NOTE_SID = b.INTEROP_NOTE_SID)

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    doh!
    that saves tons of room and looks way better to me.
    I'll run some tests with the full query and see.
    Thanks for the help!!

    Code:
    Elapsed: 00:00:01.06
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=1 Bytes=5)
       1    0   FILTER
       2    1     INDEX (FAST FULL SCAN) OF 'PK_TINTEROPERABILITY_NOTES' (UNIQUE) (Cost=2 Card=1 Bytes=5)
       3    1     PARTITION HASH (ALL)
       4    3       BITMAP CONVERSION (TO ROWIDS)
       5    4         BITMAP INDEX (SINGLE VALUE) OF 'IDX_A_CFGCN_NOTE_SID'
       6    1     INDEX (RANGE SCAN) OF 'CFCN_IN_FK' (NON-UNIQUE) (Cost=12 Card=3344 Bytes=16720)
       7    1     INDEX (RANGE SCAN) OF 'CAVN_IN_FK' (NON-UNIQUE) (Cost=1 Card=3 Bytes=15)
       8    1     INDEX (RANGE SCAN) OF 'CPM_IN_FK' (NON-UNIQUE) (Cost=1 Card=2 Bytes=10)
       9    1     INDEX (RANGE SCAN) OF 'CFAVN_IN_FK' (NON-UNIQUE) (Cost=12 Card=3420 Bytes=17100)
      10    1     INDEX (RANGE SCAN) OF 'CFCAVN_IN_FK' (NON-UNIQUE) (Cost=8 Card=2084 Bytes=10420)
      11    1     INDEX (RANGE SCAN) OF 'CFN_IN_FK' (NON-UNIQUE) (Cost=10 Card=2702 Bytes=13510)
    
    
    
    
    Statistics
    ----------------------------------------------------------
             49  recursive calls
              0  db block gets
          97905  consistent gets
              0  physical reads
              0  redo size
          13650  bytes sent via SQL*Net to client
            976  bytes received via SQL*Net from client
            102  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
           1515  rows processed
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •