How much performance can the Oracle PipeLined method improve?

How much performance can be improved by using the Oracle PipeLined method? Recently, Oracle table functions are used. In particular, the nested table data is returned by the table structure. We found that the PIPELINED method is used, it can indeed greatly improve the performance. Oracle PipeLined, and send it to the next processing stage. Recently, I found a time for a specific test. Conclusion: When a table-1 function is returned as a nested table, it is time-consuming and memory-consuming. The PIPELINED mode ensures that the data is not waiting and the efficiency is very high, especially the table function returned by the application in Oracle. It is not restricted by the SQL statement type and can improve the performance, especially in the case of large data volumes: 1) select * from table (table function () 2) or select count (*) from table (table function () (this process is not reflected, however, in actual application use, the performance is indeed higher than that in non-PIPELINED mode.) 3) select * from table (table function () where conditions (this process is not reflected, however, the performance of the application IS indeed higher than that of the non-PIPELINED method. For details, see Step 1. CREATE two auxiliary types [SQL] CREATE OR REPLACE TYPE performance_e_v IS OBJECT (pid INTEGER, persionid VARCHAR2 (40), datefield DATE, name VARCHAR2 (40), account VARCHAR2 (20), balance NUMBER, securitycode VARCHAR2 (60 )); /create or replace type performance_TABLE as table of performance_e_v;/2 create an Oracle Package, which contains two functions and one is executed in PIPELINED mode, A nested table Returns [SQL] create or replace package TEST_PIPELLINED is -- Author: ADMINISTRATOR -- Created: 15:57:21 -- Purpose: -- Public type declarations ret_table performance_TABLE: = performance_TABLE (); -- Public constant declarations -- Public variable declarations -- Public function and procedure declarations -- Test with pipelined FUNCTION implements RETURN performance_TABLE PIPELINED; -- test with no pipelined FUNCTION implements RETURN performance_TABLE; end TEST_PIPELLINED; /create or replace package body TEST_PIPELLINED is -- both the first and second executions return function compute RETURN performance_TABLE pipelined as -- TYPE rowidcur is ref cursor; -- retTab performance_e_TABLE: = performance_e_TABLE (); -- out_rec performance_e_v: = performance_e_v (0, '', 0,''); BEGIN -- if data in the nested table exists, send data directly from the nested table to pipelined if ret_table.COUNT> 0 THEN -- traverse the records of the nested table to pipelined FOR j IN 1 .. ret_table.count loop pipe row (ret_table (j); end loop; RETURN; end if; -- insert 0.5 million identical records into the nested table FOR j IN 1 .. 500000 LOOP ret_table.EXTEND (1); ret_table (ret_table.LAST): = performance_e_v (1, '', sysdate, 't02jxd9ivggs5g01j5ndebqkhbqo8l9v ', '20170', 0, 'iggohbuqoo rjex policyruyxatwgdvlkgju extinguish 1_1_e limit F pG 1_vl '); end loop; -- traverses records of nested tables to pipelined FOR j IN 1 .. ret_table.count loop pipe row (ret_table (j); end loop; END WITH_Pipelined; -- the FUNCTION WITH_NOPipelined RETURN performance_TABLE AS -- TYPE rowidcur is ref cursor is returned for both executions; -- retTab performance_e_TABLE: = performance_e_TABLE (); -- out_rec performance_e_v: = performance_e_v (0, '', 0 ,''); BEGIN -- IF data in the nested table exists, the nested table IF ret_table.COUNT> 0 then return ret_table; end if; -- insert 0.5 million identical records into the nested table (or traverse from a table cursor) FOR j IN 1 .. 500000 LOOP ret_table.EXTEND (1); ret_table (ret_table.LAST): = performance_e_v (1, '', sysdate, 't02jxd9ivggs5g01j5ndebqkhbqo8l9v ', '20170', 0, 'iggohbuqoo rjex policyruyxatwgdvlkgju extinguish 1_1_e limit F pG 1_vl '); end loop; return ret_table; END WITH_NOPipelined; end TEST_PIPELLINED; /3 let's look at the test result 3.1. it is executed in non-PIPELINED mode (returned in nested table mode) [SQL] C: \ Documents and Settings \ Administrator> sqlplus tpcctest/****** @ 102.com SQL * Plus: Release 10.2.0.4.0-Production on Thursday, December 3, 2013 Copyright (c) 1982,200 7, Oracle. all Rights Reserved. connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production With the Partitioning, OLAP and Data Mining options SQL> set timing on SQL> select count (*) from table (TEST_PIPELLINED.WITH_NOPIPELINED (); COUNT (*) ---------- 500000 <span style = "color: # ff0000;"> used time: 00: 00: 29.73 </span> SQL> select count (*) from table (TEST_PIPELLINED.WITH_NOPIPELINED (); COUNT (*) ---------- 500000 <span style = "color: # ff0000; "> used time: 00: 00: 29.75 </span> SQL> quit from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production With the Partitioning, OLAP and Data Mining options disconnected 3.2 use PIPELINED to execute [SQL] C: \ Documents ents and Settings \ Administrator> sqlplus tpcctest/****** @ 102.com SQL * Plus: release 10.2.0.4.0-Production on Thursday April 11 16:40:03 2013 Copyright (c) 1982,200 7, Oracle. all Rights Reserved. connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production With the Partitioning, OLAP and Data Mining options SQL> set timing on SQL> select count (*) from table (TEST_PIPELLINED.WITH_PIPELINED (); COUNT (*) ---------- 500000 <span style = "color: # ff0000;"> used time: 00: 00: 01.37 </span> SQL> select count (*) from table (TEST_PIPELLINED.WITH_PIPELINED (); COUNT (*) ---------- 500000 <span style = "color: # ff0000; "> used time: 00: 00: 00.25 </span> SQL> select count (*) from table (TEST_PIPELLINED.WITH_PIPELINED (); COUNT (*) ---------- 500000 <span style = "color: # ff0000;"> used time: 00: 00: 00.23 </span> SQL> quit is disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production With the Partitioning, OLAP and Data Mining options