Returning same row n amount of times.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Returning same row n amount of times.

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Returning same row n amount of times.

    Got a developer who wants to create a table with up to 300 dummy rows in it to help him print a report (well, labels actually). The user in the app needs say 15 labels, so his query goes and gets the label data and then joings the 1 row of label data with 15 dummy rows from the dummy table. So the query actually returns 15 rows, not 1. The same label is thus printed 15 times. I have no wish or desire to get involved as I'm not responsible for this Db directly.

    However, I'd like to know if there's a pure SQL (no PL/SQL) way of saying "Retrieve me that 1 row there, but return it to to me 15 times"?

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    How about a cartesian product with a big enough table and limit with rownum:

    select empname
    from emp, all_objects
    where empid = 123
    and rownum <= 15;

    (I'm sure Tamil will show us how to do this with CUBE!)

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Dapi will be disappointed if I do not give my script.

    PHP Code:
    select A.*
    from select id 
             from test_load 
            where id 
    10 A, ---- this will return one row
         
    select 1 
             from dual 
            group by cube
    (1,1,1,1)) B
    where rownum 
    16

    Tamil

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I wonder if it might be better expressed as ...
    Code:
    select empname
    from emp, 
    (select 1 from all_objects where rownum <= 15) num_list
    where empid = 123
    /
    It would translate more easily to different predicates like "emp_id in (1,2,3)" etc then as well.

    I usually like to have a nice big table of integers for this kind of thing ... there's generally a lot of uses for it, and it takes up very little space.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I agree slim (I'd thought of that afterwards but was too lazy . . . )

  6. #6
    Join Date
    Jan 2004
    Posts
    162
    Tested some variations of this (hey I love to test) for performance and here's what I got (YMMV).
    Code:
                         hsecs (for 1000 iterations)
    -------------------------------------------------
    approach                   8i       9.2      10g
    -------------------------------------------------
    union all dual            189       230       46
    table of integers          42        84       54
    collection type           106        70       44
    table function            156       121      109
    pipelined function          -       136       75
    group by cube             141      4033     3718
    ...and here is test script running on 10g.
    Code:
    Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> CREATE TABLE fifteen
      2  AS
      3    SELECT ROWNUM num
      4    FROM   all_objects
      5    WHERE  ROWNUM <= 15;
    
    Table created.
    
    SQL> CREATE OR REPLACE FUNCTION many (
      2    p_rows IN NUMBER)
      3    RETURN NUMBER_TABLE
      4  IS
      5    v_rtn NUMBER_TABLE := NUMBER_TABLE ();
      6  BEGIN
      7    v_rtn.EXTEND (p_rows);
      8    RETURN v_rtn;
      9  END;
     10  /
    
    Function created.
    
    SQL> CREATE OR REPLACE FUNCTION many_pipe (
      2    p_rows IN NUMBER)
      3    RETURN NUMBER_TABLE PIPELINED
      4  IS
      5  BEGIN
      6    FOR i IN 1..p_rows LOOP
      7      PIPE ROW (i);
      8    END LOOP;
      9    RETURN;
     10  END;
     11  /
    
    Function created.
    
    SQL> SET SERVEROUTPUT ON;
    SQL> DECLARE
      2    v_vc2s VARCHAR2_TABLE;
      3    v_iter PLS_INTEGER := 1000;
      4    v_time PLS_INTEGER := 0;
      5  BEGIN
      6    v_time := DBMS_UTILITY.GET_TIME;
      7    FOR i IN 1..v_iter LOOP
      8      SELECT /*+ CARDINALITY (t, 15) */ dummy
      9      BULK COLLECT INTO v_vc2s
     10      FROM   dual, (
     11             SELECT 1
     12             FROM   dual
     13             UNION ALL
     14             SELECT 2
     15             FROM   dual
     16             UNION ALL
     17             SELECT 3
     18             FROM   dual
     19             UNION ALL
     20             SELECT 4
     21             FROM   dual
     22             UNION ALL
     23             SELECT 5
     24             FROM   dual
     25             UNION ALL
     26             SELECT 6
     27             FROM   dual
     28             UNION ALL
     29             SELECT 7
     30             FROM   dual
     31             UNION ALL
     32             SELECT 8
     33             FROM   dual
     34             UNION ALL
     35             SELECT 9
     36             FROM   dual
     37             UNION ALL
     38             SELECT 10
     39             FROM   dual
     40             UNION ALL
     41             SELECT 11
     42             FROM   dual
     43             UNION ALL
     44             SELECT 12
     45             FROM   dual
     46             UNION ALL
     47             SELECT 13
     48             FROM   dual
     49             UNION ALL
     50             SELECT 14
     51             FROM   dual
     52             UNION ALL
     53             SELECT 15
     54             FROM   dual) t;
     55    END LOOP;
     56    DBMS_OUTPUT.PUT_LINE ('union all dual' || CHR (9) || (DBMS_UTILITY.GET_TIME - v_time));
     57
     58    v_time := DBMS_UTILITY.GET_TIME;
     59    FOR i IN 1..v_iter LOOP
     60      SELECT /*+ CARDINALITY (t, 15) */ dummy
     61      BULK COLLECT INTO v_vc2s
     62      FROM   dual, fifteen t;
     63    END LOOP;
     64    DBMS_OUTPUT.PUT_LINE ('table of integers' || CHR (9) || (DBMS_UTILITY.GET_TIME - v_time));
     65
     66    v_time := DBMS_UTILITY.GET_TIME;
     67    FOR i IN 1..v_iter LOOP
     68      SELECT /*+ CARDINALITY (t, 15) */ dummy
     69      BULK COLLECT INTO v_vc2s
     70      FROM   dual d, TABLE (NUMBER_TABLE (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)) t;
     71    END LOOP;
     72    DBMS_OUTPUT.PUT_LINE ('collection type' || CHR (9) || (DBMS_UTILITY.GET_TIME - v_time));
     73
     74    v_time := DBMS_UTILITY.GET_TIME;
     75    FOR i IN 1..v_iter LOOP
     76      SELECT /*+ CARDINALITY (t, 15) */ dummy
     77      BULK COLLECT INTO v_vc2s
     78      FROM   dual, TABLE (CAST (many (15) AS NUMBER_TABLE)) t;
     79    END LOOP;
     80    DBMS_OUTPUT.PUT_LINE ('table function' || CHR (9) || (DBMS_UTILITY.GET_TIME - v_time));
     81
     82    v_time := DBMS_UTILITY.GET_TIME;
     83    FOR i IN 1..v_iter LOOP
     84      SELECT /*+ CARDINALITY (t, 15) */ dummy
     85      BULK COLLECT INTO v_vc2s
     86      FROM   dual, TABLE (many_pipe (15)) t;
     87    END LOOP;
     88    DBMS_OUTPUT.PUT_LINE ('pipelined function' || CHR (9) || (DBMS_UTILITY.GET_TIME - v_time));
     89
     90    v_time := DBMS_UTILITY.GET_TIME;
     91    FOR i IN 1..v_iter LOOP
     92      SELECT /*+ CARDINALITY (t, 16) */ dummy
     93      BULK COLLECT INTO v_vc2s
     94      FROM   dual, (
     95         SELECT 1
     96         FROM   dual
     97         GROUP BY CUBE (1, 1, 1, 1))
     98      WHERE ROWNUM <= 15;
     99    END LOOP;
    100    DBMS_OUTPUT.PUT_LINE ('group by cube' || CHR (9) || (DBMS_UTILITY.GET_TIME - v_time));
    101  END;
    102  /
    union all dual  46
    table of integers       54
    collection type 44
    table function  109
    pipelined function      75
    group by cube   3718
    
    PL/SQL procedure successfully completed.
    
    SQL>

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Nice!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Nice work padders,

    In another thread we looked at the CUBE method. I was surprised how much resorces it required - time & temp space.

    Would you be very nice and time the direct select from all_objects in your systems so we can compare. i.e. using:
    (select 1 from all_objects where rownum <= 15)
    as slim suggested. TIA

  9. #9
    Join Date
    Jan 2004
    Posts
    162
    Yes, missed those by accident (no offence). Added these two tests...
    Code:
      v_time := DBMS_UTILITY.GET_TIME;
      FOR i IN 1..v_iter LOOP
        SELECT dummy
        BULK COLLECT INTO v_vc2s
        FROM   dual, all_objects
        WHERE  ROWNUM <= 15;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE ('all_objects direct' || CHR (9) || (DBMS_UTILITY.GET_TIME - v_time));
    
      v_time := DBMS_UTILITY.GET_TIME;
      FOR i IN 1..v_iter LOOP
        SELECT /*+ CARDINALITY (t, 15) */ dummy
        BULK COLLECT INTO v_vc2s
        FROM   dual, (
               SELECT 1
               FROM   all_objects
               WHERE  ROWNUM <= 15) t;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE ('all_objects view' || CHR (9) || (DBMS_UTILITY.GET_TIME - v_time));
    Also I discovered that there were some discrepancies in the 8i results here due to the optimizer choosing NESTED LOOPS over MERGE JOIN CARTESIAN (my CARDINALITY hint is not valid and hence was ignored in 8i) so I changed them to ORDERED hints which seem to use MERGE JOIN CARTESIAN and the corrected stats (until I find more mistakes) are....

    Code:
                              hsecs (1000 iterations)
    -------------------------------------------------
    approach                   8i       9.2      10g
    -------------------------------------------------
    union all dual            121       230       47
    table of integers          42        84       51
    collection type            36        70       48
    table function             77       121      104
    pipelined function        n/a       136       73
    group by cube             141      4033     3423
    all_objects direct        433       628      868
    all_objects view          436       584      854

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Good!

    Based on that I'd vote for the "table function" as a compromise over efficiency, version-independance, generality and ease of coding.

    (I suppose the remaining question is, how well do the various methods work for 10'000 rows . . . ?)

Posting Permissions

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


Click Here to Expand Forum to Full Width