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"?

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!)

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

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.

I agree slim (I'd thought of that afterwards but was too lazy . . . )

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>

---

Nice!

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

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

---

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 . . . ?)

You might also try recreating the "fifteen" table as an IOT and analyzing it, which would probably be the practice if it were implemented ... my local results (10g)

Code:

---

union all dual    11
table of integers  13 (heap table)
collection type    9
table function    25
pipelined function 17
group by cube      1743

---

... and ...

Code:

---

union all dual    11
table of integers  8 (analyzed IOT)
collection type    8
table function    20
pipelined function 12
group by cube      1702

---

I also tried a hint-less version of all the queries, and got ...

Code:

---

union all dual    8
table of integers  7
collection type    7
table function    20
pipelined function 14
group by cube      1717

---

The Union-all seems to benefit there.

So much for hints then :-(

Thanks for suggestions Dave, good stuff.