Index Skip Scan

Viele Fälle, in denen Index Skip Scans etwas Positives bewirkt haben, sind mir noch nicht begegnet, aber einige, in denen sie ziemlich unerfreuliche Folgen hatten. Ich hatte bisher noch nicht arg viel über die Frage nachgedacht, wann ein Skip Scan sinnvoll ist, aber Hemant Chitale liefert in seinem Blog eine sehr einleuchtende Erklärung:

Therefore, an Index Skip Scan is usable if the number of distinct values for the leading column(s) is sufficiently low that the number of "skips" in the Index does not become significant.

SSAS DistinctCount MeasureGroups

In aller Regel werden vermutlich mehr als 90% aller Kennzahlen im SSAS mit der Aggregationsfunktion SUM behandelt, aber die meisten andere Funktionenverhalten sich grundsätzlich relativ ähnlich wie die Summenfunktion und können mit ihr gemeinsam in einer MeasureGroup enthalten sein. Eine Ausnahme bildet die DistinctCountFunktion.

Grundsätzlich dient DistinctCount dazu, die Anzahl der unterschiedlichen Elemente in einer Menge zu bestimmen. Ein praktisches Beispiel wäre z.B. die Anzahl der Filialen, in denen der Artikel 4711 in einem bestimmten Monat verkauft wurde. Als SQL wäre das also etwa:

SELECT item_id
     , count(distinct store_id) 
  from …

Wenn man im BIDS eine neue DistinctCount-Kennzahl anlegt, wird automatisch eine zugehörige MeasureGroup angelegt. Der Grund dafür ist, dass die MeasureGroup nach dem Attributwert sortiert werden muss – und daraus ergibt sich dann auch, dass man für jede einzelne DistinctCount-Kennzahl eine neue MeasureGroup erhält.

Für das Processing bedeutet dieser Sachverhalt, dass die zugehörige SQL-Query (die nur die Schlüsselfelder und keine Kennzahlen abruft) ein ORDER BY enthält, und diese Sortierung kann für eine große Datenmenge natürlich ziemlich teuer werden. Deshalb lohnt es sich bereits, die Datenmenge durch Partitionierung zu verkleinern (wobei eine entsprechende Partitionierung der relationalen Tabelle natürlich besonders günstig wäre).

Für den Zugriff von MDX-Queries spielt die Partitionierung im Cube dann erneut eine wichtige Rolle: die Storage Engine bestimmt über die in den Header-Files enthaltenen Id-Ranges der Attribute, ob eine Partition im Rahmen einer Abfrage durchsucht werden muss (ähnliche – mäßig clevere – Range-Operationen macht der SSAS häufiger). Wenn die Partitionen nach dem DistinctCount-Attribut eingeteilt sind, dann ergibt sich z.B., dass Partition1 die Informationen für den Range Store_Id 1-10 enthält, Partition2 den Range Store_Id 11-20 etc. Wären die Partitionen nicht nach der Store_Id eingeteilt, dann würde sich für Partition1 und Partition2 wahrscheinlich jeweils ein Range Store_Id 1-20 ergeben – und entsprechend müssten beide Partitionen gelesen und die Ergebnisse anschließend gemischt werden. Das Beispiel ist jetzt ziemlich vereinfachend, aber das Prinzip sollte so aussehen. In der Praxis wäre vermutlich zusätzlich eine zeitliche Partitionierung sinnvoll, wie man in dem unter http://www.microsoft.com/downloads/en/details.aspx?FamilyID=65DF6EBF-9D1C-405F-84B1-08F492AF52DD erhältlichen WhitePaper Analysis Services Distinct Count Optimization nachlesen kann.

Man kann sich auch leicht ausrechnen, dass DistinctCount-Kennzahlen den Speicherbedarf eines Cubes deutlich erhöhen können. Ihr Einsatz sollte folglich auch genau durchdacht werden.

Visuelle Darstellung für SQL-Joins

Eine sehr schöne visuelle Darstellung von Join-Operationen liefert http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg. Ich würd es ja hier als Grafik unterbringen, bin mir aber nicht sicher, was das Urheberrecht dazu sagt, und halte mich daher feige zurück. Interessant sind auch die Erläuterungen dazu unter http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx?msg=2919602.

Der cbo und die Kosten

Jonathan Lewis schreibt in seinem Blog über ein ziemlich beunruhigendes Verhalten des cost based optimizers (cbo):

I’ve said for a very long time that in principle, ignoring bugs and restrictions, the optimizer will always choose the lowest cost option during its search for an execution path. It turns out that this isn’t true.

Anscheinend ergeben sich solche Fälle für bind variables und auch für “unknown value at compile time” (was z.B. im Fall von fine-grained access control eine Rolle spielen kann). Da geht mal wieder ein zentraler Merksatz dahin...

Nachtrag:hier noch ein Link auf einen Blog-Artikel, in dem Jonathan Lewis den Zusammenhang zwischen cost-Angabe und Laufzeit erläutert.

Scalar Subqueries

Eine sehr interessante Darstellung wichtiger Vorteile scalarer Subqueries findet man hier. Allerdings wird nicht ausgeführt, wann ein outer join die geeignetere Lösung für eine entsprechende Fragestellung ist, was auch ganz interessant wäre. Bei einer sehr großen Zahl von Lookups, die nicht gecacht werden können, würde ich die scalaren Subqueries inzwischen eher vermeiden (also wenn die Satzanzahl im Ergebnis, zu der die Lookups durchgeführt werden sollen, sehr groß ist); wenn mir noch eine plausiblere Bestimmung einfällt, liefere ich sie nach.

Datentyp Long

Adrian Billington hat in seinem Blog vermutlich alles Wissenswerte über den Datentyp Long aufgeschrieben und vor allem die Möglichkeiten erläutert, mit denen man Long-Werte auslesen kann.

Interval Partitions

Partitionen sind eines der wichtigsten Hilfsmittel, um große Datenmengen sinnvoll verwalten und performant abfragen zu können. In Oracle 11 gibt es nun (endlich) die Möglichkeit, Partitionen bei Bedarf automatisch vom System anlegen zu lassen. Dazu ein kleines Beispiel: Ich lege eine partitionierte Tabelle mit genau einer Spalte, einer Partition und einer Intervall-Angabe an:

SQL> r
  1  create table test_interval_partition
  2  (col1 number)
  3  partition by range (col1)
  4  interval (1000)
  5  (partition test_p1 values less than (1000)
  6* )

Tabelle wurde erstellt.

"Interval" gibt dabei an, welchen Bereich ein automatisch angelegtes Intervall umfassen soll. Bei der Füllung der Tabelle werden nun nach Bedarf neue Intervalle angelegt. Zunächst ein Wert, der in die initial angelegte Partition fällt:

SQL> insert into TEST_INTERVAL_PARTITION values (999);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1

Jetzt folgt ein Wert, der außerhalb der ersten Partition liegt:

SQL> insert into TEST_INTERVAL_PARTITION values (1000);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1
TEST_INTERVAL_PARTITION        SYS_P25         2000                        2

Um den Wert unterbringen zu können, legt das System eine neue Partition SYS_P25 an, die erwartungsgemäß an Position 2 erscheint. Nun ein deutlich höherer Wert:

SQL> insert into TEST_INTERVAL_PARTITION values (10000);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1
TEST_INTERVAL_PARTITION        SYS_P25         2000                        2
TEST_INTERVAL_PARTITION        SYS_P26         11000                       3

Das System legt eine weitere Partition SYS_P26 an Position 3 an. Jetzt ein Wert für den Raum zwischen den beiden automatisch erzeugten Partitionen:

SQL> insert into TEST_INTERVAL_PARTITION values (5000);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1
TEST_INTERVAL_PARTITION        SYS_P25         2000                        2
TEST_INTERVAL_PARTITION        SYS_P27         6000                        3
TEST_INTERVAL_PARTITION        SYS_P26         11000                       4

Die neue Partition SYS_P27 rückt an Position 3 und SYS_P26 auf Position 4.

Funktioniert also alles ziemlich genau so, wie man es erwarten würde. Interval Partitioning funktioniert übrigens nur für NUMBER und DATE Partitionsschlüssel, aber andere würde ich ohnehin nicht definieren wollen.

In Harald van Breederodes Blog finden sich übrigens noch ein paar interessante Hinweise zum Löschen von interval partitions:

• http://prutser.wordpress.com/2010/01/11/dropping-interval-partitions/ 
• http://prutser.wordpress.com/2010/07/26/dropping-interval-partitions-revisited/ 

Und im Rittman Blog schreibt Peter Scott über den Neuaufbau von lokalen Indizes für interval partitions:

• http://www.rittmanmead.com/2010/08/07/more-on-interval-partitioning/

Fragmentation

Jonathan Lewis hat in den letzten Wochen wieder allerlei interessante Blog-Einträge veröffentlicht, darunter auch eine weit gefasste Begriffserklärung für den Terminus Fragmentation. In dem Teil, der den Begriff im Kontext von Indizes erläutert, findet sich eine sehr erinnerungswürdige Kurzzusammenfassung zum Thema der Wiederverwendung von Speicherplatz in Tabellen und Indizes nach Satzlöschungen:

we have to remember that there is a difference between index deletions and table deletions that makes the subsequent behaviour different. When a transaction deletes a row from a table it can reduce the row to a stub of just a few bytes before the commit takes place and immediately make use of the space this reduction frees up in the table block; when a transaction deletes a row from an index it has to leave the entire index entry in place and flag it as deleted – it cannot immediately reuse the space, it has to wait until after the commit.

Eine Operation, die in einer Transaktion Sätze löscht und einfügt, sollte einen Index demnach deutlich wachsen lassen; dazu ein kleiner Test, in dem ich eine Tabelle mit Index anlege, alle Sätze der Tabelle lösche und anschließend identische Sätze einfüge:

--  Test1: ohne commit nach Satzlöschung
SQL> create table test
  2  as
  3  select rownum rn
  4    from dual
  5  connect by level < 1000000;

Tabelle wurde erstellt.

SQL> create index test_idx on test(rn);

Index wurde erstellt.

SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              1099

SQL> delete from test;

999999 Zeilen wurden gelöscht.

SQL> insert into test
  2  select rownum rn
  3    from dual
  4  connect by level < 1000000;

999999 Zeilen wurden erstellt.

SQL>  exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST',
                    estimate_percent=>dbms_stats.auto_sample_size)

SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              2197

Ohne Commit wächst der Index also tatsächlich auf die doppelte Größe.

--  Test2: mit commit nach Satzlöschung
 SQL> drop table test;

Tabelle wurde gelöscht.

SQL> create table test
  2  as
  3  select rownum rn
  4    from dual
  5  connect by level < 1000000;

Tabelle wurde erstellt.

SQL> create index test_idx on test(rn);

Index wurde erstellt.

 SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              1099

SQL> delete from test;

999999 Zeilen wurden gelöscht.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> insert into test
  2  select rownum rn
  3    from dual
  4  connect by level < 1000000;

999999 Zeilen wurden erstellt.

SQL> exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST',
                   estimate_percent=>dbms_stats.auto_sample_size)

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select INDEX_NAME
  2       , LEAF_BLOCKS
  3    from user_indexes
  4   where INDEX_NAME = 'TEST_IDX';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_IDX                              1099 

In diesem Fall wächst der Index nicht, da der Platz nach dem Commit freigegeben wurde. Der Herr Lewis hat also recht, was mich jetzt aber auch nicht besonders überrascht...

Für einen unique index würde das hier verwendete Beispiel übrigens eine Wiederverwendung des Speicherplatzes zeigen, da dies eine Besonderheit dieses Index-Typs ist. Bei Richard Foote findet man eine umfangreichere Erläuterung dieses Verhaltens.