Application of temporary tables in Oracle temporary tables

Some people on the Internet have provided the best optimization ideas: 1. first, extract the records that meet the conditions in the large table to generate a temporary table. 2. then, compare the small temporary table with another small table.

Some people on the Internet have provided the best optimization ideas: 1. first, extract the records that meet the conditions in the large table to generate a temporary table. 2. then, compare the small temporary table with another small table.

Some people on the Internet have provided the best optimization ideas:

1. Extract the records that meet the conditions in the large table to generate a temporary table.

2. Associate the small temporary table with another small table.

No matter whether the thinking is worth discussing, it is very worthy of recognition to regard the temporary table as a transfer station.

Temporary tables are essentially cache representations. Temporary tables in Oracle are created in advance.

Once a temporary table is used, data related to the session is stored.

No one will use a temporary table to save the data that should have been shared.

The with subquery uses a temporary table. Oracle will create a temporary table for you.

Therefore, the overhead of the temporary table also has the WITH subquery. You only need to open AUTOTRACE and you will see the REDO overhead.

The use of temporary tables poses at least two problems:

1) Execution Plan of the master Query

2) Additional redo writing problems

If,

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) The problem is quite serious.

(I) Execution Plan of the primary query

9i temporary table because of the dynamic sampling level 1, hint must be used, 10g is better

Temporary tables may be used in complex stored procedures (such as data extraction). The advantage of temporary tables is that they have fewer redo operations and are automatically cleared.

For the defect of the temporary table-sampling problem, the issue of execution plan is mainly about the cardinality problem of the temporary table.

For the temporary table scheme, dynamic sampling is recommended. In Versions later than 9IR2, The DYNAMIC_SAMPLING parameter or hint can be basically avoided.

For example, write the HINT to force it to sample/* + dynamic_sampling (t 0 )*/

Cardinality hint segmentation prompt is a good best 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 of the temporary table changes, the original execution plan may not be optimal.

Dynamic SQL is recommended for this problem.

The data volume of the temporary table can be learned through SQL % ROWCOUNT after the insertion ends.

Then the cardinality prompt is added to the dynamic SQL. 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 is treated as 10000, and-is treated,

Similarly, CARDINALITY = CEIL (SQL % ROWCOUNT/5000) * 5000,

You can also adjust a reasonable value through testing.