Composite Partitionierung mit mehreren Ebenen

Dieser Tage beschäftige ich mich mit der Frage, wie man eine große Menge von Transaktionsdaten (ca. 5.000.000.000 Sätze) nach möglichst vielen Kriterien partitionieren kann. Nun habe ich in der Vergangenheit häufiger mit composite partitioning gearbeitet, um solche Daten zunächst zeitlich zu partitionieren und darunter dann nach Regionen oder Häusern zu subpartitionieren. Diesmal würde ich aber gerne noch eine dritte Ebene verwenden, nämlich die der Kunden - wobei in diesem Fall nur eine ganz grobe Zuordnung nach Transaktionen mit bzw. Transaktionen ohne Kundennummer erforderlich (und sinnvoll) wäre. Für meinen Test (in Version 11.2.0.1) verwende ich eine ganz einfache Tabelle mit drei Spalten (StoreId, CustomerId, SalesDate), die alle ihre Rolle bei der Partitionierung spielen sollen. Meine erste Wahl wäre eine Range-List-Partitionierung, da die Anzahl der gegebenen Stores nicht sehr groß ist und in einem Subpartition Template untergebracht werden kann:

Versuch 1: Range-List-Partitionierung mit zwei Spalten im Subpartition-Key

create table test_part_range_list
( storeid number
, salesdate date
, customerid number
, customerflag number generated always as (case when customerid = 0 then 0 else 1 end ))
   partition by range (salesdate)
   subpartition by list (storeid, customerflag)
   subpartition template
      (subpartition sp1_cust values (1, 1)
      , subpartition sp2_cust values (2, 1)
      , subpartition sp1_nocust values (1, 0)
      , subpartition sp2_nocust values (2, 0)
      , subpartition sp_others values (default )
      )
  (partition p1 values less than ( to_date('01.01.2011','dd.mm.yyyy')),
   partition p2 values less than ( to_date('01.02.2011','dd.mm.yyyy'))
  )
;

   subpartition template
   *
FEHLER in Zeile 8:
ORA-14304: List-Partitionierungsmethode erwartet eine einzelne Partitionierungsspalte

Und schon zeigt sich, dass es bisweilen ratsam ist, die Dokumentation etwas genauer anzuschauen: List-Partitionierung funktioniert offenbar nur mit einer einzelnen Schlüsselspalte, was diesen Versuch beendet...

Versuch 2: Range-List-Partitionierung mit einer virtuellen Spalte im Subpartition-Key

Im ersten Versuch hatte ich bereits eine virtuelle Spalte verwendet, um die Fallunterscheidung zwischen Transaktion mit Kundennummer und Transaktion ohne Kundennummer unterzubringen - ohne dafür eine "echte" Spalte definieren zu müssen, die die Breite der Sätze erhöhen würde. Virtual Column-Based Partitioning ist ein neues Feature in Version 11, scheint aber problemlos zu funktionieren.

create table test_part_range_list
( storeid number
, salesdate date
, customerid number
, subpartcode number generated always as (case when customerid = 0 then storeid * 100 + 1 else storeid * 100 end ))
   partition by range (salesdate)
   subpartition by list (subpartcode)
   subpartition template
      (subpartition st1_nocust values (100)
      , subpartition st1_cust values (101)
      , subpartition st2_nocust values (200)
      , subpartition st2_cust values (201)
      , subpartition sp_others values (default )
      )
  (partition p1 values less than ( to_date('01.01.2011','dd.mm.yyyy')),
   partition p2 values less than ( to_date('01.02.2011','dd.mm.yyyy'))
  );

-- Testdaten
insert into test_part_range_list (storeid, salesdate, customerid)
values (1, '31.12.2010', 4711);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (1, '31.12.2010', 0);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (1, '31.01.2011', 4711);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (1, '31.01.2011', 0);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (2, '31.12.2010', 1234);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (2, '31.12.2010', 0);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (2, '31.01.2011', 1234);  

insert into test_part_range_list (storeid, salesdate, customerid)
values (2, '31.01.2011', 0);  

-- Statistikerhebung auf Subpartitionsebene
exec dbms_stats.gather_table_stats(user, 'TEST_PART_RANGE_LIST', Granularity => 'SUBPARTITION')

-- Verteilung der Sätze auf die Subpartitionen
select partition_name
     , subpartition_name
     , num_rows 
  from dba_tab_subpartitions 
 where table_name = 'TEST_PART_RANGE_LIST';  

PARTITION_NAME                 SUBPARTITION_NAME                NUM_ROWS
------------------------------ ------------------------------ ----------
P1                             P1_ST1_NOCUST                           1
P1                             P1_ST1_CUST                             1
P1                             P1_ST2_NOCUST                           1
P1                             P1_ST2_CUST                             1
P1                             P1_SP_OTHERS                            0
P2                             P2_ST1_NOCUST                           1
P2                             P2_ST1_CUST                             1
P2                             P2_ST2_NOCUST                           1
P2                             P2_ST2_CUST                             1
P2                             P2_SP_OTHERS                            0

Die Partitionierung führt also zunächst zur gewünschten Verteilung der Daten auf die Partitionen (wobei die Default-Sub-Partitionen leer bleiben; in ihnen landen alle Sätze, für die im Subpartition Template keine eigene (Sub-)Partition definiert ist. Nun aber zur Frage, ob diese Partitionierung sinnvolles Partition Pruning erlaubt - also den Ausschluss von Partitionen, die keine relevanten Daten enthalten, im Rahmen der Abarbeitung entsprechender Queries. Dazu liefert Autotrace folgende Ergebnisse:

select *
  from test_part_range_list

   STOREID SALESDAT CUSTOMERID SUBPARTCODE
---------- -------- ---------- -----------
         1 31.12.10       4711         100
         1 31.12.10          0         101
         2 31.12.10       1234         200
         2 31.12.10          0         201
         1 31.01.11       4711         100
         1 31.01.11          0         101
         2 31.01.11       1234         200
         2 31.01.11          0         201

8 Zeilen ausgewõhlt.

Abgelaufen: 00:00:00.04

Ausführungsplan
----------------------------------------------------------
Plan hash value: 1185884213

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |     8 |   136 |    21   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|                      |     8 |   136 |    21   (0)| 00:00:01 |     1 |     2 |
|   2 |   PARTITION LIST ALL|                      |     8 |   136 |    21   (0)| 00:00:01 |     1 |     5 |
|   3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     8 |   136 |    21   (0)| 00:00:01 |     1 |    10 |
------------------------------------------------------------------------------------------------------------


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         62  consistent gets
          0  physical reads
          0  redo size
        917  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

Diese 62 consistent gets sind also der Referenzwert für das Lesen aller Partitionen. Die folgenden Autotrace-Listings sind aus Gründen der Übersichtlichkeit massiv gekürzt:

-- Datumseinschränkung
select *
  from test_part_range_list
 where salesdate = '31.12.2010';

------------------------------------------------------------------------------
| Id  | Operation              | Name                 | Rows | Pstart| Pstop |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                      |     4|       |       |
|   1 |  PARTITION RANGE SINGLE|                      |     4|     1 |     1 |
|   2 |   PARTITION LIST ALL   |                      |     4|     1 |     5 |
|*  3 |    TABLE ACCESS FULL   | TEST_PART_RANGE_LIST |     4|     1 |     5 |
-------------------------------------------------------------------------------

Statistiken
----------------------------------------------------------
         33  consistent gets
          4  rows processed

--> partition pruning funktioniert (was auf dieser Ebene keine Überraschung ist)

-- Einschränkung auf StoreId
select *
  from test_part_range_list
 where storeid = 1;

----------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |     4 |       |       |
|   1 |  PARTITION RANGE ALL|                      |     4 |     1 |     2 |
|   2 |   PARTITION LIST ALL|                      |     4 |     1 |     5 |
|*  3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     4 |     1 |    10 |
----------------------------------------------------------------------------		  

Statistiken
----------------------------------------------------------
         62  consistent gets
          4  rows processed	  

--> kein Pruning: PARTITION RANGE ALL und PARTITION LIST ALL
--> offenbar kann der Wert der virtuellen Spalte subpartcode nicht aus
    der StoreId abgeleitet werden

-- Einschränkung auf CustomerId
select *
  from test_part_range_list
 where customerid = 4711;

----------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |     3 |       |       |
|   1 |  PARTITION RANGE ALL|                      |     3 |     1 |     2 |
|   2 |   PARTITION LIST ALL|                      |     3 |     1 |     5 |
|*  3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     3 |     1 |    10 |
----------------------------------------------------------------------------		  

Statistiken
----------------------------------------------------------
         62  consistent gets
          2  rows processed

--> gleiches Verhalten wie bei der StoreId, was wiederum konsequent ist

-- Kombinierte Bedingung mit StoreId und CustomerId
select *
  from test_part_range_list
 where storeid = 1
   and customerid = 4711;

----------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |     1 |       |       |
|   1 |  PARTITION RANGE ALL|                      |     1 |     1 |     2 |
|   2 |   PARTITION LIST ALL|                      |     1 |     1 |     5 |
|*  3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     1 |     1 |    10 |
----------------------------------------------------------------------------

Statistiken
----------------------------------------------------------
         62  consistent gets
          2  rows processed		  

--> auch die Kombination der beiden Basiswerte, aus denen sich die virtuelle
    Partitionsschlüsselspalte definiert, führt nicht zum Partition Pruning

-- Bedinung mit der virtuellen Subpartitionsspalte
select *
  from test_part_range_list
 where subpartcode = 201;

-------------------------------------------------------------------------------
| Id  | Operation              | Name                 | Rows  | Pstart| Pstop |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                      |     2 |       |       |
|   1 |  PARTITION RANGE ALL   |                      |     2 |     1 |     2 |
|   2 |   PARTITION LIST SINGLE|                      |     2 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | TEST_PART_RANGE_LIST |     2 |   KEY |   KEY |
-------------------------------------------------------------------------------

Statistiken
----------------------------------------------------------
         16  consistent gets
          2  rows processed

--> mit dem virtuellen subpartcode funktioniert das Pruning

Demnach sorgt die virtuelle Spalte "subpartcode" zwar für eine sinnvolle Verteilung der Daten auf die Subpartitionen, aber das Partition Pruning funktioniert auf Subpartitionsebene nur, wenn dieses Attribut auch als Bedingung in den zugehörigen Queries erscheint. Damit ist aber auch dieser zweite Fall nur noch von akademischem Interesse, da die Queries, die auf den Daten operieren sollen, nicht notwenig beide Einschränkungen enthalten - und schon gar nicht die virtuelle Zusatzspalte verwenden.

Da der Eintrag jetzt schon recht unübersichtlich geworden ist, verschiebe ich weitere Untersuchungen (Hash-Subpartitioning, Range-Partitioning mit mehreren Spalten) auf ein andermal.