Dienstag, Januar 17, 2017

Zur Semantik des USE_NL Hints

Ich erinnere mich, dass Jonathan Lewis diesen Punkt schon häufiger erwähnt hat, aber offenbar hatte er tatsächlich noch keinen Artikel dazu geschrieben, und dies jetzt nachgeholt: häufig sieht man in Oracle SQL-Queries Hints der folgenden Form:
use_nl(t1 t2)
Und es gibt wohl in der Tat viele Verwender, die davon ausgehen, dass man den Optimizer damit anweist, einen NESTED LOOPS Join zu verwenden, bei dem t1 unmittelbar mit t2 verknüpft wird, wobei t1 die driving table ist, also zuerst abgefragt wird. Das ist allerdings nicht der Fall. Tatsächlich ist der Hint nur eine Kuzform für:
use_nl(t1) use_nl(t2)
Der Hint sagt nichts darüber, in welcher Reihenfolge der Zugriff erfolgt - und wenn weitere Tabellen im Join beteiligt sind, kann es durchaus dazu kommen, dass t1 und t2 nur mittelbar miteinander verknüft werden. Um die Ausführungsreihenfolge zu bestimmen, benötigt man zumindest einen weiteren leading-Hint. Das Beispiel macht einmal mehr deutlich, dass die Verwendung von Hints deutlich komplzierter ist, als sie manchmal auszusehen scheint.

Mittwoch, Januar 11, 2017

Redundante Prädikate zur SQL Optimierung im SQL Server

Da ich nicht so oft lobende Erwähnungen auf anderen Webseiten erhalte, will ich nicht darauf verzichten, diese hier zu verlinken: mein alter Freund und ehemaliger Kollege Andrej Kuklin hat im SDX Blog einen Artikel veröffentlicht, der sich damit beschäftigt, wie man Queries im SQL Server durch die Ergänzung eigentlich redundanter Prädikate optimieren kann. In seinem Beispiel läuft eine Query mit einem Inner Join schnell, so lange über eine gegebene Variable auf ein bestimmtes Datum eingeschränkt wird, wobei die entsprechende Spalte auch in der Join-Bedingung erscheint. Andrejs (und meine) Annahme ist, dass hier - so wie bei Oracle - ein Fall von transitive closure vorliegt: die auf der einen Seite angegebe Einschränkung wird dupliziert und auch auf die zweite Menge/Tabelle angewendet:

explain plan for
SELECT
    fp.*
   ,ft.TradeID
   ,ft.IsCompleted
   ,ft.Amount
FROM
    FactPosition fp
INNER JOIN FactTrade ft ON
      ft.DateID=fp.DateId
      AND ft.PositionID=fp.PositionId
WHERE
    fp.DateId=20151028;

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |  2309 |   101K|    26   (4)| 00:00:01 |
|*  1 |  HASH JOIN                   |                 |  2309 |   101K|    26   (4)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| FACTPOSITION    |  1000 | 24000 |     9   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | FACTPOSITION_PK |  1000 |       |     5   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| FACTTRADE       |  2000 | 42000 |    16   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | FACTTRADE_PK    |  2000 |       |     8   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("FT"."DATEID"="FP"."DATEID" AND "FT"."POSITIONID"="FP"."POSITIONID")
   3 - access("FP"."DATEID"=20151028)
   5 - access("FT"."DATEID"=20151028)

Im Plan sieht man, dass die DateId-Einschränkung für beide Tabellen hergezogen werden kann (Step 3 und Step 5). Wird jedoch statt der Angabe der Variable (oder wie bei mir: eines Literals) eine Subquery verwendet ("WHERE fp.DateId=(SELECT MAX(DateId) FROM DimDate)"), dann kann der Optimizer diese Einschränkung im SQL Server und in Oracle nicht auf die zweite Tabelle anwenden und muss auf dieser einen full table scan durchführen, obwohl der Index-Zugriff deutlich schneller wäre. Um dem Optimizer die zusätzliche Information zu liefern, muss in diesem Fall die Subquery dupliziert werden (also: "WHERE fp.DateId=(SELECT MAX(DateId) FROM DimDate) AND ft.DateId=(SELECT MAX(DateId) FROM DimDate);"). Inhaltlich würde ich annehmen, dass der Optimizer eine solche Umformung auch selbständig ergänzen könnte, aber in den aktuellen Versionen von SQL Server und Oracle tut er das offenbar noch nicht. Interessant ist jedenfalls mal wieder zu sehen, wie ähnlich sich relationale Datenbanken in vielen Fällen verhalten.

Anders als meine Notiz hier zeichnet sich Andrejs Artikel übrigens durch die detaillierte Präsentation des Beispiels und der sich ergebenden SQL Server Pläne aus.

Montag, Januar 09, 2017

Maria Colgan antwortet bei AskTom

Ein vielversprechender Start ins Jahr 2017: Maria Colgan gehört jetzt neben Chris Saxon und Connor McDonald zum Team bei AskTom. Außerdem hat sie damit angefangen, in ihrem eigenen Blog zu schreiben. Da es vermutlich kaum jemanden gibt, der mehr über den Optimizer (oder ind In-Memory-Optionen) weiß als Frau Colgan, ist das eine günstige Entwicklung.

Freitag, Dezember 23, 2016

Neue Index-Features in 12.2

Richard Foote hat angekündigt, einige Artikel zu Index-Features, die in 12.2 ergänzt wurden, zu veröffentlichen. Ich werde versuchen, diese Artikel hier zusammenzufassen:
  • Oracle Database 12c Release 2: New Indexing Features – Long Identifiers (Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict): die Länge des Titels steht in Zusammenhang mit dem vorgestellten Feature - die Größenbegrenzung für Identifier wurde angehoben: statt 30 Zeichen kann ein Indexname jetzt 128 Zeichen umfassen. Klingt für mich eher bedrohlich, obwohl es natürlich Fälle gibt, in denen das alte Limit ein Problem darstellte.
  • 12.2 Index Advanced Compression “High” – Part I (High Hopes): die neue "high" Variante der in 12.1 eingeführten "index advanced compression" (bei der mich immer noch die Reihenfolge der Namensbestandteile stört) verspricht eine deutlich verbesserte Komprimierung und verwendet andere Algorithmen als die "low" Variante, die nur auf der Deduplizierung von Index-Einträgen im Leaf Block basierte. Bei der "high" Variante werden die Index-Einträge in compression units gespeichert, wobei das Verfahren offenbar dem der hybrid columnar compression ähnelt. Im Beispiel sind die Effekte recht eindrucksvoll: während normale index compression den Beispiel-Index (in dem 25% der Daten in einem bestimmten Teilbereich der Tabelle Duplikate sind)  größer macht als die nicht komprimierte Variante (2158 Blocks vs. 2684 Blocks), sorgt die "low" Variante für eine geringfügige Verkleinerung (2057 Blocks), aber die "high" Variante macht den Index deutlich kompakter (815 Blocks; wobei aufgrund eines Bugs eine Neuerfassung der Statistiken erforderlich ist, die zunächst 0 Blocks anzeigen).
  • 12.2 Index Advanced Compression “High” Part II (One Of My Turns): untersucht das Verhalten mit einem Index auf eindeutigen Werten ohne Wiederholungen. Für die normale index compression und die "low" Variante der index advanced compression ergibt sich jeweils der Fehler "ORA-25193: cannot use COMPRESS option for a single column key". Die "high" Variante hingegen reduziert die Indexgröße im Test wiederum signifikant (2088 Blocks vs. 985 Blocks). Auf Wunsch von Jonathan Lewis hat der Herr Foote das Beispiel mit größeren Id-Werten und einer randomisierten Verteilung wiederholt, wodurch sich der Effekt verkleinert, aber immer noch eine Größenreduzierung um 30% hervorruft. Jonathan Lewis hat dem Thema auch noch einen eigenen Artikel in seinem Scratchpad gewidmet.
Ich versuche, wie üblich, die in der Serie folgenden Artikel nachzutragen.

Donnerstag, Dezember 22, 2016

Attribut-Clustering und ein Jubiläum

Vor kurzem hat Connor McDonald eine Artikelserie zum Attribut-Clustering in 12c veröffentlicht, die im dritten Teil ein ziemlich seltsames Verhalten demonstriert. Ein Kommando:
alter table source_data add clustering by linear order(object_id);
führt zu einem erfolgreichen Clustering, während das Kommando:
alter table source_data clustering by linear order(object_id);
erfolgreich abläuft, aber anscheinend keinerlei Wirkung hat: das "add" hat also die entscheidende Wirkung. Randolf Geist hat in seinem Kommentar zum Artikel die naheliegende Frage nach dem Sinn der zweiten Variante gefragt, was Connor McDonald leider nicht beantworten konnte. Seltsam ist das auf jeden Fall.

Einen anderen Punkt will ich auch noch erwähnen: dies ist der tausendste Eintrag in diesem Blog und ich nehme das mal wieder zum Anlass, mich bei meinen Lesern zu bedanken. Darüber hinaus ergänze ich aus kalendarischen Gründen dann auch noch alle jahreszeitüblichen Grüße und Wünsche.