Eine Frage, die ich mir gelegentlich schon einmal gestellt hatte, und dachte, die Antwort hier im Blog bereits notiert zu haben, lautet: ist der INDEX FULL SCAN (MIN/MAX) als Zugriffsoption auch für partitionierte Tabellen möglich? Da diese Antwort aber auf Anhieb unauffindbar zu sein scheint und womöglich von mir nie protokolliert worden ist, schreibe ich sie (noch einmal?) auf:
drop table t;
create table t
( id number
, startdate date
)
partition by range (startdate)
interval (numtoyminterval(1,'MONTH'))
(partition p1 values less than ( to_date('01.07.2015','dd.mm.yyyy'))
);
-- Daten einfügen
insert into t
select rownum
, trunc(sysdate) + mod(rownum, 365)
from dual
connect by level <= 100000;
create index t_idx on t(startdate) local;
explain plan for
select min(startdate) from t;
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL MIN/MAX| | 1 | 9 | | | 1 |1048575|
| 2 | SORT AGGREGATE | | 1 | 9 | | | | |
| 3 | INDEX FULL SCAN (MIN/MAX)| T_IDX | 1 | 9 | 2 (0)| 00:00:01 | 1 |1048575|
-----------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
select /*+ gather_plan_statistics */ max(startdate) from t;
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 | 1 |
| 1 | PARTITION RANGE ALL MIN/MAX| | 1 | 1 | 1 |00:00:00.01 | 1 | 1 |
| 2 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 | 1 |
| 3 | INDEX FULL SCAN (MIN/MAX)| T_IDX | 1 | 1 | 1 |00:00:00.01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Ich will nicht behaupten, dass da eine große Überraschung im Spiel ist: jeder einzelne lokale Index ist ein Segment und für jedes dieser Segmente ist ein INDEX FULL SCAN (MIN/MAX) möglich. Während das einfache Explain Plan die für interval partitions übliche - eher weniger hilfreiche - Bereichsangabe 1-1048575 für Pstart und Pstop liefert, zeigt der Plan mit rowsource statistics recht deutlich, dass tatsächlich nur auf ein Segment zugegriffen wird, denn Buffers = 1 wäre für einen Zugriff auf mehrere lokale Indizes schwer zu erklären. Auch das ist zunächst keine Überraschung, denn startdate ist schließlich der partition key, so dass hier partition pruning möglich sein sollte und anschließend dann der INDEX FULL SCAN (MIN/MAX) auf der (zeitlich) letzten Partition. Überraschender ist da schon die PARTITION RANGE ALL MIN/MAX Angabe, denn eigentlich ist hier kein Scan aller Partitionen erforderlich und findet laut rowsource statistics auch nicht statt. Um die Annahme, dass hier tatsächlich partition pruning im Spiel (und der Execution Plan nicht so ganz plausibel) ist, zu überprüfen, habe ich - den Erläuterungen von
Christoph Bohl folgend - noch ein Trace mit Event 10128 erzeugt und darin Folgendes gefunden:
Partition Iterator Information:
partition level = PARTITION
call time = RUN
order = DESCENDING
Partition iterator for level 1:
iterator = RANGE [0, 4]
index = 4
current partition: part# = 4, subp# = 1048576, abs# = 4
An dieser Stelle bin ich mir der Semantik nicht so ganz sicher: einerseits scheint die Iterator-Angabe einen Zugriff auf alle fünf Partitionen anzugeben, andererseits verweist current partition nur auf die (zeitlich) letzte Partition. Da mir die zweite Aussage besser in den Kram passt, halte ich sie zunächst mal für die korrekte Interpretation (zumal sie auch den Aussagen in der manuell erzeugten Hilfstabelle kkpap_pruning entspricht) - versuche aber noch, weitere Meinungen einzuholen.
P.S.: die geringe Anzahl der Partitionen ergibt sich übrigens daraus, dass meine Datenbank ein wenig in der Vergangenheit lebt und ihr sysdate im Oktober 2014 sieht.