Oracle 日期列中的范围分区DD/MM/YYYY HH24:MM:SS

嗨，团队，

我需要在end_date的基础上使用RAGE间隔分区对TEST_PARTITIONS表进行分区。

create table TEST_PARTITIONS
 partition by range(end_date)
 ( partition p2010 values less than (to_date('01-Jan-2011','dd-mon-yyyy')) ,
   partition p2011 values less than (to_date('01-Jan-2012','dd-mon-yyyy')) ,
   partition p2012 values less than (to_date('01-Jan-2013','dd-mon-yyyy')) ,
   partition p2013 values less than (to_date('01-Jan-2014','dd-mon-yyyy')) ,
   partition p2014 values less than (to_date('01-Jan-2015','dd-mon-yyyy')) ,
   partition p2015 values less than (to_date('01-Jan-2016','dd-mon-yyyy')) ,
   partition p2016 values less than (to_date('01-Jan-2017','dd-mon-yyyy')) ,
   partition p2017 values less than (to_date('01-Jan-2018','dd-mon-yyyy')) )
 as
 select a.* , created-1 as begin_date,
              created+1 as end_date
 from all_objects a;

当我尝试获取特定日期的订单时，即13/04/2016导致找不到记录

select *
 from TEST_PARTITIONS
where end_date = to_date('13/04/2016','DD/MM/YYYY');

当我尝试char end_date列查询按预期给出输出，但它去搜索所有分区-

select *
 from TEST_PARTITIONS
where end_date = to_date('13/04/2016','DD/MM/YYYY');


Plan hash value: 3588510895
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |    21 |  3696 |   470   (2)| 00:00:06 |       |       |
|   1 |  PARTITION RANGE ALL|                 |    21 |  3696 |   470   (2)| 00:00:06 |     1 |     8 |
|*  2 |   TABLE ACCESS FULL | TEST_PARTITIONS |    21 |  3696 |   470   (2)| 00:00:06 |     1 |     8 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_CHAR(INTERNAL_FUNCTION("END_DATE"),'DD/MM/YYYY')='13/04/2016')
 
Note
-----
   - dynamic sampling used for this statement (level=2)

你能建议任何替代方法来获取明智的订单检索吗？

Oracle数据库中的日期始终具有时间组件。当你这样做的时候:

where end_date = to_date('13/04/2016','DD/MM/YYYY')

你正在比较end_date到日期时间4月13日，2016在午夜。你不太可能在这个精确的时间创建任何对象。

要查找在给定日期创建的行，您需要查找当天随时发生的行:

set serveroutput off
alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';
select min(created)
 from all_objects a;

MIN(CREATED)          
24-AUG-2013 18:26:49  

create table TEST_PARTITIONS
 partition by range(end_date)
 ( partition p2015 values less than (to_date('01-Jan-2016','dd-mon-yyyy')) ,
   partition p2016 values less than (to_date('01-Jan-2017','dd-mon-yyyy')) ,
   partition p2017 values less than (to_date('01-Jan-2018','dd-mon-yyyy')) ,
   partition p2019 values less than (to_date('01-Jan-2019','dd-mon-yyyy')) )
 as
 select a.* , created as end_date
 from all_objects a;

select count(*)
 from TEST_PARTITIONS
where end_date = to_date('24/08/2013','DD/MM/YYYY');

COUNT(*)  
0      

select count(*)
 from TEST_PARTITIONS
where end_date = to_date('24/08/2013 18:26:49  ','DD/MM/YYYY  HH24:MI:SS');

COUNT(*)  
131 

select count(*)
 from TEST_PARTITIONS
where end_date >= to_date('24/08/2013','DD/MM/YYYY')
and   end_date <  to_date('24/08/2013','DD/MM/YYYY') + 1;

COUNT(*)  
74,263  

我不确定 “尝试char end_date” 是什么意思。但是，如果您查看计划底部的谓词部分，您将看到:

 2 - filter(TO_CHAR(INTERNAL_FUNCTION("END_DATE"),'DD/MM/YYYY')='13/04/2016')

如果是这样，当您将Oracle数据库与日期进行比较时，将其隐式转换为日期。这意味着它将一个函数应用于end_date。当您的查询在分区列上具有功能时，它无法进行分区修剪。这就是为什么你看到它访问所有分区。

这也是为什么你应该使用上面的dt <= end_date < dt 1方法来查找给定日期的行。不应用trunc或类似end_date:

select count(*)
 from TEST_PARTITIONS
where trunc(end_date) = to_date('24/08/2013','DD/MM/YYYY');

COUNT(*)  
74,263   

select * from table(dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE'));

PLAN_TABLE_OUTPUT                                                         
EXPLAINED SQL STATEMENT:                                                  
------------------------                                                  
select count(*)  from TEST_PARTITIONS where trunc(end_date) =             
to_date('24/08/2013','DD/MM/YYYY')                                        
                                                                          
Plan hash value: 4117722176                                               
                                                                          
------------------------------------------------                          
| Id  | Operation            | Name            |                          
------------------------------------------------                          
|   0 | SELECT STATEMENT     |                 |                          
|   1 |  SORT AGGREGATE      |                 |                          
|   2 |   PARTITION RANGE ALL|                 |                          
|*  3 |    TABLE ACCESS FULL | TEST_PARTITIONS |                          
------------------------------------------------                          
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   3 - filter(TRUNC(INTERNAL_FUNCTION("END_DATE"))=TO_DATE(' 2013-08-24   
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

select count(*)
 from TEST_PARTITIONS
where end_date >= to_date('24/08/2013','DD/MM/YYYY')
and   end_date <  to_date('24/08/2013','DD/MM/YYYY') + 1;

COUNT(*)  
74,263   

select * from table(dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE'));

PLAN_TABLE_OUTPUT                                                           
EXPLAINED SQL STATEMENT:                                                    
------------------------                                                    
select count(*)  from TEST_PARTITIONS where end_date >=                     
to_date('24/08/2013','DD/MM/YYYY') and   end_date <                         
to_date('24/08/2013','DD/MM/YYYY') + 1                                      
                                                                            
Plan hash value: 1044590884                                                 
                                                                            
---------------------------------------------------                         
| Id  | Operation               | Name            |                         
---------------------------------------------------                         
|   0 | SELECT STATEMENT        |                 |                         
|   1 |  SORT AGGREGATE         |                 |                         
|   2 |   PARTITION RANGE SINGLE|                 |                         
|*  3 |    TABLE ACCESS FULL    | TEST_PARTITIONS |                         
---------------------------------------------------                         
                                                                            
Predicate Information (identified by operation id):                         
---------------------------------------------------                         
                                                                            
   3 - filter(("END_DATE">=TO_DATE(' 2013-08-24 00:00:00', 'syyyy-mm-dd     
              hh24:mi:ss') AND "END_DATE"