Split Partition und Indizes

Nur eine kurze Notiz: sofern man ins Kommando ALTER TABLE ... SPLIT PARTITION ...; nicht explizit die Option UPDATE INDEXES aufnimmt, führt das das Splitting zur Invalidierung der Indizes in der Quell- und in der Zielpartition, sofern anschließend in beiden Partitionen Werte enthalten sind:

-- 11.1.0.7
drop table test_mpr;

create table test_mpr (
    part_col number
  , col1 number
)
partition by list (part_col) 
(
    partition part_default values (default)
)
;
 
create index test_mpr_ix on test_mpr (
     col1
) local;
 
insert into test_mpr (part_col, col1) values (1, 1);
insert into test_mpr (part_col, col1) values (2, 2);
 
commit;

select partition_name
     , status 
  from user_ind_partitions
 where index_name = 'TEST_MPR_IX';

PARTITION_NAME                 STATUS
------------------------------ --------
PART_DEFAULT                   USABLE

alter table test_mpr 
split partition part_default values(1)
into (partition p1, partition part_default); 

select partition_name
     , status 
  from user_ind_partitions
 where index_name = 'TEST_MPR_IX';

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             UNUSABLE
PART_DEFAULT                   UNUSABLE

Nach dem Split enthalten beide Tabellen-Partitionen je einen Satz und beide Index-Partitionen sind UNUSABLE. Anders verhält es sich, wenn die Default-Partition nach dem Split leer ist: dann behandelt Oracle die Operation offenbar als reine Umbenennung und die Indizes bleiben USABLE. Um die Indizes nach dem Split in jedem Fall funktionsfähig zu erhalten, dient die UPDATE INDEXES-Klausel:

alter table test_mpr
split partition part_default values(1)
into (partition p1, partition part_default)
update indexes;

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             USABLE
PART_DEFAULT                   USABLE

Ob die Maintainance der Indizes im Fall der Verwendung von UPDATE INDEXES aufwändiger ist als bei einem kompletten Rebuild wäre gelegentlich auch noch interessant.

Nachtrag 31.10.2012: beim Vergleich der v$sesstat-Inhalte für den Fall der Verwendung von UPDATE INDEXES und den des expliziten REBUILD der Index-Partitionen nach einem Split ohne UPDATE INDEXES kann ich keine signifikanten Verarbeitungsunterschiede erkennen: anscheinend erfolgt in beiden Fällen ein kompletter Neuaufbau der Indizes. Das gilt auch für einen 99:1-Split, bei dem man argumentieren könnte, dass eine behutsame Aktualisierung des Index effizienter wäre. Allerdings würde das Verfahren dadurch natürlich auch komplizierter und fehleranfälliger.

Nachtrag 25.11.2012: für die Statistiken gelten ähnliche Gesetzmäßigkeiten: ein Split ohne Migration von Daten im Segment (also ein Split, der als Metadatenänderung nur einer Umbennenung entspricht), führt nicht zur Invalidierung der Statistiken. Wenn der Split aber Daten verschiebt, dann werden die Partitionsstatistiken gelöscht - ein Verhalten, das ich für völlig konsistent und schlüssig halte.

Oracles Big Data Lösungen

Keine eigenen Gedanken, nur ein Link (bzw. mehrere) ...

Mark Rittman hat in seinem Blog eine interessante Serie zu Oracles aktuellen Lösungen im Umfeld Big Data Analysis veröffentlicht:

• Oracle Exalytics, Oracle R Enterprise and Endeca Part 1 : Oracle’s Analytics, Engineered Systems, and Big Data Strategy
• Oracle Exalytics, Oracle R Enterprise and Endeca Part 2 : Oracle Endeca, the Advanced Analytics Option and Oracle Exalytics
• Oracle Exalytics, Oracle R Enterprise and Endeca Part 3 : Flight Delays Analysis using OBIEE, Endeca and Oracle R Enterprise on Exalytics

Sperrige Titel (die bei mir irgendwie die Assoziation an Tlön, Uqbar, Orbis Tertius aufrufen), aber sehr interessante Ausführungen, die das Zusammenspiel der Oracle-BI-Tools verdeutlichen. Ich spare mir das Exzerpieren, da ich aktuell mit keinem der angesprochenen Produkte arbeite (obwohl das sicher spannend wäre ...).

Materialized Views und ORA-32036

Vor längerer Zeit hatte ich erwähnt, dass die Verwendung von Queries mit subquery factoring (CTEs, WITH-Klauseln) als Datengrundlage für das SSAS-Prozessing oder auch für SSRS-Berichte den (nicht unbedingt selbsterklärenden) Fehler "ORA-32036: Nicht unterstützte Schreibweise für Inlining von Abfrage-Name in WITH-Klausel" hervorrufen kann. Dabei konnte man die Queries über sqlplus oder SQL Developer problemlos ausführen, aber beim Zugriff der Microsoft-Systeme ergaben sich die Probleme, die sich nur durch Refakturierung des Codes oder größere Umwege (z.B. die Definition entsprechender table functions) umgehen ließen.

Gestern ist mir ORA-32036 wieder begegnet: diesmal bei der Anlage einer Materialized View. Und in diesem Fall lässt sich das Verhalten sehr leicht reproduzieren:

-- Anlage von zwei - sehr simplen - Basistabellen
create table test_base_mpr
as
select rownum id
     , mod(rownum, 2) col1
  from dual
connect by level <= 100;

create table test_add_mpr
as
select rownum col1
  from dual
connect by level <= 10;

create materialized view test_mpr
as
with
basedata as (
select test_base_mpr.id
     , test_base_mpr.col1
  from test_base_mpr
  left outer join 
       test_add_mpr
    on (test_base_mpr.col1 = test_add_mpr.col1)
)
,
basedata_filtered as (
select * 
  from basedata
 where id <= 50
)
,
basedata_grouped as (
select col1
     , count(*) count_id
  from basedata_filtered
 group by col1
)
select basedata_filtered.col1
     , basedata_grouped.count_id
  from basedata_filtered
  left outer join
       basedata_grouped
    on basedata_filtered.col1 = basedata_grouped.col1
 order by basedata_filtered.col1;
 
FEHLER in Zeile 1:
ORA-32036: Nicht unterstützte Schreibweise für Inlining von Abfrage-Name in WITH-Klausel

-- wenn man die CTE basedata in eine inline-View umwandelt, gibt es keine Probleme:
create materialized view test_mpr
as
with
basedata_filtered as (
select *
  from (select test_base_mpr.id
             , test_base_mpr.col1
          from test_base_mpr
          left outer join
               test_add_mpr
            on (test_base_mpr.col1 = test_add_mpr.col1)
        ) basedata
 where id <= 50
)
,
basedata_grouped as (
select col1
     , count(*) count_id
  from basedata_filtered
 group by col1
)
select basedata_filtered.col1
     , basedata_grouped.count_id
  from basedata_filtered
  left outer join
       basedata_grouped
    on basedata_filtered.col1 = basedata_grouped.col1
 order by basedata_filtered.col1;

Materialized View wurde erstellt.

Auch in diesem Fall lässt sich die ursprüngliche Query via sqlplus problemlos ausführen (über die Semantik der Query und den Inhalt der Ergebnismenge - je 25 mal die Tupel (0, 25) und (1, 25) - muss man nicht weiter nachdenken). Auch kann man auf der Basis der Definition eine (nicht materialisierte) View anlegen. Aber die MV stellt offenbar ein Problem dar. Beim Vergleich der Pläne für die SELECTs (inklusive "Column Projection Information") sehe ich - abgesehen von generierten Query-Block- und temp-table-transformation-Namen - keine Unterschiede. Dabei sollte das SQL der Query dem des MV-Aufbaus entsprechen: zumindest im Fall des erfolgreichen Aufbaus trifft dies zu, hier scheint nur das übliche

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "TEST"."TEST_MPR"

vor der Definitions-Query.

Mein Versuch, die exakten Voraussetzungen des Problems zu bestimmen, bleibt erst einmal unvollständig. Ursprünglich nahm ich an, dass die Verwendung einer CTE an mehreren Stellen der Query genügen würde, um den Fehler hervorzurufen, aber auch die funktionierende Version verwendet die CTE basedata_filtered als Basis für basedata_grouped und als Element des Outer Joins im Haupt-SELECT. Möglicherweise spielt tatsächlich nur die CTE-Verschachtelungstiefe eine Rolle. In jedem Fall ist das Problem ziemlich unerfreulich, da es dazu zwingt, den durch CTEs verständlich gegliederten SQL-Code wieder in eine Form zu bringen, die der CBO offenbar besser versteht - die aber für den Entwickler in vielen Fällen schwerer zu durchschauen sein dürfte. Falls mir (oder jemandem, der hier vorbei kommt) jenseits der Phänomenologie eine plausible Erklärung dazu einfällt, liefere ich sie nach.

Aus Gründen der Vollständigkeit hier auch noch mal der Link auf Dom Brooks Überlegungen zum Thema - interessant sind dort auch die Kommentare.

COUNT und bitmap Indizes

Zu den alten Wahrheiten, mit denen ich seit Jahren meine unvorsichtigen Zuhörer langweile, gehört, dass es keinen Unterschied macht, ob man in einer Query count(*) oder count(1) zum Zählen der Sätze verwendet. Falls jemand Details zum Thema wünscht, verweise ich auf Tom Kyte, der das Thema bereits 2001 abschließend beantwortet hat. Jetzt liest man bei Jonathan Lewis, dass es im Fall vorliegender bitmap Indizes allerdings einen Unterschied macht, ob man count(1) oder count(-1) verwendet: für den count(1)-Fall (und ebenso für count(*)) kann Oracle die Abkürzung des BITMAP CONVERSION COUNT verwenden, für count(-1) muss hingegen eine BITMAP CONVERSION TO ROWIDS durchgeführt werden. Immer wieder erstaunlich, welche Überraschungen bei solch harmlosen Tests auftauchen.

Skip Scan Costing

Jonathan Lewis führt in seinem Blog ein ziemlich einfaches Beispiel vor, in dem aufgrund eines deutlich besseren ClusteringFactors ein SKIP SCAN auf die zweite Spalte eines zusammengesetzten Index erfolgt statt des (für den menschlichen Benutzer plausibleren) Zugriffs über einen Index, der nur diese Spalte beinhaltet. Er weist dabei auch darauf hin, dass das Kostenmodell für SKIP SCANs insgesamt nicht viel taugt, was sich mit meinen Beobachtungen deckt - ich betrachte den SKIP SCAN fast schon als Warnsignal; umgekehrt tritt er dann aber nicht unbedingt dort auf, wo er hilfreich wäre.

Bitmap Indizes und Partitions-Compression

Im Fall des folgenden Problems wundere ich mich, dass mir das erst jetzt aufgefallen ist - aber vielleicht habe ich frühere Begegnungen auch einfach vergessen. Es handelt sich um eine jener kleinen Asymmetrien, bei denen ich mir die Frage stelle, ob es dafür eine inhaltliche Begründung gibt: das nachträgliche Komprimieren einzelner Partitionen von Tabellen mit lokalen B*Tree Indizes ist eine ganz harmlose Operation. Kommen aber bitmap Indizes ins Spiel, wird der Vorgang komplizierter. Dazu ein Test.

Fall 1: nachträgliche Komprimierung einer Partition mit B*Tree Index

-- 11.1.0.7
-- Anlage einer List-partitionierten Tabelle mit zwei Spalten
drop table list_part;
create table list_part (
    start_date date
  , col1 number
)
partition by list (start_date)
(
    partition P201201 values (to_date('01.01.2012', 'dd.mm.yyyy'))
  , partition P201202 values (to_date('01.02.2012', 'dd.mm.yyyy'))
  , partition P201203 values (to_date('01.03.2012', 'dd.mm.yyyy'))
  , partition P201204 values (to_date('01.04.2012', 'dd.mm.yyyy'))
  , partition P201205 values (to_date('01.05.2012', 'dd.mm.yyyy'))
  , partition P201206 values (to_date('01.06.2012', 'dd.mm.yyyy'))
  , partition P201207 values (to_date('01.07.2012', 'dd.mm.yyyy'))
  , partition P201208 values (to_date('01.08.2012', 'dd.mm.yyyy'))
  , partition P201209 values (to_date('01.09.2012', 'dd.mm.yyyy'))
  , partition P201210 values (to_date('01.10.2012', 'dd.mm.yyyy'))
);

insert into list_part(start_date, col1) 
values (to_date('01.01.2012', 'dd.mm.yyyy'), 1);

-- Anlage eines lokalen B*Tree Index
create index list_part_ix_local on list_part (col1) local;

Im Lauf der Zeit wird mir nun klar, dass ich die älteren Partitionen dieser Tabelle gerne komprimieren würde:

alter table list_part modify partition P201201 compress;
--> Tabelle wurde geändert.
alter table list_part move partition P201201;
--> Tabelle wurde geändert.

select partition_name
     , status
  from user_ind_partitions
 where index_name = 'LIST_PART_IX_LOCAL'
/

PARTITION_NAME                 STATUS
------------------------------ --------
P201201                        UNUSABLE
P201202                        USABLE
P201203                        USABLE
P201204                        USABLE
P201205                        USABLE
P201206                        USABLE
P201207                        USABLE
P201208                        USABLE
P201209                        USABLE
P201210                        USABLE

Die nachträgliche Komprimierung der Einzelpartition funktioniert also ohne Probleme. Der lokale Index wird durch die physikalische Reorganisation der Tabelle aber natürlich unbenutzbar und muss neu aufgebaut werden.

Fall 2: nachträgliche Komprimierung einer Partition mit bitmap Index

Nun zum bitmap Index:

-- 11.1.0.7
-- Anlage einer List-partitionierten Tabelle mit zwei Spalten
drop table list_part;
create table list_part (
    start_date date
  , col1 number
)
partition by list (start_date)
(
    partition P201201 values (to_date('01.01.2012', 'dd.mm.yyyy'))
  , partition P201202 values (to_date('01.02.2012', 'dd.mm.yyyy'))
  , partition P201203 values (to_date('01.03.2012', 'dd.mm.yyyy'))
  , partition P201204 values (to_date('01.04.2012', 'dd.mm.yyyy'))
  , partition P201205 values (to_date('01.05.2012', 'dd.mm.yyyy'))
  , partition P201206 values (to_date('01.06.2012', 'dd.mm.yyyy'))
  , partition P201207 values (to_date('01.07.2012', 'dd.mm.yyyy'))
  , partition P201208 values (to_date('01.08.2012', 'dd.mm.yyyy'))
  , partition P201209 values (to_date('01.09.2012', 'dd.mm.yyyy'))
  , partition P201210 values (to_date('01.10.2012', 'dd.mm.yyyy'))
);

insert into list_part(start_date, col1) 
values (to_date('01.01.2012', 'dd.mm.yyyy'), 1);

-- Anlage eines lokalen bitmap Index
-- wobei der Versuch, auf einer partitionierten Tabelle einen globalen bitmap Index 
-- zu erzeugen, mit der wunderbar klaren Fehlermeldung "ORA-25122
-- Bei partitionierten Tabellen sind nur LOCAL-Bitmap-Indizes zulässig"
-- beantwortet wird.
create bitmap index list_part_bix_local on list_part (col1) local;

Und auch für diesen Fall will ich die erste Partition nachträglich komprimieren:

alter table list_part modify partition P201201 compress;
--> Tabelle wurde geändert.
alter table list_part move partition P201201;

FEHLER in Zeile 1:
ORA-14646: Der angegebene Vorgang zur Änderungen einer Tabelle
der eine Komprimierung umfasst, kann nicht ausgeführt werden,
wenn verwendbare Bitmap-Indizes vorhanden sind.

Na gut, dann mache ich die Index-Partition eben UNUSABLE:

alter index list_part_bix_local modify partition P201201 unusable;
--> Index wurde geändert.
alter table list_part move partition P201201;

FEHLER in Zeile 1:
ORA-14646: Der angegebene Vorgang zur Änderungen einer Tabelle
der eine Komprimierung umfasst, kann nicht ausgeführt werden,
wenn verwendbare Bitmap-Indizes vorhanden sind.

Das hilft also nicht. Tatsächlich muss in diesem Fall nicht nur die lokale Partition, sondern der gesamte Index UNUSABLE gesetzt und später wieder per REBUILD neu aufgebaut werden:

alter index list_part_bix_local unusable;
--> Index wurde geändert.
alter table list_part move partition P201201;
--> Tabelle wurde geändert.

alter index list_part_bix_local rebuild;
FEHLER in Zeile 1:
ORA-14086: Ein partitionierter Index kann nicht als ganzes neu erstellt werden

Immerhin kann man alle Indizes einer Partition mit dem Kommando

alter table ... modify partition ... rebuild unusable local indexes;

neu erzeugen, was ich gelegentlich schon mal erwähnt hatte.

Fehlt noch eine Antwort auf die Frage, ob es einen guten Grund für das unterschiedliche Verhalten gibt. Dazu ist zunächst zu sagen, dass die Oracle-Dokumentation den hier dargestellten Fall explizit erläutert. Dort heißt es:

This rebuilding of the bitmap index structures is necessary to accommodate the potentially higher number of rows stored for each data block with table compression enabled. Enabling table compression must be done only for the first time. All subsequent operations, whether they affect compressed or uncompressed partitions, or change the compression attribute, behave identically for uncompressed, partially compressed, or fully compressed partitioned tables.

To avoid the recreation of any bitmap index structure, Oracle recommends creating every partitioned table with at least one compressed partition whenever you plan to partially or fully compress the partitioned table in the future. This compressed partition can stay empty or even can be dropped after the partition table creation.

Having a partitioned table with compressed partitions can lead to slightly larger bitmap index structures for the uncompressed partitions. The bitmap index structures for the compressed partitions, however, are usually smaller than the appropriate bitmap index structure before table compression. This highly depends on the achieved compression rates.

Es scheint also doch einen guten Grund für das Verhalten zu geben. Ein Blick in Julian Dykes klassische Präsentation Bitmap Internals erklärt das Verhalten: dort wird auf Folie 37 erklärt, dass für die Komprimierung der bitmaps der Hakan Factor der Tabelle relevant ist. Den Hakan Factor findet man in TAB$:

select OBJ#
     , spare1 
  from sys.tab$
 where OBJ# = 105354;

-- vor dem MOVE der Partition
      OBJ#     SPARE1
---------- ----------
    105354        736

-- nach dem MOVE der Partition
      OBJ#     SPARE1
---------- ----------
    105354     163831

Der Hakan Factor ist demnach ein Attribut der kompletten Tabelle (die OBJECT_IDs der Partitionen findet man nicht in TAB$) und ändert sich erst nach dem Neuaufbau einer Partition - also nicht bereits mit dem MODIFY PARTITION ... COMPRESS, sondern erst mit dem MOVE, also der physikalischen Reorganisation. Das Verhalten ist somit begründet - und mehr wollte ich gar nicht wissen.

TNS Protocol Internals

Gwen Shapira hat im Pythian Blog Ian Redferns klassische Untersuchung Oracle Protocol reposted, die in den Tiefen des Netzes unterzugehen drohte. Darin findet man allerlei Grundlegendes zu den internen Mechanismen des TNS Protokolls. Interessant für den Fall, dass ich mal von meiner Linie abgehe, Netzwerkfragen als Problem anderer Leute zu klassifizieren.

Maximale child cursor-Anzahl

Christian Antognini untersucht in seinem Blog die Frage, wie viele child cursor zu einem parent cursor erzeugt werden können, und liefert die Antwort 65536 - die er aus dem trace-file eines ora-600-Fehlers ableitet (wobei mich sein Vorgehen mehr interessiert als der Ergebniswert, denn wer will schon 65536 child cursor erzeugen?). In 11.2.0.3 wurde das Limit über den Parameter _cursor_obsolete_threshold auf den Default-Wert 100 gesenkt.

MDX-Optimierung durch Syntaxumbau

Dass syntaktisch unterschiedliche aber semantisch identische SQL-Queries eine extrem unterschiedliche Performance aufweisen können, obwohl es dem Optimizer theoretisch möglich sein sollte, alle Varianten in die am besten geeignete Zugriffsvariante zu transformieren, ist die Grundlage vieler Optimierungsoperationen in relationalen Datenbanken. Aktuelle RDBMS sind dabei allerdings so gut instrumentiert, dass das Verhalten und die Wirkung solcher Umformulierungen recht genau zu bestimmen sind.

Für SSAS und MDX sieht das (leider) anders aus: dort muss man durch Tests - man könnte auch sagen: wildes Rumprobieren - bestimmen, welche Syntaxvariante für eine bestimmte Datenstruktur und eine bestimmte Datenmenge die geeignetste ist. Sehr hilfreich ist es, wenn sich jemand die Mühe macht, solche Tests durchzuführen und die Ergebnisse zu dokumentieren, wie das Will Färber im Blog von Trivadis für das Thema der Clusterung von Umsatzgruppen in MDX getan hat.

Ich glaube, ich hab's schon mal erwähnt: Ausführungspläne für MDX-Queries wären eine schöne Sache. Aber da ich die SSAS-Zukunft eher skeptisch betrachte, glaube ich nicht, dass damit noch zu rechnen ist.

MDX und PowerPivot

Chris Webb hat vor kurzem eine Serie Introduction to MDX for PowerPivot Users gestartet, die zur Zeit zwei Artikel umfasst, aber vermutlich noch wachsen wird. Mein Eindruck ist, dass der Herr Webb den schleichenden Niedergang von SSAS und MDX mit mehr Wehmut betrachtet als die Kollegen Russo und Ferrari. Seine These zum Zusammenhang von MDX und PowerPivot lautet dabei:

The more I use PowerPivot, the more I realise how many more cool things you can do when building your Excel reports if you know a bit of MDX. Of course it seems a bit strange to say this, because (at least if you hear Marco and Alberto present at a conference!) MDX is a bit passé and DAX is the native language of PowerPivot, SSAS Tabular and, well the future. But Excel speaks MDX and Excel will continue to speak MDX for a long time to come, so it’s MDX that you need to know if you want to get the most out of PowerPivot.

Ich hoffe, es gelingt mir, die folgende Liste aktuell zu halten, aber versprechen will ich nichts.

• Introduction to MDX for PowerPivot Users, Part 1: erklärt, welche MDX-Elemente für den PowerPivot-Anwender interessant sein könnten (Excel Cube Funktionen, Create Set Optionen u.a.). Außerdem gibt's eine kurze terminologische Klärung zu den Namen der Dinge in MDX und PowerPivot.
• Introduction to MDX for PowerPivot Users, Part 2: Basic Sets: erläutert die Rolle (oder das Konzept) von Tuples und Sets in MDX.
• Introduction to MDX for PowerPivot Users, Part 3: The Members() and Crossjoin() functions: erläutert die Funktionen MEMBERS (die alle Elemente einer Hierarchie oder Hierarchie-Ebene liefert) und CROSSJOIN (die - wie man sich vielleicht schon denken konnte - ein Kreuzprodukt der beiden übergebenen Mengen liefert; an ihrer Stelle kann man auch den *-Operator verwenden, der dem Herrn Webb - und mir - besser gefällt). Durch Verwendung eines named set  mit den über MEMBERS und CROSSJOIN generierten Menge der möglichen Permutationen kann man Excel davon abhalten, in de PivotTable subtotals und grand totals abzurufen.

Global Temporary Tables und CTAS

Temporäre Tabellen gehören nicht zu den Objekten, über die ich mir allzu häufig Gedanken mache. Gestern habe ich allerdings ein recht interessantes Verhalten beobachtet, das ich in Zukunft einsetzen kann, wenn ich nicht möchte, dass die Datenbank anfängt sich zu langweilen:

create global temporary table test_mpr
as
select rownum rn
     , lpad('*', 50, '*') col1
  from dual
connect by level <= 1000000;

Tabelle wurde erstellt.

select count(*)
  from test_mpr;

  COUNT(*)
----------
         0

Auf die Idee, eine temporäre Tabelle über CTAS zu befüllen, wäre ich allein nie gekommen, aber das Ergebnis ist interessant. Ein Blick in v$sql zeigt, dass tatsächlich 1M rows erzeugt wurden, die aber nach Abschluss der Operation nicht mehr verfügbar sind. In v$sesstat sieht man allerdings, dass die Anzahl der user commits durch die CTAS-Operation um 1 erhöht wurde. Demnach wird die temporäre Tabelle also gefüllt, aber durch das implizite COMMIT am Ende der CTAS-Operation gleich wieder geleert. Um die Sätze zu erhalten benötigt man folglich eine temporäre Tabelle mir der ON COMMIT PRESERVE ROWS-Option:

create global temporary table test_mpr
on commit preserve rows
as
select rownum rn
     , lpad('*', 50, '*') col1
  from dual
connect by level <= 1000000;

Tabelle wurde erstellt.

select count(*)
  from test_mpr;

  COUNT(*)
----------
   1000000

NULL-Werte und row size

Jonathan Lewis weist in seinem Blog darauf hin, dass NULL-Werte in den letzten Spalten einer Tabelle keinen Speicherplatz benötigen, so lange ihnen kein Attribut mit einem "richtigen" Wert folgt. Gerade für große DWH-Tabellen mit vielen Attributen, die nicht regelmäßig verwendet werden (Stichwort: Satzarten), kann demnach die Reihenfolge der Spalten eine große Rolle spielen. Unter Umständen könnten solche Tabellen von einer physikalischen Reorganisation profitieren.

Cardinality-Schätzung mit METHOD_OPT FOR ALL INDEXED COLUMNS

Eigentlich wollte ich heute etwas ganz anderes untersuchen, aber das, was ich zeigen wollte, konnte ich nicht reproduzieren - und deshalb zeige ich jetzt einen möglicherweise verwandten Fall, der sich in meinem Test ergeben hat. Gegeben ist eine nach Monatsdatumsangaben LIST-partitionierte Tabelle mit zehn Partitionen, in denen jeweils 1000 Sätze vorliegen (die elfte Partition für 1970 spielt hier gar keine Rolle, aber ich mag sie jetzt nicht mehr entfernen):

-- 11.1.0.7
drop table list_part;

create table list_part (
    start_date date
  , col1 number
)
partition by list (start_date) 
(
    partition P_min values (to_date('01.01.1970', 'dd.mm.yyyy'))
  , partition P201201 values (to_date('01.01.2012', 'dd.mm.yyyy'))
  , partition P201202 values (to_date('01.02.2012', 'dd.mm.yyyy'))
  , partition P201203 values (to_date('01.03.2012', 'dd.mm.yyyy'))
  , partition P201204 values (to_date('01.04.2012', 'dd.mm.yyyy'))
  , partition P201205 values (to_date('01.05.2012', 'dd.mm.yyyy'))
  , partition P201206 values (to_date('01.06.2012', 'dd.mm.yyyy'))
  , partition P201207 values (to_date('01.07.2012', 'dd.mm.yyyy'))
  , partition P201208 values (to_date('01.08.2012', 'dd.mm.yyyy'))
  , partition P201209 values (to_date('01.09.2012', 'dd.mm.yyyy'))
  , partition P201210 values (to_date('01.10.2012', 'dd.mm.yyyy'))
);

insert into list_part
select add_months(to_date('01.01.2012', 'dd.mm.yyyy'), trunc((rownum - 1)/1000) ) start_date
     , rownum col1
  from dual
connect by level <= 10000;  

commit;

-- Fall 1: Standard-Statistiken
exec dbms_stats.gather_table_stats(user, 'list_part')

explain plan for
select count(*) 
  from list_part
 where start_date >= to_date('20121001','yyyymmdd') 
   and start_date <= to_date('20121031','yyyymmdd');

select * from table(dbms_xplan.display);

-- Fall 2: keine Statistiken
exec dbms_stats.delete_table_stats(user, 'list_part')

explain plan for
select count(*) 
  from list_part
 where start_date >= to_date('20121001','yyyymmdd') 
   and start_date <= to_date('20121031','yyyymmdd');

select * from table(dbms_xplan.display);

-- Fall 3: method_opt => 'for all indexed columns'
exec dbms_stats.gather_table_stats(user, 'list_part', method_opt => 'for all indexed columns')

explain plan for
select count(*) 
  from list_part
 where start_date >= to_date('20121001','yyyymmdd') 
   and start_date <= to_date('20121031','yyyymmdd');

select * from table(dbms_xplan.display);

Die Ergebnisse des Tests sehen dann folgendermaßen aus:

-- Fall 1: Standard-Statistiken
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |     8 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |           |     1 |     8 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|           |  1000 |  8000 |     3   (0)| 00:00:01 |    11 |    11 |
|   3 |    TABLE ACCESS FULL   | LIST_PART |  1000 |  8000 |     3   (0)| 00:00:01 |    11 |    11 |
----------------------------------------------------------------------------------------------------

-- Fall 2: keine Statistiken
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |     9 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |           |     1 |     9 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|           |  1000 |  9000 |     3   (0)| 00:00:01 |    11 |    11 |
|   3 |    TABLE ACCESS FULL   | LIST_PART |  1000 |  9000 |     3   (0)| 00:00:01 |    11 |    11 |
----------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

-- Fall 3: method_opt => 'for all indexed columns' 
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |     9 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |           |     1 |     9 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|           |     3 |    27 |     3   (0)| 00:00:01 |    11 |    11 |
|   3 |    TABLE ACCESS FULL   | LIST_PART |     3 |    27 |     3   (0)| 00:00:01 |    11 |    11 |
----------------------------------------------------------------------------------------------------

Fall 1 und Fall 2 liefern die korrekte Schätzung von 1000 rows aus der letzten Partition der Tabelle - einmal auf der Basis solider Statistiken und einmal auf der Basis von dynamic sampling. Nur Fall 3 liefert ein völlig anderes Ergebnis: da in der Tabelle kein Index existiert, erzeugt die method_opt => 'for all indexed columns' für keine Spalte globale Statistikinformationen (was diese recht verbreitete method_opt ziemlich gefährlich machen kann). Da in diesem Fall - ziemlich magere - Statistiken vorliegen, erfolgt kein dynamic sampling. Ohne Spalten-Statistik scheint der CBO aber auf die Arithmetik für bound ranges zurückzufallen, also Satzanzahl_der_Partition * 5% * 5% = 1000 * 0.05 * 0.05 = 2.5, was dann aufgerundet 3 ergibt. Wenn ich auf die Obergrenze des Ranges verzichte, erhalte ich:

explain plan for
select count(*)
  from list_part
 where start_date >= to_date('20121001','yyyymmdd');

select * from table(dbms_xplan.display);

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |     9 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |           |     1 |     9 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|           |    50 |   450 |     3   (0)| 00:00:01 |    11 |    11 |
|   3 |    TABLE ACCESS FULL   | LIST_PART |    50 |   450 |     3   (0)| 00:00:01 |    11 |    11 |
----------------------------------------------------------------------------------------------------

Das entspricht meiner Erwartung: 1000 * 0.5 = 50. Meine (vorläufige) Interpretation des Verhaltens lautet: durch Partition Elimination kann der CBO die relevante Partition bestimmen und rechnet in der Folge mit ihrer Satzanzahl. Das Fehlen von Spaltenstatistiken macht die Bestimmung der Filterung durch den Datumsrange dann aber zu einem Ratespiel, bei dem der CBO mit Standard-Selektivitäten rechnet. Theoretisch könnte der Optimizer erkennen, dass die gegebene Bedingung innerhalb der Partition keine Filterwirkung mehr haben kann, aber offenbar bringt er die Informationen zum Partition_Key und zum Datumsrange nicht zusammen. Da mir dergleichen auch mit schöner Regelmäßigkeit entgeht, will ich ihn dafür nicht kritisieren.

Im praktischen Fall, den ich nachbauen wollte, kam der CBO trotz vorliegender Spaltenstatistiken zu einem ähnlichen Ergebnis, aber da ich dieses Verhalten im Test nicht reproduzieren konnte, will ich dazu erst mal nichts Weiteres sagen.

Ein paar Details zu Oracle 12c

Auf der Oracle OpenWorld 2012 wurden offenbar ziemlich viele interessante Details zum Release 12c vorgestellt. Hier eine sehr kurze Liste von Artikeln, die einige dieser neuen Features ansprechen:

• Tim Hall: Oracle OpenWorld 2012 : Day 5
• umfangreiche Liste mit wichtigen Verbesserungen
• Adaptive Execution Plans: wenn der CBO während der Ausführung erkennt, dass sein Mengengerüst nicht stimmt, dann kann er den Plan während der Ausführung korrigieren. Klingt interessant, aber auch ziemlich gefährlich
• Persistierung von bestimmten dynamic sampling Operationen
• verbesserte (height balanced) Histogramme, die pro Bucket Informationen zu mehr als einem Wert verzeichnen können
• Jonathan Lewis: Partitioning 12c
• Zusammenfassung mehrerer maintainance Operationen in einen Schritt (Splitting, Zusammenfassung mehrerer Partitionen)
• partitions-spezifische Indizierung
• online move für Partitionen (compression einer Partition, die den read-only-Zustand erreicht hat)
• Jonathan Lewis: Indexing 12c
• für eine Spaltenkombination können in Zukunft mehrere Indizes mit unterschiedlichen Eigenschaften (unique - nonunique, bitmap - b*tree, partitioned - non-partitioned) definiert werden, ohne dass der Fehler "ORA-01408: such column list already indexed" auftritt
• Alex Nuijten: OOW 2012: Little things make me happy
• Sequence-Werte können als Default-Werte in einer Tabellendefinition verwendet werden (z.B. als synthetischer Schlüssel); bisher häufig ein Fall für Trigger

Index-Aufbau und INSERT APPEND

Dass die Laufzeit eines INSERT APPEND in eine Tabelle mit aktiven Indizes höher ist als die eines entsprechenden INSERT APPEND in eine Tabelle ohne Indizes (oder mit Indizes im Zustand UNUSABLE), bei dem die Indizes nach der Ladeoperation neu aufgebaut werden, habe ich gelegentlich überprüft - und entsprechende Hinweise findet man auch ziemlich regelmäßig als Optimierungsvorschlag für ETL-Ladeprozessen. Den Grund für das unterschiedliche Verhalten konnte ich aber auf Anhieb nicht nennen: meiner Erinnerung nach sorgt der APPEND Hint hinsichtlich der Indizes dafür, dass die Maintainance verschoben wird bis die Daten in die Tabellenblocks eingefügt worden sind. Wo also liegt der Unterschied zum kompletten Neuaufbau? Ich habe dazu - natürlich - einen kleinen Test gemacht; aber wahrscheinlich hätte man sich die Antwort auch an zwei Fingern abzählen können ... Der Test erfolgte auf meinem PC mit 11.2.0.1, Windows 7, einer Noarchivelog-DB und sehr langsamen Platten, so dass ich keine allzu großen Datenmengen brauchte, um signifikante Laufzeiten zu erreichen:

drop table test_insert_source;
drop table test_insert_ind_recreate;
drop table test_insert_ind_maintain;

-- Anlage einer Quelltabelle mit 1M rows
create table test_insert_source
as
select rownum rn
     , mod(rownum , 2) col1
     , mod(rownum , 4) col2
     , mod(rownum , 8) col3
     , mod(rownum , 16) col4
     , lpad('*', 50, '*') col_pad
  from dual
connect by level <= 1000000;

-- Zieltabelle für Index-Anlage nach INSERT APPEND
create table test_insert_ind_recreate
as
select *
  from test_insert_source
 where 1 = 0;

-- Erstellung eines Snapshot von v$session_wait für die Test-Session
-- in einer zweiten Session:
-- create table recreate_start as
-- select *
--   from v$sesstat
--  where sid = 72;

-- Insert und folgender Aufbau der Indizes
insert /*+ append */ into test_insert_ind_recreate
select * from test_insert_source;

create index test_insert_ind_recreate_ix1 on test_insert_ind_recreate(col1);
create index test_insert_ind_recreate_ix2 on test_insert_ind_recreate(col2);
create index test_insert_ind_recreate_ix3 on test_insert_ind_recreate(col3);
create index test_insert_ind_recreate_ix4 on test_insert_ind_recreate(col4);

-- Erstellung eines Snapshot von v$session_wait für die Test-Session
-- in einer zweiten Session:
-- create table recreate_end as
-- select *
--   from v$sesstat
--  where sid = 72;


-- Zieltabelle für Index-Anlage vor INSERT APPEND
create table test_insert_ind_maintain 
as
select *
  from test_insert_source
 where 1 = 0;

-- Erstellung eines Snapshot von v$session_wait für die Test-Session
-- in einer zweiten Session:
-- create table maintain_start as
-- select *
--   from v$sesstat
--  where sid = 72;

-- Index-Anlage vor dem Insert
create index test_insert_ind_maintain_ix1 on test_insert_ind_maintain(col1);
create index test_insert_ind_maintain_ix2 on test_insert_ind_maintain(col2);
create index test_insert_ind_maintain_ix3 on test_insert_ind_maintain(col3);
create index test_insert_ind_maintain_ix4 on test_insert_ind_maintain(col4);

insert /*+ append */ into test_insert_ind_maintain
select * from test_insert_source;

-- Erstellung eines Snapshot von v$session_wait für die Test-Session
-- in einer zweiten Session:
-- create table maintain_end as
-- select *
--   from v$sesstat
--  where sid = 72;

Man hätte natürlich auch Adrian Billigtons runstat-Script zur Erfassung der Deltas verwenden können, aber die vier Snapshot erfüllen den gleichen Zweck:

with
recreate as (
select rs.statistic#
     , re.value - rs.value value_recreate
  from recreate_start rs
     , recreate_end re
 where rs.statistic# = re.statistic#
)
,
maintain as (
select ms.statistic#
     , me.value - ms.value value_maintain
  from maintain_start ms
     , maintain_end me
 where ms.statistic# = me.statistic#
)
, 
basedata as (
select recreate.statistic# statistic#
     , recreate.value_recreate
     , maintain.value_maintain
  from recreate
     , maintain
 where recreate.statistic# = maintain.statistic#
)
select sn.name
     , basedata.value_recreate
     , basedata.value_maintain
     , basedata.value_recreate - basedata.value_maintain diff
  from v$statname sn
     , basedata
 where sn.statistic# = basedata.statistic#
   and basedata.value_recreate <> basedata.value_maintain
 order by abs(basedata.value_recreate - basedata.value_maintain) desc

Diese Query liefert mir 121 Zeilen, von denen ich hier aber nur eine kleine Auswahl aufführe, zu der ich Erklärungen habe:

NAME                                         VALUE_RECREATE VALUE_MAINTAIN       DIFF
-------------------------------------------- -------------- -------------- ----------
-- recreate ist schneller
DB time                                                1937           3565      -1628
non-idle wait time                                     1418           2263       -845
CPU used by this session                                498           1266       -768

-- recreate muss mehr Leseoperationen 
-- durchführen
table scan rows gotten                              5014711        1014711    4000000
table scans (direct read)                                 4              0          4

-- maintain erzeugt mehr undo + redo 
-- und Schreiboperationen
physical read bytes                               407265280      169582592  237682688
physical write bytes                              144875520      251494400 -106618880
redo size                                         146141776      314122640 -167980864
undo change vector size                              140052      108339092 -108199040
db block gets                                         25017         212748    -187731

-- maintain kann nicht alle Sortierungen 
-- im Speicher ausführen
physical reads direct temporary tablespace                0          20701     -20701
physical writes direct temporary tablespace               0          20701     -20701
workarea executions - optimal                            10              4          6
workarea executions - onepass                             0              6         -6
sorts (disk)                                              0              3         -3

-- Index-Maintainance 
-- ist kein Index-Aufbau!
leaf node 90-10 splits                                    0           6885      -6885
leaf node splits                                          0           6885      -6885
branch node splits                                        0             12        -12
root node splits                                          0              4         -4

Dazu noch ein paar Erläuterungen:

• die nachträgliche Erzeugung der Indizes (recreate) benötigt für die komplette Operation ca. 19 sec gegenüber ca. 35 sec, die bei im Rahmen des INSERTs aktiven Indizes anfallen (maintain). Dabei verteilten sich diese 16 sec. Unterschied zu gleichen Teilen auf CPU und Waits
• im recreate-Fall werden deutlich mehr Sätze gelesen: nämlich ca. 5M gegenüber 1M für den maintain-Fall. Das ist unmittelbar einleuchtend, denn im recreate-Fall muss die Tabelle natürlich für jeden Index noch einmal komplett gelesen werden.
• der maintain-Fall ruft deutlich mehr redo und undo hervor, wobei sich die Unterschiede beim redo vermutlich aus der Verwendung einer Noarchivelog-DB ergeben, bei der APPEND-Operationen kein (oder wenig) redo erzeugen (jedenfalls für das Tabellen-Insert, die Indizes sind ein anderer Fall). Die Matrix von Tom Kyte zum Thema hatte ich schon mindestens einmal in diesem Blog verlinkt.
• der maintain-Fall kann die erforderlichen Sortierungen nicht komplett im Speicher durchführen und muss in einigen Fällen auf "workarea executions - onepass" ausweichen.
• der wichtigste Aspekt ist aber wohl, dass der maintain-Fall genau das tun muss, was sein Name schon andeutet: nämlich den Index sukzessive erweitern, statt ihn komplett neu aufbauen zu können. Zwar verschiebt die APPEND-Operation diesen Schritt ans Ende der Verarbeitung, aber er erfordert dann doch das allmähliche Erweitern der Index-Struktur um neue Knoten, was Splits auf Root-, Branch- und Leaf-Ebene hervorruft.

Der praktische Fall, für den sich die Frage nach der Performance der beiden Verfahren stellte, betraf die initiale Befüllung vorher leerer Tabellen - aber Oracle kann das nicht wissen und deshalb kann die Maintainance nicht durch einen kompletten Neuaufbau ersetzt werden. Kürzlich hat Alberto Dell'Era darüber geschrieben, dass Oracle in 11.2.0.3 den fast refresh einer MV, deren Parent MV über complete refresh neu gefüllt wurde, stillschweigend in einen complete refresh umwandelt (weil der complete refresh in solchen Fällen deutlich flotter ist als der fast refresh, der wirklich besser incremental refresh genannt worden wäre ...). Eine solche Operationsumwandlung wäre hier ebenfalls nett, aber vielleicht ein wenig viel verlangt. Sie landet in der gleichen Kategorie wie die (nicht existierende) Force-Option für's DROP TABLE, mit der ich manchmal gerne monumentale Rollback-Operationen für Tabellen abkürzen würde, die keinerlei relevanten Inhalt enthalten.

Intervall-Teilungslogik

Alberto Dell'Era zeigt in seinem Blog eine Lösung für das (im Retail-Kontext ziemlich allgegenwärtige) Problem der Zusammenfassung widersprüchlicher überlappender Intervallangaben (z.B. Preise oder Aktionszuordnungen eines Artikels), bei der er mit den analytischen Funktionen LEAD, LAG, SUM und DENSE_RANK operiert (und dadurch die üblichen Performance-Probleme bekommt). Ich habe in der Vergangenheit ähnliche Queries bebastelt, zuletzt aber gelegentlich auch für PL/SQL plädiert, als die Logik der Zusammenfassung besonders sperrig wurde (was nicht so selten vorkommt).