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.
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.
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 table T_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. created DATE, 10. last_ddl_time DATE, 11. timestamp VARCHAR2 (19), 12. status VARCHAR2 (7 ),
13. temporary VARCHAR2 (1), 14. generated VARCHAR2 (1 ),
15. secondary VARCHAR2 (1)
16 .);
17 ./
18.
19. create table T_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 replace package pkg_test is
2. procedure load_target_normal; 3.end pkg_test; 4.
5. create or replace package body pkg_test is 6. procedure load_target_normal is 7. begin 8. insert into t_target (owner, object_name, comm) 9. select owner, object_name, 'xxx' from t_ss_normal; 10. commit; 11. end; 12. begin 13. null; 14.end pkg_test;
An insert into select statement is easy to process.
An insert into select statement is easy to process.
1. create type obj_target as object (
2. owner VARCHAR2 (30), object_name VARCHAR2 (128), comm varchar2 (10)
3 .);
4 ./
5. create or replace type typ_array_target as table of obj_target; 6 ./
7.
8. create or replace package pkg_test is 9.
10. function pipe_target (p_source_data in sys_refcursor) return typ_array_target 11. pipelined;
12.
13. procedure load_target; 14.end pkg_test; create two custom types first. The definition of obj_target is consistent with the table structure of t_target. It is used to store each target table record. Typ_array_target is used to return values of the MPs queue function.
Next, define a pipeline function.
A pipelined keyword is added at the end of a common function, which is a pipeline function. The return parameter type of this function is set so that it can be used as a table function. A table function is called in the from clause using table (v_resultset). v_resultset is a set type parameter.
Define a stored procedure call.
Define the MPs queue function and call the stored procedure in the package body. The input parameter of the Pipeline Function pipe_target is of the sys_refcursor type. This is a cursor, which can be understood as a result set that can be obtained only by using select * from table.
You can also define a cursor in the function instead of passing in the cursor.
1. function pipe_target (p_source_data in sys_refcursor) return typ_array_target
2. pipelined is 3. r_target_data obj_target: = obj_target (null, null, null); 4. r_source_data t_ss % rowtype;
5.
6. begin 7.
8. loop
9. fetch p_source_data 10. into r_source_data; 11. exit when p_source_data % notfound; 12.
13. r_target_data.owner: = r_source_data.owner;
14. r_target_data.object_name: = r_source_data.object_name;
15. r_target_data.comm: = 'xxx'; 16. pipe row (r_target_data );
17.
18. end loop; 19.
20. close p_source_data; 21. return; 22.
23. end; 24.
25. procedure load_target is 26. begin 27. insert into t_target 28. (owner, object_name, comm)
29. select owner, object_name, comm 30. from table (pipe_target (cursor (select * from t_ss_normal); 31. commit; 32. end; 33.
