The nice and important thing to do when going to a conference is - beside consuming all the great presentations - talk to other attendees and exchange experiences, current issues and probable solutions.
I am very happy I attended
POUG2022! This conference is one of the best in my opinion!
During one of the discussions I claimed Oracle can - under specific circumstances - do a
partition pruning, even when the partition key isn't somewhere defined as a filter.
It's probably easiest to show it by a simple example.
First some data to play with
CREATE TABLE RAW_DATA
( "ID" NUMBER NOT NULL ENABLE,
"SNAP_ID" NUMBER NOT NULL ENABLE,
"EVENT_TIME" DATE NOT NULL ENABLE,
"PAD1" VARCHAR2(4000 BYTE)
);
insert into RAW_DATA
select rownum
, 1+trunc(rownum/99)
, sysdate - (rownum/99)
, rpad( to_char(rownum), 500, '*')
from dual
connect by level <= 10000;
commit;
CREATE TABLE SNAP
( "SNAP_ID" NUMBER NOT NULL ENABLE,
"BEGIN_INTERVAL_TIME" DATE NOT NULL ENABLE,
"END_INTERVAL_TIME" DATE NOT NULL ENABLE,
CONSTRAINT "SNAP__PK" PRIMARY KEY ("SNAP_ID") ENABLE,
CONSTRAINT "SNAP__BEGIN_U" UNIQUE ("BEGIN_INTERVAL_TIME") ENABLE,
CONSTRAINT "SNAP__END_U" UNIQUE ("END_INTERVAL_TIME") ENABLE,
CONSTRAINT "SNAP__TIME_ASC" CHECK (begin_interval_time < end_interval_time) ENABLE --> -- for HTML parser
)
;
INSERT INTO SNAP
SELECT SNAP_ID, min(event_time), max(event_time)
from RAW_DATA
group by SNAP_ID;
commit; -- 102 rows inserted
CREATE TABLE PART
( "ID" NUMBER NOT NULL ENABLE,
"SNAP_ID" NUMBER NOT NULL ENABLE,
"EVENT_TIME" DATE NOT NULL ENABLE,
"PAD1" VARCHAR2(4000 BYTE) NOT NULL ENABLE,
CONSTRAINT "PART_PK" PRIMARY KEY ("ID") ENABLE
)
PARTITION BY LIST ("SNAP_ID") AUTOMATIC
(PARTITION "P1" VALUES (1) SEGMENT CREATION DEFERRED )
;
select Partition_Name from user_tab_partitions where table_name='PART';
PARTITION_NAME
--------------
P1
SYS_P529
SYS_P530
...
SYS_P627
SYS_P628
SYS_P629
102 rows selected.
The partitions are not that big, the data quite boring, but it's sufficient data to play around.
Now I want to query a row from PART for a specific time-range. as the field EVENT_TIME is not the partition key, the simple query goes like this:
-- SQL1
SELECT /*+ gather_plan_statistics */ p.*
from PART p
WHERE EVENT_TIME between sysdate-(1+1/98) and sysdate-1
;
Plan hash value: 2974113857
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 7007 |
|* 1 | FILTER | | 1 | | | | 1 |00:00:00.01 | 7007 |
| 2 | PARTITION LIST ALL| | 1 | 1 | 1 | 102 | 1 |00:00:00.01 | 7007 |
|* 3 | TABLE ACCESS FULL| PART | 102 | 1 | 1 | 102 | 1 |00:00:00.01 | 7007 |
------------------------------------------------------------------------------------------------------
In this simple query all partitions are scanned (Pstart => 1 to Pstop => 102).
Let's see if we can use SNAP to make the query more efficient:
The simplest query makes it even more expensive:
-- SQL2
select p.*
from snap sn, part p
where sn.snap_id = p.snap_id
and p.event_time between sysdate-(1+1/98) and sysdate-1 ;
Plan hash value: 2574539144
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 27817 (100)| | | |
|* 1 | FILTER | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 2050 | 27817 (1)| 00:00:02 | | |
| 3 | INDEX FAST FULL SCAN | SNAP__PK | 102 | 1326 | 2 (0)| 00:00:01 | | |
| 4 | PARTITION LIST ITERATOR| | 1 | 2037 | 273 (1)| 00:00:01 | KEY | KEY |
|* 5 | TABLE ACCESS FULL | PART | 1 | 2037 | 273 (1)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------------------------------------------
But we are missing something:
alter table PART
add constraint PART__SNAP_FK foreign key("SNAP_ID") references "SNAP"("SNAP_ID")
;
-- rerun SQL2
Plan hash value: 2974113857
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 27818 (100)| | | 1 |00:00:00.01 | 7007 |
|* 1 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 7007 |
| 2 | PARTITION LIST ALL| | 1 | 1 | 27818 (1)| 1 | 102 | 1 |00:00:00.01 | 7007 |
|* 3 | TABLE ACCESS FULL| PART | 102 | 1 | 27818 (1)| 1 | 102 | 1 |00:00:00.01 | 7007 |
-------------------------------------------------------------------------------------------------------------------
Now we are back at the original plan - but no progress is made.
Next try: provide more information about the correlation between SNAP_IDs and dates in PART and SNAP:
select /*+ gather_plan_statistics */ p.*
from snap sn, part p
where sn.snap_id = p.snap_id
and p.event_time between sysdate-(1+1/98) and sysdate-1
and p.event_time between sn.begin_interval_time and sn.end_interval_time
;
Plan hash value: 3865632887
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 27819 (100)| | | 1 |00:00:00.01 | 74 |
|* 1 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 74 |
| 2 | NESTED LOOPS | | 1 | 1 | 27819 (1)| | | 1 |00:00:00.01 | 74 |
| 3 | NESTED LOOPS | | 1 | 1 | 27819 (1)| | | 1 |00:00:00.01 | 73 |
| 4 | PARTITION LIST SUBQUERY | | 1 | 1 | 27818 (1)|KEY(SQ)|KEY(SQ)| 1 |00:00:00.01 | 72 |
|* 5 | TABLE ACCESS FULL | PART | 1 | 1 | 27818 (1)|KEY(SQ)|KEY(SQ)| 1 |00:00:00.01 | 70 |
|* 6 | INDEX UNIQUE SCAN | SNAP__PK | 1 | 1 | 0 (0)| | | 1 |00:00:00.01 | 1 |
|* 7 | TABLE ACCESS BY INDEX ROWID| SNAP | 1 | 1 | 1 (0)| | | 1 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYSDATE@!-1>=SYSDATE@!-1.01020408163265306122448979591836734694)
5 - filter(("P"."EVENT_TIME">=SYSDATE@!-1.01020408163265306122448979591836734694 AND
"P"."EVENT_TIME"<=SYSDATE@!-1))
6 - access("SN"."SNAP_ID"="P"."SNAP_ID")
7 - filter(("P"."EVENT_TIME">="SN"."BEGIN_INTERVAL_TIME" AND "P"."EVENT_TIME"<="SN"."END_INTERVAL_TIME"
AND "SN"."END_INTERVAL_TIME">=SYSDATE@!-1.01020408163265306122448979591836734694 AND
"SN"."BEGIN_INTERVAL_TIME"<=SYSDATE@!-1))
-- >
D'OH!
Now the costs increased - by the additional effort shows less Buffers! And also Pstart and Pstop show thePartitions begin and end at KEY(SQ) (no 1 .. 102 anymore).
That's what I tried to achieve: indirect partition pruning based on a key stored in another table.
Unfortunately the nasty
and p.event_time between sn.begin_interval_time and sn.end_interval_time
is required as it can't be defined as an ASSERTION - even it is defined in SQL-92 already which is 30 years old.
If you would like to see ASSERTIONs in Oracle, please upvote this
Idea!
