Shulou(Shulou.com)05/31 Report--

Editor to share with you the Oracle 12CR2 query conversion tutorial cursor-duration temporary table example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

The function of Cursor-Duration temporary Table

Complex queries sometimes process the same query block multiple times, which will add unnecessary performance to unlocking. To avoid this problem, the Oracle database can create temporary tables for query results during the cursor life cycle and store them in memory. For complex operations with with clause queries, star transformations, and grouping set operations, this optimization enhances the use of materialized intermediate results to optimize subqueries. In this way, the cursor-duration temporary table improves performance and optimizes Icano.

Working principle of Cursor-Duration temporary meter

The cursor-definition temporary table definition is built into memory. The table definition is related to the cursor and is visible only to the session that executes the cursor. When using cursor-duration temporary tables, the database does the following:

1. Select an execution plan that uses the cursor-duration temporary table

two。 Use a unique name when creating temporary tables

3. Rewrite query reference temporary table

4. Load data into memory until no memory is available, in which case a temporary segment will be created on disk

5. Execute a query to return data from a temporary table

6.truncate table, freeing memory and temporary segments on any disk

Note that the metadata of the cursor-duration temporary table remains in memory as long as the cursor is in memory. Metadata is not stored in the data dictionary, which means that it cannot be queried through the data dictionary view and cannot be explicitly deleted. The above scenario depends on available memory. For specific queries, temporary tables use PGA memory.

The implementation of cursor-duration temporary tables is similar to sorting. If no memory is available, the database will write the data to the temporary segment. For cursor-duration temporary tables, the main differences are as follows:

. The database frees memory and temporary periods at the end of the query rather than when the row source is not active.

. Data in memory is still stored in memory, unlike sorted data that may move between memory and temporary periods.

When the database uses cursor-duration temporary tables, the keyword cursor duration memory appears in the execution plan.

Cursor-duration temporary table usage scenario

A with query that repeats the same subquery multiple times may sometimes perform better using cursor-duration temporary tables. The following query uses a with clause to create three subquery blocks:

SQL > set long 99999SQL > set linesize 300SQL > with 2 Q1 as (select department_id, sum (salary) sum_sal from hr.employees group by 3 department_id), 4 Q2 as (select * from Q1), 5 Q3 as (select department_id, sum_sal from Q1) 6 select * from Q1 7 union all 8 select * from Q29 union all 10 select * from Q3 DEPARTMENT_ID SUM_SAL--100 51608 30 24900 7000 90 58000 20 19000 70 10000 110 20308 50 156400 80 304500 40 6500 60 28800 10 4400 100 51608 30 24900 7000 58000 20 19000 70 10000 110 20308 50 156400 80 304500 40 6500 60 28800 100 51608 30 24900 7000 58000 20 19000 70 10000 110 20308 50 156400 80 304500 40 6500 60 28800 10 440036 rows selected.

The following is the execution plan after the optimized conversion

SQL > select * from table (dbms_xplan.display_cursor (format= > 'basic + rows + cost')) PLAN_TABLE_OUTPUT-EXPLAINED SQL STATEMENT:- -with Q1 as (select department_id Sum (salary) sum_sal from hr.employeesgroup by department_id), Q2 as (select * from Q1), Q3 as (selectdepartment_id) Sum_sal from Q1) select * from Q1 union all select * from Q2 union all select * from q3Plan hash value: 4087957524- -| Id | Operation | Name | Rows | Cost (% CPU) | PLAN_TABLE_OUTPUT- -| 0 | SELECT STATEMENT | | 6 | TEMP TABLE TRANSFORMATION | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9E08D2_620789C | 3 | HASH GROUP BY | | 11 | 276 (2) | 4 | TABLE ACCESS FULL | EMPLOYEES | 100K | 273 (1) | | 5 | UNION-ALL | 6 | | | VIEW | | 11 | 2 (0) | | 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9E08D2_620789C | 11 | 2 (0) | | 8 | VIEW | 11 | 2 (0) | | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9E08D2_620789C | 11 | 2 (0) | | 10 | VIEW | 11 | 2 (0) | | 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9E08D2_620789C | 11 | | 2 (0) |-26 rows selected.

In the above execution plan, TEMP TABLE TRANSFORMATION in step 1 instructs the database to use the cursor-duration temporary table to execute the query. The CURSOR DURATION MEMORY in step 2 instructs the database to use memory and, if memory is available, store the results as a temporary table SYS_TEMP_0FD9E08D2_620789C. If no memory is available, the database writes temporary data to disk.

The above is all the content of the article "sample Analysis of the cursor-duration temporary Table of the Oracle 12CR2 query Transformation tutorial". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.