问题描述
嗨
我们有日期和时间戳分区表。加入他们时，分区修剪似乎不起作用。
有什么办法可以做到这一点吗？
我们有日期和时间戳分区表。加入他们时，分区修剪似乎不起作用。
有什么办法可以做到这一点吗？
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') 复制 文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1389次阅读
2025-03-13 11:40:53
Oracle RAC ASM 磁盘组满了，无法扩容怎么在线处理？
Lucifer三思而后行
845次阅读
2025-03-17 11:33:53
RAC 19C 删除+新增节点
gh
526次阅读
2025-03-14 15:44:18
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
485次阅读
2025-03-13 14:38:19
Oracle 如何修改 db_unique_name？强迫症福音！
Lucifer三思而后行
385次阅读
2025-03-12 21:27:56
Oracle DataGuard高可用性解决方案详解
孙莹
337次阅读
2025-03-26 23:27:33
墨天轮个人数说知识点合集
JiekeXu
282次阅读
2025-04-01 15:56:03
一键装库脚本3分钟极速部署，传统耗时砍掉95%！
IT邦德
275次阅读
2025-03-10 07:58:44
切换Oracle归档路径后，不能正常删除原归档路径上的归档文件
dbaking
259次阅读
2025-03-19 14:41:51
风口浪尖！诚通证券扩容采购Oracle 793万...
Roger的数据库专栏
258次阅读
2025-03-24 09:42:53
