在Oracle函数中使用分区键

Question

我们使用这个syntaxe在Oracle数据库中有一个分区表：

...
PARTITION BY RANGE(saledate)
(PARTITION sal99q1 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY')),
PARTITION sal99q2 VALUES LESS THAN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),
...

我们通常在select语句中使用分区键，如下所示：

Select * from table where saledate >= trunc(sysdate-3) and saledate < trunc(sysdate-2)

为了使用更少的代码来获得相同的结果，我通常使用以下查询：

Select * from table where trunc(saledate) = trunc(sysdate-3)

我的问题是，通过在函数中使用分区键(在本例中为trunc() )，我们是否失去了分区性能？

Answer 1

你误解了蟾蜍的计划(在你的回答中)。对于分别显示的两个查询：

Partition #: 2 Partitions determined by key values

和

Partition #: 1 Partitions accessed #1 - #17

第一个查询只访问它需要的分区，基于键值，即日期；因此，它只需要对可能包含日期的分区进行全面扫描。

第二个查询必须访问所有分区，因为您正在使用函数操作键值，这意味着您不再真正使用分区键。关键是saledate，而不是trunc(saledate)。这类似于在索引列上使用函数时发生的情况；在这种情况下不再使用索引，在这里不再使用分区键。正如你从你的问题中所怀疑的那样，是的，你确实失去了效率。

还可以看到，基数被猜测为50是因为函数调用，而不是由stats提供的4966值。

您可以在使用dbms_xplan的虚拟表中看到相同的内容；从第一个查询中可以看到：

--------------------------------------------------------------------------------------------------  
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
--------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT          |      |  4996 | 39968 |    14   (0)| 00:00:01 |       |       |  
|*  1 |  FILTER                   |      |       |       |            |          |       |       |  
|   2 |   PARTITION RANGE ITERATOR|      |  4996 | 39968 |    14   (0)| 00:00:01 |   KEY |   KEY |  
|*  3 |    TABLE ACCESS FULL      | T42  |  4996 | 39968 |    14   (0)| 00:00:01 |   KEY |   KEY |  
--------------------------------------------------------------------------------------------------  

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

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

第二个问题是：

--------------------------------------------------------------------------------------------        
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |        
--------------------------------------------------------------------------------------------        
|   0 | SELECT STATEMENT    |      |    50 |   400 |    14   (0)| 00:00:01 |       |       |        
|   1 |  PARTITION RANGE ALL|      |    50 |   400 |    14   (0)| 00:00:01 |     1 |    17 |        
|*  2 |   TABLE ACCESS FULL | T42  |    50 |   400 |    14   (0)| 00:00:01 |     1 |    17 |        
--------------------------------------------------------------------------------------------        

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

   2 - filter(TRUNC(INTERNAL_FUNCTION("SALEDATE"))=TRUNC(SYSDATE@!-3))                              

注意每个查询中的pstart/pstop值和基数。

您的第一个查询将更有效，因为它可以使用分区键来选择它进行完全扫描的分区，而第二个查询不能并且必须扫描所有分区。