Load 1 million data into a flat file, Here using three methods. Comparing with them to know how the pipelined function and parallel to effect the performance.
Trigger DDL , some skill
after suspend in trigger.
Trigger for DDL. Do not forget the attribute you could use when it is triggered.
Create a insead of trigger for DML view, you had batter do not use DML to view frequently.
sys_context could afford many information about environment variables, which could be used in trigger or sql statement.
Export data as text mode. Here I introduce two methods to export data as text mode with an specified format for sql loader or external table. Obviously, UTL_FILE is faster.
Table Function, use the cursor expression as the input parameter, nested table as the output, then use table function transfer the output to a table, It is a stream, do not cost huge PGA. Eventually, the parallel for DML work best. If you want to know about what is the Table Function and Pipelined, You should see my this blog.
It is easy to understand that, this option in package header is means whether collection is kept in buffer when a anonymous block end or not.
dbms_utility.get_cpu_time is a function return the time of cpu. Use it to create a function to calculate time you specified procedure or function real run.
You need consider consistency of the table which be used in you function, if you function run for a long time , may the data of table would changed. How to assure the consistency.
Use the constant in package header or Cache a table in PGA to improve performance , however, considering memory cost, you need a blance consideration.
One session one time for initialize package, It is useful, if you store a small table into memory for complex logic calculation. Speed up.
Declare cursor in package header, you could declare directly, or just declare header with hiding SELECT statement. But you need append the RETURN statement which is the rowtype in SELECT statement that you would use
Many people heard overloading in PL/SQL, here is the detail about that. Just tell you procedure and function could be overloaded at the same PLSQL object. There are still many tips could be talked, to be continue......
PL/SQL is execution flow which from top to bottom. If you declare a function or procedure before you want to invoke it, you need declare it before. So it is the drawback of this design.
AUTHID CURRENT_USER AUTHID DEFINER
Use package to manage exception
Create login.sql in the /home/oracle/ directory.
exception for plsql
batch processing record with update.
two level loops.
explicitly cursor and implicitly cursor.
a table type in pl/sql.
Copyright © 2005-2024 51CTO.COM 版权所有 京ICP证060544号
