Tested some variations of this (hey I love to test) for performance and here's what I got (YMMV).
...and here is test script running on 10g.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
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>
Reply With Quote