Application of temporary tables in Oracle temporary tables

Some people on the Internet have provided the best optimization ideas for the application of temporary tables in Oracle temporary tables: 1. first, extract the records that meet the conditions in the large table to generate a temporary table. 2. then, associate the small temporary table with another small table. regardless of whether the thinking is debatable or not, it is worth noting that the temporary table is essentially a cache format, oracle temporary tables are created in advance. Once a temporary table is used, the stored data is the data related to this session. No one will use a temporary table to save the data that should be shared. In fact, a temporary table is used for the with subquery, oracle will create a temporary table for you, so the overhead of the temporary table WITH subquery will also exist. As long as AUTOTRACE is enabled, you will see that the overhead of REDO has at least two problems with the use of temporary tables: 1) the execution plan of the primary query; 2) the additional redo writing problem, temporary tables are used for primary queries as intermediate results of complex query conditions, because temporary tables are often only a small amount of data for individual fields. 1) The problem is quite serious. If, the temporary table is returned as the result before the final display. The temporary table may have more data with multiple fields. 2) (I) Execution Plan of the primary query 9i temporary table must use hint because of the dynamic sampling level 1, and 10 Gb can be used for complex stored procedures (such as data extraction) temporary tables may be used. The advantage of temporary tables is that there is less redo than entity tables. The defects in temporary tables are automatically cleared-sampling problems, the main problem with the execution plan is the cardinality of the temporary table. For the temporary table scheme, dynamic sampling is recommended. In Versions later than 9IR2, using the DYNAMIC_SAMPLING parameter or hint can basically avoid forcing the HINT to sample/* + dynamic_sampling (t 0) */cardinality hint segment. This is a good practice. For example: when the data volume in the temporary table is large, Oracle only performs one sampling during hard parsing. When the data volume in the temporary table changes, the original execution plan may not be optimal. If this problem occurs, we recommend that you use the data volume of the temporary dynamic SQL table. After the insertion, you can use SQL % ROWCOUNT to learn the data and then splice cardinality in the dynamic SQL statement, this prompt does not need to be accurate. Otherwise, you will have countless hard resolutions. We recommend that you set the threshold to 5000, that is, 1-5000 as 10000,-as, and so on, CARDINALITY = CEIL (SQL % ROWCOUNT/5000) * 5000. You can also adjust it by testing Value (2) the temporary table generated by the redo of the temporary table does not generate a redo for their blocks. Therefore, the temporary table operation is not "recoverable ". When you modify a block in a temporary table, the modification will not be recorded in the redo log file. However, the temporary table will indeed generate undo, And the undo is included in the log. Therefore, temporary tables also generate some redo statements. Why do we need to generate an undo? This is because you can roll back to a temporary SAVEPOINT table in the transaction with constraints. In normal tables, all temporary tables may have an INSERT statement to INSERT 500 rows into the temporary table, but failed to insert the row to 500th, which requires that this statement be rolled back because the temporary table generally performs like a "normal" table, therefore, the temporary table must generate undo because the undo data must be created with logs, the temporary table will generate some redo logs for the generated undo to prevent undo from occurring, the SQL statements run on the temporary table are mainly INSERT and SELECT INSERT, which only generate a very small number of undo statements. In addition, SELECT does not generate undo statements at all. Therefore, the redo of the temporary table is generated because undo is required, in fact, most of the cases where temporary tables are used for query, so there is often no need for undo (iii) Use Cases of temporary tables (①) before the initial optimization of cyclic SQL, there are many similar SQL statements in the system for cyclic execution, low efficiency. For example, thousands of parameters are usually input through the interface. When SQL optimization is executed cyclically based on these parameters, you can insert these parameters into the temporary table first, then join the temporary table for execution at a time to achieve optimization results ② multi-Table association using temporary tables simplify complex SQL statements with too many table associations ③ if a data set is used repeatedly, it is recommended that use temporary tables ④ temporary tables are useful when the logic is complex and large data volume is updated, in the query section, with can be used as the intermediate result of complex query conditions for the primary query