Index Statistiken und Column Group Statistiken (Extended Statistics)

Nach einiger Zeit widme ich mich mal wieder einem meiner Hobbies: der Zusammenfassung des aktuellen Artikels von Jonathan Lewis. Diesmal geht es darum, dass der Herr Lewis gerne den - von mir häufig wiederholten - Vorschlag macht, bei der Löschung eines aus Zugriffssicht überflüssigen Index entsprechende "extended statistics" für die fragliche column group anzulegen, da der Optimizer die distinct key Angabe eines Index zur Durchführung eines Sanity Checks bei der cardinality-Bestimmung verwenden kann - was die Beurteilung von Index-Löschungen grundsätzlich schwieriger macht als die der Neuanlage von Indizes. Außerdem hat der Autor gelegentlich darauf hingewiesen, dass column group Statistiken dann Probleme bereiten (so heißen: nicht berücksichtigt werden), wenn die Einschränkung für eine der fraglichen Spalten außerhalb des Bereichs der für diese Spalte bekannten Werte liegt (also außerhalb des Korridors von low_value und high_value in user_tab_columns). Im aktuellen Artikel bringt er diese beiden Beobachtungen zusammen und stellt die Frage: kann die Löschung eines Index und Anlage von entsprechenden column group Statistiken im Fall derartiger Prädikate zu einer Veränderung der cardinality Schätzung führen. Dazu gibt es wie üblich ein Test-Beispiel: diesmal mit einer Tabelle mit zwei Spalten mit vollständig korrelierenden Werten, was ohne den Index oder die column groups in jedem Fall zu massiven Fehleinschätzungen des Optimizers führen würde. Auf dieser Basis werden drei Queries ausgeführt:

• eine Query mit Index und zwei Prädikaten innerhalb des Ranges der bekannten Werte
• eine Query mit Index und zwei Prädikaten außerhalb des Ranges der bekannten Werte
• eine Query ohne Index - aber mit column group Statistiken - und zwei Prädikaten außerhalb des Ranges der bekannten Werte

 Die Ergebnisse zeigen für die drei Fälle:

• für die erste Query werden die distinct keys des Index als Sanity Check bei der Kalkulation der Cardinality verwendet: dadurch ergibt sich ein Full Table Scan.
• für die zweite Query werden die individuellen Selektivitäten der Spalten verwendet und jenseits des bekannten Wertebereichs setzt die übliche Abnahme der erwarteten Trefferwahrscheinlichkeit ein (linear decay): dadurch ergibt sich ein Index Zugriff.
• für die dritte Query ergibt sich wieder der Full Table Scan.

Für den Herrn Lewis ergibt sich daraus: "So my concern that substituting column groups for indexes was unfounded – the optimizer was being silly (legal disclaimer: that’s just my opinion) with indexes, and the silly (ditto) behaviour with column groups hasn’t changed anything." Mir ist diese Antwort noch nicht ganz klar, da mein Eindruck ist, dass sich column group und der Index hier nicht zu den gleichen Ergebnissen führen - aber möglicherweise entgeht mir hier etwas Entscheidendes. Möglicherweise bekomme ich dazu noch eine Erklärung, die ich dann hier ergänzen würde.

Nachtrag vom gleichen Tag: Jonathan Lewis hat's erklärt: "The second and third plans both have a cardinality of 79 (which is the only thing the note was about). Were you looking at the cost, perhaps?" Tatsächlich hatte ich auf die cost-Angaben geschaut...