Pipelined function of Oracle for high-performance Big Data Processing

In plsql development, some big data table data processing is involved. For example, the records of a table with over million records are processed and converted into another table or several tables.

The conventional operation method can be implemented, but the time, disk IO, redo log and so on are very large. Oracle provides an advanced function that can improve the performance of such data processing to the limit. This type of function is called a pipeline function.

In actual projects, pipeline functions are used together with table functions, data stream functions (that is, table functions and CURSOR), data sets, and concurrency to reach the peak of big data processing performance.

In the following example, the t_ss_normal record of the table is inserted into the t_target table, and some conversion operations are performed during the insertion process.

I divided it into four methods to implement this data processing operation.

The first method is also the most common method. The Code is as follows:

1. Create TableT_SS_NORMAL
2. (
3. Owner VARCHAR2 (30 ),
4. Object_name VARCHAR2 (128 ),
5. Subobject_name VARCHAR2 (30 ),
6. Object_id NUMBER,
7. Data_object_id NUMBER,
8. Object_type VARCHAR2 (19 ),
9. CreatedDATE,
10. Last_ddl_timeDATE,
11. TimestampVARCHAR2 (19 ),
12. Status VARCHAR2 (7 ),
13. TemporaryVARCHAR2 (1 ),
14. Generated VARCHAR2 (1 ),
15. Secondary VARCHAR2 (1)
16. );
17. /
19. Create TableT_TARGET
20. (
21. Owner VARCHAR2 (30 ),
22. Object_name VARCHAR2 (128 ),
23. Comm VARCHAR2 (10)
24. );

This is the table structure of the source and target tables. Currently, there are million source tables with data from the dba_objects view.

1. Create Or ReplacePackage pkg_testIs
2. ProcedureLoad_target_normal;
3. EndPkg_test;
5. Create Or ReplacePackage body pkg_testIs
6. ProcedureLoad_target_normalIs
7. Begin
8. Insert IntoT_target (owner, object_name, comm)
9. SelectOwner, object_name,'Xxx' FromT_ss_normal;
10. Commit;
11. End;
12. Begin
13. Null;
14. EndPkg_test;

An insert into select statement is easy to process.

1 2 3 Next Page