-
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>
-
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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 . . . ?)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|