- Eine partitionierte Tabelle gliedert ein größeres Segment in kleinere Teil-Segmente.
- Lokale Indizes beschränken sich immer auf eine Partition.
- Deshalb sollte eine Query der Form: SELECT COUNT(*) FROM partitioned_table WHERE partition_key = ... dazu in der Lage sein, zunächst durch partition elimination das passende Tabellen-Segment und seine Indizes zu ermitteln und dann einen beliebigen bitmap index zu verwenden, um daraus die erforderlichen Daten zu ziehen, ohne dabei auf die Tabelle zugreifen zu müssen.
Das schien mir plausibel, ist es aber anscheinend nicht - jedenfalls funktioniert es so nicht:
Demnach funktioniert die partition elimination, aber der Zugriff erfolgt dann über FTS, obwohl der Index deutlich kleiner ist als die Tabelle (laut DBA_SEGMENTS: 3976 BLOCKS im Index gegenüber 17928 BLOCKS in der Tabelle).
drop table test_partition_bidx;
create table test_partition_bidx
( id number
, startdate date
, col1 number
, padding varchar2(100)
)
partition by range (startdate)
(partition p1 values less than ( to_date('30.06.2012','dd.mm.yyyy')),
partition p2 values less than ( to_date('01.07.2012','dd.mm.yyyy')),
partition p3 values less than ( to_date('02.07.2012','dd.mm.yyyy')),
partition p4 values less than ( to_date('03.07.2012','dd.mm.yyyy')),
partition p5 values less than ( to_date('04.07.2012','dd.mm.yyyy'))
);
-- Daten einfügen
insert into test_partition_bidx
select rownum
, to_date('30.06.2012','dd.mm.yyyy') + trunc((rownum -1)/200000)
, trunc(rownum - 1/10000)
, lpad('*', 100, '*')
from dual
connect by level <= 1000000;
-- Statistiken erzeugen
exec dbms_stats.gather_table_stats(user, 'test_partition_bidx')
-- bitmap index anlegen
create bitmap index ix_test_partition_bidx on test_partition_bidx(col1) local;
-- Query-Ausführung mit autotrace
set autot on
SQL> select count(*) from test_partition_bidx where startdate = '01.07.2012';
COUNT(*)
----------
250000
Abgelaufen: 00:00:00.35
Ausführungsplan
----------------------------------------------------------
Plan hash value: 788106769
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1199 (1)| 00:00:15 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE| | 250K| 1953K| 1199 (1)| 00:00:15 | 3 | 3 |
|* 3 | TABLE ACCESS FULL | TEST_PARTITION_BIDX | 250K| 1953K| 1199 (1)| 00:00:15 | 3 | 3 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistiken
----------------------------------------------------------
1 recursive calls
0 db block gets
4360 consistent gets
4354 physical reads
0 redo size
425 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Demnach funktioniert die partition elimination, aber der Zugriff erfolgt dann über FTS, obwohl der Index deutlich kleiner ist als die Tabelle (laut DBA_SEGMENTS: 3976 BLOCKS im Index gegenüber 17928 BLOCKS in der Tabelle).
Wenn man dem CBO den Index-Zugriff per Hint nahelegt, ergibt sich Folgendes:
Der Hint erzwingt den Index-Zugriff - aber nicht in gewünschter Form: statt eines INDEX FAST FULL SCANs erfolgt ein INDEX FULL SCAN und anschließend wird eine teuere BITMAP CONVERSION TO ROWIDS durchgeführt, gefolgt von einem TABLE ACCESS BY LOCAL INDEX ROWID, der eigentlich nicht erforderlich sein sollte. Die Zahl der LIOs ist dabei dann höher als für den FTS-Fall und die Laufzeit steigt dramatisch. Der Versuch, den Zugriff über einen INDEX_FFS-Hint auf einen INDEX FAST FULL SCAN zu lenken, bringt leider keine Besserung: in diesem Fall wählt der CBO wieder den FTS.
Um das Zählen der Sätze in den Partitionen zu beschleunigen, hilft ein bitmap Index auf dem partition key:
Hier ergibt sich dann das, was ich mir gewünscht habe: ein INDEX FAST FULL SCAN mit anschließendem BITMAP CONVERSION COUNT und ohne BITMAP CONVERSION TO ROWIDS und Tabellen-Zugriff. Die LIOs sinken auf übersichtliche 12 und die Laufzeit ist geringfügig.
Aber warum funktioniert das nur mit einem bitmap Index, der den partition key enthält (geeignet wäre auch ein mehrspaltiger bitmap index, der den partition key enthält)? Die Antwort lautet: ich weiß es nicht. Anscheinend verknüpft der CBO in diesem Fall nicht alle Informationen, die ihm zur Verfügung stehen und führt deshalb im Step 3 TABLE ACCESS BY LOCAL INDEX ROWID die Filterung filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) durch, die eigentlich redundant ist, weil sie bereits durch die partition elimination sichergestellt ist. Bis mir jemand eine bessere Erklärung liefert, gehe ich davon aus, dass es sich hier wieder um einen Fall handelt, in dem der CBO weniger clever ist, als er sein könnte.
Nachtrag 03.07.2012: Die Erklärung des Verhaltens liefert Randolf Geist in seinem Kommentar zum Artikel: ich weiß zwar, dass jede Partition nur einen Tag enthält, aber der CBO kann das nicht wissen und muss die bitmap Struktur deshalb auspacken. Mit der von Randolf vorgeschlagenen LIST-Partitionierung bekomme ich ohne Hints immer noch einen FTS, aber mit Index-Hint ergibt sich ein INDEX FULL SCAN mit folgendem BITMAP CONVERSION COUNT.
select /*+ index (t) */ count(*) from test_partition_bidx t where startdate = '01.07.2012';
Abgelaufen: 00:00:03.35
Ausführungsplan
----------------------------------------------------------
Plan hash value: 3608709152
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 7873 (1)| 00:01:35 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE | | 250K| 1953K| 7873 (1)| 00:01:35 | 3 | 3 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_PARTITION_BIDX | 250K| 1953K| 7873 (1)| 00:01:35 | 3 | 3 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 5 | BITMAP INDEX FULL SCAN | IX_TEST_PARTITION_BIDX | | | | | 3 | 3 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistiken
----------------------------------------------------------
1 recursive calls
0 db block gets
5185 consistent gets
5142 physical reads
0 redo size
425 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Der Hint erzwingt den Index-Zugriff - aber nicht in gewünschter Form: statt eines INDEX FAST FULL SCANs erfolgt ein INDEX FULL SCAN und anschließend wird eine teuere BITMAP CONVERSION TO ROWIDS durchgeführt, gefolgt von einem TABLE ACCESS BY LOCAL INDEX ROWID, der eigentlich nicht erforderlich sein sollte. Die Zahl der LIOs ist dabei dann höher als für den FTS-Fall und die Laufzeit steigt dramatisch. Der Versuch, den Zugriff über einen INDEX_FFS-Hint auf einen INDEX FAST FULL SCAN zu lenken, bringt leider keine Besserung: in diesem Fall wählt der CBO wieder den FTS.
Um das Zählen der Sätze in den Partitionen zu beschleunigen, hilft ein bitmap Index auf dem partition key:
create bitmap index ix2_test_partition_bidx on test_partition_bidx(startdate) local;
select count(*) from test_partition_bidx where startdate = '01.07.2012';
Abgelaufen: 00:00:00.03
Ausführungsplan
----------------------------------------------------------
Plan hash value: 296465563
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 6 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE | | 250K| 1953K| 6 (0)| 00:00:01 | 3 | 3 |
| 3 | BITMAP CONVERSION COUNT | | 250K| 1953K| 6 (0)| 00:00:01 | | |
|* 4 | BITMAP INDEX FAST FULL SCAN| IX2_TEST_PARTITION_BIDX | | | | | 3 | 3 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistiken
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
7 physical reads
0 redo size
425 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Hier ergibt sich dann das, was ich mir gewünscht habe: ein INDEX FAST FULL SCAN mit anschließendem BITMAP CONVERSION COUNT und ohne BITMAP CONVERSION TO ROWIDS und Tabellen-Zugriff. Die LIOs sinken auf übersichtliche 12 und die Laufzeit ist geringfügig.
Aber warum funktioniert das nur mit einem bitmap Index, der den partition key enthält (geeignet wäre auch ein mehrspaltiger bitmap index, der den partition key enthält)? Die Antwort lautet: ich weiß es nicht. Anscheinend verknüpft der CBO in diesem Fall nicht alle Informationen, die ihm zur Verfügung stehen und führt deshalb im Step 3 TABLE ACCESS BY LOCAL INDEX ROWID die Filterung filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) durch, die eigentlich redundant ist, weil sie bereits durch die partition elimination sichergestellt ist. Bis mir jemand eine bessere Erklärung liefert, gehe ich davon aus, dass es sich hier wieder um einen Fall handelt, in dem der CBO weniger clever ist, als er sein könnte.
Nachtrag 03.07.2012: Die Erklärung des Verhaltens liefert Randolf Geist in seinem Kommentar zum Artikel: ich weiß zwar, dass jede Partition nur einen Tag enthält, aber der CBO kann das nicht wissen und muss die bitmap Struktur deshalb auspacken. Mit der von Randolf vorgeschlagenen LIST-Partitionierung bekomme ich ohne Hints immer noch einen FTS, aber mit Index-Hint ergibt sich ein INDEX FULL SCAN mit folgendem BITMAP CONVERSION COUNT.
