当前位置: 首页 > 编程笔记 >

Oracle 12CR2查询转换教程之cursor-duration临时表详解

慕弘义
2023-03-14
本文向大家介绍Oracle 12CR2查询转换教程之cursor-duration临时表详解,包括了Oracle 12CR2查询转换教程之cursor-duration临时表详解的使用技巧和注意事项,需要的朋友参考一下

前言

在Oracle12C中为了物化查询的中间结果,Oracle数据库在查询编译时在内存中可能会隐式的创建一个cursor_duration临时表。

下面话不多说了,来一起看看详细的介绍吧

Cursor-Duration临时表的作用

复杂查询有时会处理相同查询块多次,这将会增加不必要的性能开锁。为了避免这种问题,Oracle数据库可以在游标生命周期内为查询结果创建临时表并存储在内存中。对于有with子句查询,星型转换与分组集合操作的复杂操作,这种优化增强了使用物化中间结果来优化子查询。在这种方式下,cursor-duration临时表提高了性能并且优化了I/O。

Cursor-Duration临时表工作原理

cursor-definition临时表定义内置在内存中。表定义与游标相关,并且只对执行游标的会话可见。当使用cursor-duration临时表时,数据库将执行以下操作:

1.选择使用cursor-duration临时表的执行计划

2.创建临时表时使用唯一名

3.重写查询引用临时表

4.加载数据到内存中直到没有内存可用,在这种情次品下将在磁盘上创建临时段

5.执行查询,从临时表中返回数据

6.truncate表,释放内存与任何磁盘上的临时段

注意,cursor-duration临时表的元数据只要cursor在内存中就会一直存在于内存中。元数据不会存储在数据字典中这意味着通过数据字典视图将不能查询到,不能显性地删除元数据。上面的场景依赖于可用的内存。对于特定查询,临时表使用PGA内存。

cursor-duration临时表的实现类似于排序。如果没有可用内存,那么数据库将把数据写入临时段。对于cursor-duration临时表,主要差异如下:

.在查询结束时数据库释放内存与临时段而不是当row source不现活动时释放。

.内存中的数据仍然存储在内存中,不像排序数据可能在内存与临时段之间移动。

当数据库使用cursor-duration临时表时,关键字cursor duration memory会出现在执行计划中。

cursor-duration临时表使用场景

一个with查询重复相同子查询多次可能有时使用cursor-duration临时表性能更高,下面的查询使用一个with子句来创建三个子查询块:

SQL> set long 99999
SQL> set linesize 300
SQL> 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 q2
 9 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
   90  58000
   20  19000
   70  10000
   110  20308
   50  156400
   80  304500
   40  6500
   60  28800
   10  4400
   100  51608
   30  24900
     7000
   90  58000
   20  19000
   70  10000
   110  20308
   50  156400
   80  304500
   40  6500
   60  28800
   10  4400

36 rows selected.

下面是优化转换后的执行计划

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.employees
group by department_id), q2 as (select * from q1), q3 as (select
department_id, sum_sal from q1) select * from q1 union all select *
from q2 union all select * from q3

Plan hash value: 4087957524

----------------------------------------------------------------------------------------------------
| Id | Operation        | Name      | Rows | Cost (%CPU)|

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |       |  |  6 (100)|
| 1 | 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.

在上面的执行计划中,在步骤1中的TEMP TABLE TRANSFORMATION指示数据库使用cursor-duration临时表来执行查询。在步骤2中的CURSOR DURATION MEMORY指示数据库使用内存,如果有可用内存,将结果作为临时表SYS_TEMP_0FD9E08D2_620789C来进行存储。如果没有可用内存,那么数据库将临时数据写入磁盘。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对小牛知识库的支持。

 类似资料:
  • 本文向大家介绍Oracle 12CR2查询转换教程之临时表转换详解,包括了Oracle 12CR2查询转换教程之临时表转换详解的使用技巧和注意事项,需要的朋友参考一下 前言 大家都知道在12CR2中出现一种新的查询转换技术临时表转换, 在下面的例子中,数据库对customers表上的子查询结果物化到一个临时表中: 优化器使用临时表SYS_TEMP_0FD9D6893_63D6F82来代替custo

  • 操作步骤: 菜单栏: Refactor —> Replace Temp with Query...

  • 使用Composer查询语言和REST API的查询教程 在本教程中,我们将在开发者教程的基础上进行扩展,以展示查询。原生查询语言可以使用条件过滤返回的结果,并可以在交易中被调用以执行操作,例如更新或删除结果集上的资产。 查询在查询文件(.qry)中定义,文件位于业务网络定义的父目录中的。查询包含一个WHERE子句,它定义了选择资产或参与者的过滤条件。 本教程使用tutorial-network业

  • 本文向大家介绍MongoDB教程之查询操作实例,包括了MongoDB教程之查询操作实例的使用技巧和注意事项,需要的朋友参考一下 1.  基本查询:     构造查询数据。   2.  查询条件:     MongoDB提供了一组比较操作符:$lt/$lte/$gt/$gte/$ne,依次等价于</<=/>/>=/!=。     3.  null数据类型的查询: 4.  正则查询: 5.  数组数据

  • 问题内容: 我可以这样创建一个临时表: 但是新表不可读,因为它说它没有主键。 是exisitingtable的主键,因此我希望它在temp表中得到相同的处理。 但是,无论如何,我宁愿找到某种ORM方式来执行此操作。鉴于: 如何在不执行100000命令的情况下填充一些选定的内容?还是有一种方法可以通过类似于上面的普通SQL版本的查询来创建表? 问题答案: 它不完全是ORM,但为了最初创建表,我将克隆