gpt4 book ai didi

解释计划分区上的 oracle 过滤器

转载 作者:行者123 更新时间:2023-12-05 00:52:32 27 4
gpt4 key购买 nike

我正在做一个概念证明,我正在试验一种奇怪的行为。
我有一个按日期字段按范围分区的表,如果我设置固定日期或由 SYSDATE 创建的日期,查询的成本会发生很大变化。

这些是解释计划:

SQL> SELECT *
2 FROM TP_TEST_ELEMENTO_TRAZABLE ET
3 WHERE ET.FEC_RECEPCION
4 BETWEEN TRUNC(SYSDATE-2) AND TRUNC(SYSDATE-1)
5 ;

5109 filas seleccionadas.


Plan de Ejecuci¾n
----------------------------------------------------------
Plan hash value: 1151442660

------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5008 | 85136 | 4504 (8)| 00:00:55 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION RANGE ITERATOR| | 5008 | 85136 | 4504 (8)| 00:00:55 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | TP_TEST_ELEMENTO_TRAZABLE | 5008 | 85136 | 4504 (8)| 00:00:55 | KEY | KEY |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TRUNC(SYSDATE@!-2)<=TRUNC(SYSDATE@!-1))
3 - filter("ET"."FEC_RECEPCION">=TRUNC(SYSDATE@!-2) AND "ET"."FEC_RECEPCION"<=TRUNC(SYSDATE@!-1))


EstadÝsticas
----------------------------------------------------------
1 recursive calls
0 db block gets
376 consistent gets
0 physical reads
0 redo size
137221 bytes sent via SQL*Net to client
4104 bytes received via SQL*Net from client
342 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5109 rows processed

使用固定日期:
SQL> SELECT *
2 FROM TP_TEST_ELEMENTO_TRAZABLE ET
3 WHERE ET.FEC_RECEPCION
4 BETWEEN TO_DATE('26/02/2017', 'DD/MM/YYYY') AND TO_DATE('27/02/2017', 'DD/MM/YYYY')
5 ;

5109 filas seleccionadas.


Plan de Ejecuci¾n
----------------------------------------------------------
Plan hash value: 3903280660

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5008 | 85136 | 11 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 5008 | 85136 | 11 (0)| 00:00:01 | 607 | 608 |
|* 2 | TABLE ACCESS FULL | TP_TEST_ELEMENTO_TRAZABLE | 5008 | 85136 | 11 (0)| 00:00:01 | 607 | 608 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ET"."FEC_RECEPCION"<=TO_DATE(' 2017-02-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


EstadÝsticas
----------------------------------------------------------
1 recursive calls
0 db block gets
376 consistent gets
0 physical reads
0 redo size
137221 bytes sent via SQL*Net to client
4104 bytes received via SQL*Net from client
342 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5109 rows processed

产生 4504 的成本和 11 的成本有什么区别?

提前致谢 :)

最佳答案

不同之处在于,当您使用 SYSDATE 时,它可能需要任何分区。例如,如果您是每天分区,那么您今天和明天需要访问的分区会有所不同。因此,计划是 KEY:KEY,这意味着实际分区在运行时解析。

有了固定的日期,我们在编译时就知道它解析到哪个分区。而且由于它解析为单个分区,因此成本更“准确”。

关于解释计划分区上的 oracle 过滤器,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42515420/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com