How much performance can OraclePipeLined improve?

Recently, Oracle table functions have been used, especially for returning data from nested tables according to the table structure. It is found that the PIPELINED method can indeed improve the performance.

Recently, Oracle table functions have been used, especially for returning data from nested tables according to the table structure. It is found that the PIPELINED method can indeed improve the performance.

Recently, Oracle table functions have been used, especially for returning data from nested tables according to the table structure. It is found that the PIPELINED method can indeed improve the performance.

Oracle PipeLined:

After reading the relevant articles, we generally process the data in a pipeline without waiting, instead of sending the data to the next processing stage after the traditional process is complete.

Recently, I found a time for a specific test.

Conclusion:

1. When a table function is returned as a nested table, it is time-consuming and memory-consuming;

2. The PIPELINED method ensures data availability and high efficiency, especially when the table function is returned in Oracle;

It is not limited by the SQL statement type and can improve the performance, especially when the data volume is large:

1) select * from table (table function ())

2) select count (*) from table (table function () (this process is not reflected, but in actual application, the performance is indeed higher than that of the PIPELINED method)

3) select * from table (table function () where conditions (this process is not reflected, but the performance of the application is indeed higher than that of the PIPELINED method)

Next let's take a look at the specific steps

1. Create two Secondary types

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;
/