Oracle 加入日期和时间戳分区表，但分区修剪似乎不起作用

嗨

我们有日期和时间戳分区表。加入他们时，分区修剪似乎不起作用。

有什么办法可以做到这一点吗？

create table T_A(
ORDERED  DATE       not null,
C1       number(20) not null
)
partition by range(ORDERED)
interval (NUMTOYMINTERVAL(1,'MONTH'))
(
    PARTITION DEFAULT_PARTITION VALUES LESS THAN (TO_DATE('20180101','yyyymmdd'))
);

create table T_B(
INSERTED TIMESTAMP  not null,
C1       number(20) not null
)
partition by range(INSERTED)
interval (NUMTOYMINTERVAL(1,'MONTH'))
(
    PARTITION DEFAULT_PARTITION VALUES LESS THAN (TO_DATE('20180101','yyyymmdd'))
);

insert into t_a(ordered , c1) select to_date('20180101','YYYYMMDD') + level , level from dual connect by level < 500;
insert into t_B(inserted, c1) select to_date('20180101','YYYYMMDD') + level + 3/2, level from dual connect by level < 500;
commit;
execute dbms_stats.gather_table_stats(user,tabname => 'T_A');
execute dbms_stats.gather_table_stats(user,tabname => 'T_B');
select 
    t_a.ORDERED,
    t_b.INSERTED,
    t_a.c1    
from
    t_a
    inner join t_b on T_A.C1 = t_b.c1 and T_A.ORDERED <= t_b.INSERTED
where 
    T_A.ORDERED >= to_date('20180701','yyyymmdd')
;
select * from table(dbms_xplan.display_cursor);
--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |  1176 (100)|          |       |       |
|*  1 |  HASH JOIN                |      |    16 |   432 |  1176   (1)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |   320 |  3840 |   462   (1)| 00:00:01 |     8 |1048575|
|*  3 |    TABLE ACCESS FULL      | T_A  |   320 |  3840 |   462   (1)| 00:00:01 |     8 |1048575|
|   4 |   PARTITION RANGE ALL     |      |   499 |  7485 |   713   (1)| 00:00:01 |     1 |1048575|
|   5 |    TABLE ACCESS FULL      | T_B  |   499 |  7485 |   713   (1)| 00:00:01 |     1 |1048575|
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T_A"."C1"="T_B"."C1")
       filter("T_B"."INSERTED">=INTERNAL_FUNCTION("T_A"."ORDERED"))
   3 - filter("T_A"."ORDERED">=TO_DATE(' 2018-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 

select 
    t_a.ORDERED,
    t_b.INSERTED,
    t_a.c1    
from
    t_a
    inner join t_b on T_A.C1 = t_b.c1 and T_A.ORDERED <= t_b.INSERTED
where 
    T_A.ORDERED >= to_date('20180701','yyyymmdd')
and T_A.ORDERED = cast (T_A.ORDERED as timestamp)
and cast(T_A.ORDERED as timestamp) <= T_B.INSERTED
;


| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |  1176 (100)|          |       |       |
|*  1 |  HASH JOIN                |      |     1 |    27 |  1176   (1)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |     3 |    36 |   462   (1)| 00:00:01 |     8 |1048575|
|*  3 |    TABLE ACCESS FULL      | T_A  |     3 |    36 |   462   (1)| 00:00:01 |     8 |1048575|
|   4 |   PARTITION RANGE ALL     |      |   499 |  7485 |   713   (1)| 00:00:01 |     1 |1048575|
|   5 |    TABLE ACCESS FULL      | T_B  |   499 |  7485 |   713   (1)| 00:00:01 |     1 |1048575|
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T_A"."C1"="T_B"."C1")
       filter(("T_B"."INSERTED">=INTERNAL_FUNCTION("T_A"."ORDERED") AND 
              "T_B"."INSERTED">=CAST(INTERNAL_FUNCTION("T_A"."ORDERED") AS timestamp)))
   3 - filter(("T_A"."ORDERED">=TO_DATE(' 2018-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 
              AND INTERNAL_FUNCTION("T_A"."ORDERED")=CAST(INTERNAL_FUNCTION("T_A"."ORDERED") AS 
              timestamp)))

您可以将时间戳强制转换为日期。这使优化器能够将过滤器应用于T_B。但不是分区修剪:

select 
    t_a.ORDERED,
    t_b.INSERTED,
    t_a.c1    
from t_a
join t_b 
on T_A.C1 = t_b.c1 
and T_A.ORDERED <= cast ( t_b.INSERTED as date )
where T_A.ORDERED >= to_date('20180701','yyyymmdd');

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS +PARTITION LAST')); 

Plan hash value: 4264623528                                                             
                                                                                        
-------------------------------------------------------------------------------------   
| Id  | Operation                 | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |   
-------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT          |      |      1 |        |       |       |   1276 |   
|*  1 |  HASH JOIN                |      |      1 |     64 |       |       |   1276 |   
|   2 |   PARTITION RANGE ITERATOR|      |      1 |    639 |     8 |1048575|    638 |   
|*  3 |    TABLE ACCESS FULL      | T_A  |     11 |    639 |     8 |1048575|    638 |   
|   4 |   PARTITION RANGE ALL     |      |      1 |    998 |     1 |1048575|    640 |   
|*  5 |    TABLE ACCESS FULL      | T_B  |     18 |    998 |     1 |1048575|    640 |   
-------------------------------------------------------------------------------------   
                                                                                        
Predicate Information (identified by operation id):                                     
---------------------------------------------------                                     
                                                                                        
   1 - access("T_A"."C1"="T_B"."C1")                                                    
       filter("T_A"."ORDERED"<=CAST(INTERNAL_FUNCTION("T_B"."INSERTED") AS              
              date ))                                                                   
   3 - filter("T_A"."ORDERED">=TIMESTAMP' 2018-07-01 00:00:00')                         
   5 - filter(CAST(INTERNAL_FUNCTION("T_B"."INSERTED") AS date )>=TIMESTAMP'            
              2018-07-01 00:00:00')      

问题是数据库需要做一个日期 <> 时间戳转换。无论您是显式执行此操作还是隐式执行此操作，您都将在至少一个分区列上具有函数。

将函数应用于分区列会限制优化器分区修剪的能力。

为了确保这样做，您需要更改数据类型以匹配:

drop table t_a cascade constraints purge;
drop table t_b cascade constraints purge;

create table T_A(
ORDERED  TIMESTAMP  not null,
C1       number(20) not null
)
partition by range(ORDERED)
interval (NUMTOYMINTERVAL(1,'MONTH'))
(
    PARTITION DEFAULT_PARTITION VALUES LESS THAN (TO_DATE('20180101','yyyymmdd'))
);

create table T_B(
INSERTED TIMESTAMP  not null,
C1       number(20) not null
)
partition by range(INSERTED)
interval (NUMTOYMINTERVAL(1,'MONTH'))
(
    PARTITION DEFAULT_PARTITION VALUES LESS THAN (TO_DATE('20180101','yyyymmdd'))
);

insert into t_a(ordered , c1) select to_date('20180101','YYYYMMDD') + level , level from dual connect by level < 500;
insert into t_B(inserted, c1) select to_date('20180101','YYYYMMDD') + level + 3/2, level from dual connect by level < 500;
commit;
execute dbms_stats.gather_table_stats(user,tabname => 'T_A');
execute dbms_stats.gather_table_stats(user,tabname => 'T_B');

select 
    t_a.ORDERED,
    t_b.INSERTED,
    t_a.c1    
from t_a
join t_b 
on T_A.C1 = t_b.c1 
and T_A.ORDERED <= t_b.INSERTED 
where T_A.ORDERED >= to_timestamp('20180701','yyyymmdd');

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS +PARTITION LAST')); 

drop table t_a cascade constraints purge;
drop table t_b cascade constraints purge;

create table T_A(
ORDERED  TIMESTAMP  not null,
C1       number(20) not null
)
partition by range(ORDERED)
interval (NUMTOYMINTERVAL(1,'MONTH'))
(
    PARTITION DEFAULT_PARTITION VALUES LESS THAN (TO_DATE('20180101','yyyymmdd'))
);

create table T_B(
INSERTED TIMESTAMP  not null,
C1       number(20) not null
)
partition by range(INSERTED)
interval (NUMTOYMINTERVAL(1,'MONTH'))
(
    PARTITION DEFAULT_PARTITION VALUES LESS THAN (TO_DATE('20180101','yyyymmdd'))
);

insert into t_a(ordered , c1) select to_date('20180101','YYYYMMDD') + level , level from dual connect by level < 500;
insert into t_B(inserted, c1) select to_date('20180101','YYYYMMDD') + level + 3/2, level from dual connect by level < 500;
commit;
execute dbms_stats.gather_table_stats(user,tabname => 'T_A');
execute dbms_stats.gather_table_stats(user,tabname => 'T_B');

select 
    t_a.ORDERED,
    t_b.INSERTED,
    t_a.c1    
from t_a
join t_b 
on T_A.C1 = t_b.c1 
and T_A.ORDERED <= t_b.INSERTED 
where T_A.ORDERED >= to_timestamp('20180701','yyyymmdd');

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS +PARTITION LAST')); 

Plan hash value: 4124582613                                                             
                                                                                        
-------------------------------------------------------------------------------------   
| Id  | Operation                 | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |   
-------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT          |      |      1 |        |       |       |    319 |   
|*  1 |  HASH JOIN                |      |      1 |    161 |       |       |    319 |   
|   2 |   PARTITION RANGE ITERATOR|      |      1 |    320 |     8 |1048575|    319 |   
|*  3 |    TABLE ACCESS FULL      | T_A  |     11 |    320 |     8 |1048575|    319 |   
|   4 |   PARTITION RANGE ITERATOR|      |      1 |    321 |     8 |1048575|    320 |   
|*  5 |    TABLE ACCESS FULL      | T_B  |     11 |    321 |     8 |1048575|    320 |   
-------------------------------------------------------------------------------------   
                                                                                        
Predicate Information (identified by operation id):                                     
---------------------------------------------------                                     
                                                                                        
   1 - access("T_A"."C1"="T_B"."C1")                                                    
       filter("T_A"."ORDERED"<="T_B"."INSERTED")                                        
   3 - filter("T_A"."ORDERED">=TIMESTAMP' 2018-07-01 00:00:00.000000000')               
   5 - filter("T_B"."INSERTED">=TIMESTAMP' 2018-07-01 00:00:00.000000000')