My oracle database version ：PL/SQL Release 12.2.0.1.0 - Production
table A ddl：
CREATE TABLE A
( "BILLID" NVARCHAR2(50) NOT NULL ENABLE,
"BILLTYPE" NVARCHAR2(10),
"CDATE" DATE,
CONSTRAINT "PK_A_ID" PRIMARY KEY ("BILLID"))
PARTITION BY RANGE (CDATE)
--INTERVAL(NUMTOYMINTERVAL(12,'MONTH'))
Subpartition By list(billtype)
SUBPARTITION template
(
SUBPARTITION BILLMTYPE_XSCK_C VALUES ('XSCK'),
SUBPARTITION BILLMTYPE_XCWGRK_C VALUES ('XCWGRK'),
SUBPARTITION BILLMTYPE_XCSHRK_C VALUES ('XCSHRK'),
SUBPARTITION BILLMTYPE_WGRK_C VALUES ('WGRK'),
SUBPARTITION BILLMTYPE_DBD_C VALUES ('DBD'),
SUBPARTITION BILLMTYPE_XCWZCKC VALUES ('XCWZCK'),
SUBPARTITION BILLMTYPE_CPRK_C VALUES ('CPRK'),
SUBPARTITION BILLMTYPE_WZCK_C VALUES ('WZCK'),
SUBPARTITION BILLMTYPE_XCRK_C VALUES ('XCRK'),
SUBPARTITION BILLMTYPE_QTCK_C VALUES ('QTCK'),
SUBPARTITION BILLMTYPE_XCCK_C VALUES ('XCCK')
)
(
PARTITION SM_A_2018 VALUES LESS THAN (To_date('2018-12-31 16:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION SM_A_2019 VALUES LESS THAN (To_date('2019-12-31 16:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION SM_A_2020 VALUES LESS THAN (To_date('2020-12-31 16:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION SM_A_2021 VALUES LESS THAN (To_date('2021-12-31 16:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION SM_A_2022 VALUES LESS THAN (To_date('2022-12-31 16:00:00','yyyy-mm-dd hh24:mi:ss'))
)
;
table B ddl:
CREATE TABLE B
(
"BILLDETAILID" NVARCHAR2(50) NOT NULL ENABLE,
"BILLID" NVARCHAR2(50) not null,
"ITEMID" NVARCHAR2(50),
CONSTRAINT "PK_A_B_DETAIL" PRIMARY KEY ("BILLDETAILID"),
CONSTRAINT "FK_B_A_BILLID" FOREIGN KEY(BILLID) REFERENCES A ("BILLID"))
PARTITION BY REFERENCE(FK_B_A_BILLID);
table C ddl:
CREATE TABLE C(
"ID" nvarchar2(50) not null enable,
"CODE" NVARCHAR2(80),
CONSTRAINT "PK_C_ID" PRIMARY KEY ("ID")
)
explain plan:
1.
explain plan for
select * from a inner join b on a.billid=b.billid
inner join c on B.Itemid =C.Id
where A.Billtype='WZCK' and A.Cdate >= to_date('2019-12-31 16:00:00','yyyy-mm-dd hh24:mi:ss') AND A.Cdate < TO_DATE('2020-12-31 16:00:00','yyyy-mm-dd hh24:mi:ss')
;
Plan hash value: 3515450193
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49519 | 17M| | 26754 (1)| 00:00:02 | | |
|* 1 | HASH JOIN | | 49519 | 17M| 13M| 26754 (1)| 00:00:02 | | |
| 2 | PARTITION RANGE SINGLE| | 49519 | 13M| | 19132 (1)| 00:00:01 | 3 | 3 |
| 3 | PARTITION LIST SINGLE| | 49519 | 13M| | 19132 (1)| 00:00:01 | KEY | KEY |
|* 4 | HASH JOIN | | 49519 | 13M| | 19132 (1)| 00:00:01 | | |
| 5 | TABLE ACCESS FULL | A | 7552 | 604K| | 240 (0)| 00:00:01 | 30 | 30 |
| 6 | TABLE ACCESS FULL | B | 1662K| 307M| | 18888 (1)| 00:00:01 | 30 | 30 |
| 7 | TABLE ACCESS FULL | C | 748K| 70M| | 3018 (1)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ITEMID"="C"."ID")
4 - access("A"."BILLID"="B"."BILLID")
Note
-----
- this is an adaptive plan
磁區修剪生效
2.explain paln
explain plan for
select * from a inner join b on a.billid=b.billid
inner join c on B.Itemid =C.Id
where A.Billtype='WZCK' and A.Cdate >= to_date('2019-12-31 16:00:00','yyyy-mm-dd hh24:mi:ss') AND A.Cdate < TO_DATE('2020-12-31 16:00:00','yyyy-mm-dd hh24:mi:ss')
AND C.CODE LIKE '20.%'
;
Plan hash value: 437133680
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 375 | 21920 (1)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 1 | 375 | 21920 (1)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 4 | 375 | 21920 (1)| 00:00:01 | | |
|* 3 | HASH JOIN | | 4 | 1172 | 21912 (1)| 00:00:01 | | |
|* 4 | TABLE ACCESS FULL | C | 1 | 99 | 3020 (1)| 00:00:01 | | |
| 5 | PARTITION REFERENCE ALL | | 1662K| 307M| 18888 (1)| 00:00:01 | 1 | 55 |
| 6 | TABLE ACCESS FULL | B | 1662K| 307M| 18888 (1)| 00:00:01 | 1 | 55 |
|* 7 | INDEX UNIQUE SCAN | PK_A_ID | 1 | | 1 (0)| 00:00:01 | | |
|* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| A | 1 | 82 | 2 (0)| 00:00:01 | 30 | 30 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."ITEMID"="C"."ID")
4 - filter("C"."CODE" LIKE U'20.%')
7 - access("A"."BILLID"="B"."BILLID")
8 - filter("A"."BILLTYPE"=U'WZCK' AND "A"."CDATE">=TO_DATE(' 2019-12-31 16:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "A"."CDATE"<TO_DATE(' 2020-12-31 16:00:00', 'syyyy-mm-dd hh24:mi:ss'))
磁區修剪不生效
A has 300,000 data rows
b has 2,000,000 data rows
c has 700,000 data rows.
3.explain plan:
手動指定磁區
explain plan for
select * from a inner join b Partition(SM_A_2020_BILLMTYPE_WZCK_C) on a.billid=b.billid
inner join c on B.Itemid =C.Id
where A.Billtype='WZCK' and A.Cdate >= to_date('2019-12-31 16:00:00','yyyy-mm-dd hh24:mi:ss') AND A.Cdate < TO_DATE('2020-12-31 16:00:00','yyyy-mm-dd hh24:mi:ss')
AND C.CODE LIKE '20.%'
;
Plan hash value: 223762195
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 3760 | 572 (1)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 10 | 3760 | 572 (1)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 10 | 3760 | 572 (1)| 00:00:01 | | |
|* 3 | HASH JOIN | | 10 | 2940 | 552 (1)| 00:00:01 | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| C | 1 | 99 | 4 (0)| 00:00:01 | | |
|* 5 | INDEX RANGE SCAN | IDX_C_ITEM | 1 | | 3 (0)| 00:00:01 | | |
| 6 | PARTITION REFERENCE SINGLE | | 44145 | 8406K| 548 (1)| 00:00:01 | 30 | 30 |
| 7 | TABLE ACCESS FULL | B | 44145 | 8406K| 548 (1)| 00:00:01 | 30 | 30 |
|* 8 | INDEX UNIQUE SCAN | PK_A_ID | 1 | | 1 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY GLOBAL INDEX ROWID | A | 1 | 82 | 2 (0)| 00:00:01 | 30 | 30 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."ITEMID"="C"."ID")
5 - access("C"."CODE" LIKE U'20.%')
filter("C"."CODE" LIKE U'20.%')
8 - access("A"."BILLID"="B"."BILLID")
9 - filter("A"."BILLTYPE"=U'WZCK' AND "A"."CDATE">=TO_DATE(' 2019-12-31 16:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "A"."CDATE"<TO_DATE(' 2020-12-31 16:00:00', 'syyyy-mm-dd hh24:mi:ss'))
為什么增加了個普通表欄位的查詢條件,磁區修剪就失效了呢？？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/229513.html
標籤：基礎和管理
下一篇：小白求解 基礎問題