ORACLE9i: Index Skip Scans

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Hallo:

ich habe gelesen, dass ab ORACLE9i bei einem Composite Index der Index auch genommen wird, wenn im WHERE nur z.B. Column3 vorkommt (Index-Skip-Scan ).

Nach meinem Test sind mir allerdings einige Dinge unklahr.

Ich habe eine Tabelle „BDU“ mit einem Index „IDX_NOMBRE_COM“.

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION

IDX_NOMBRE_COM BDU NOMBRE 1
IDX_NOMBRE_COM BDU APELLIDO1 2
IDX_NOMBRE_COM BDU APELLIDO2 3

In meiner 1.Abfrage nimmt er meinen Index, obwohl ich nur das 3.Feld im WHERE habe (was ja neu sein soll). Selektioniert wird das Feld „NOMBRE“, das im Index enthalten ist.

SQL> explain plan for select nombre from bdu where apellido2=‚ZAPATERO‘;

TIMESTAMP OPERATION OPTIONS OBJECT_OWNER OBJECT_NAME OBJECT_TYPE OPTIMIZER ID
23/08/2005 15:51:02 SELECT STATEMENT 0
23/08/2005 15:51:02 INDEX FAST FULL SCAN BDU IDX_NOMBRE_COM NON-UNIQUE ANALYZED 1

Wenn ich aber nun ein „select *“ oder ein Feld selektioniere, das nicht im Index enthalten ist, macht er mir einen FULL TABLE SCAN.

SQL> explain plan for select * from bdu where apellido2=‚ZAPATERO‘;

TIMESTAMP OPERATION OPTIONS OBJECT_OWNER OBJECT_NAME OBJECT_TYPE OPTIMIZER ID
23/08/2005 16:14:07 SELECT STATEMENT CHOOSE 0
23/08/2005 16:14:07 TABLE ACCESS FULL BDU BDU ANALYZED 1

Warum?
Warum geht er nicht über den Index und holt sich danach die Restdaten über Rowid?
Habe ich da etwas falsch verstanden?

Die Tabelle enthält 9,8 Millionen Datensätze.

viele Grüsse
Regine

#2

Hallo,

wenn ich das richtig verstanden habe macht Oracle folgendes:

beim ersten SELECT geht Oracle NUR über den Index und lässt die Tabelle aussen vor. Es war nicht nötig, nochmals in der Tabelle nachzusehen.

beim zweiten SELECT muss Oracle in der Tabelle nachsehen. Der Optimizer hat Informationen über die Häufungsverteilung in der Tabelle und hat entschieden, dass es sich nicht lohnt, den Index zu benutzen.

Oft ist es unwirtschaftlicher einen Index zu benutzen als einen Full Table Scan durchzuführen. Indexe verwendet die DB nur dann, wenn die Rückgabemenge klein ist im Vergleich zu den Blocks. Sonst steigt die Wahrscheinlichkeit stark an, dass Du einen Block mehrfach besuchen musst.

Du kannst es ja mal ausprobieren. Gib doch mal einen Hint mit.

Du findest das übrigens SEHR ausführlich in dem Buch von Thomas Kyte: Expert Oracle One-on-one. (T. Kyte ist asktom.oracle.com)

Viele Grüße

Peter

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#3

Hallo Peter,

erstmal vielen Dank für Deine Antwort.

Der erste Fall ist mir völlig klar.
Da er ja alle Werte schon im Index hat, geht er nicht mehr auf die Tabelle. Und dass er den Index benutzt, ist ja eben die Neuerung und das habe ich auch erwartet.

Fall 2 ist mir jedoch keineswegs klar:
Bei meinem Select bekomme ich 129 Einträge zurück (von 9,8 Millionen).
Der Index ist analysiert und hat 3,5 Mill. „distinct keys“.

Meinen Hint nimmt er nicht an und macht weiter einen FULL TABLE SCAN:
EXPLAIN PLAN FOR SELECT * /*+ INDEX (BDU IDX_NOMBRE_COM) */ FROM BDU WHERE apellido2=‚ZAPATERO‘;

Erwartet hätte ich die Verwendung des Index und einen anschliessenden „TABLE ACCESS BY INDEX ROWID“.

Irgendwelche Einschränkungen habe ich bis jetzt noch nicht in den Artikeln dazu gefunden. Aber den Grund für seine Entscheidung gegen den Index habe ich auch noch nicht entdeckt.

viele Grüsse
Regine

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#4

Hallo Regine,

nur zur Vorsicht: Du hast ein

ANALYZE TABLE xxx
COMPUTE STATISTICS FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS;

NACHDEM Du die Tabelle gefüllt hast gemacht?

Vielleicht sind die Analysedaten nicht korrekt.

Was mich wundert ist, dass der Hint nicht angenommen wird.

Aber bei einer Treffermenge von

#5

Hallo Peter:

analysiert wurde mit dbms_stats.gather_schema_stats

Ich habe eben nochmal ein analyze table abgesetzt in der Form, wie Du es unten geschrieben hast.

Er machte mir immernoch ein FULL TABLE SCAN (mit und ohne hint).

Ich bin aber gerade beim Herumstöbern auf folgendes gestossen:
(AskTOM: Thema - Column order in index)

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F495…
Followup:
satisfied or not, it is NOT skip scanning. If it were – it would say „index skip scan“.
It says clearly INDEX FAST FULL SCAN PDC_BANKING_TBL_VS
It is using the index as a SKINNY version of the table. It is full scanning the index using multi-block IO. It is not even a little related to skip scans.
The optimizer has decided that this index is small enough that the advantage of multi-block IO to read a little more outweighs the single block IO used in the range scan.
…
…
Reviewer: David from Memphis, TN
My colleague was saying that in 9i, it does not matter whether the column referred in the where clause is in leading edge of the index or not because of the auto feature of „Skip Scan Index“? Is this true? If so I can create one composite index on T(col1,col2,col3) and my where clause can refer any of col1, col2, col3 and this index would be used by the optimizer? Does this feature „Skip Scan Index“ reduce number of indexes that need to be created to satisfy all of the combinations that one could use in the where clause?

Followup:
not true in general.
true in SPECIFIC CERTAIN CASES when the leading edge column value is of very low cardinality.

Demnach bedeutet es, er hat auch beim 1.Mal keinen „Index Skip Scan“ gemacht sondern einen „Fast Full SCAN“ über den Index
Und beim 2.Mal hat er garkeinen Index benutzt, da die Bedingung „low cardinality“ beim Feld „NOMBRE“ nicht erfüllt ist.

Warum nun aber beim 1.Mal ein Index herangezogen wird, das ist mir jetzt wiederum nicht mehr so richtig klar.

viele Grüsse
Regine

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#6

Hallo Regine,

in dem Buch, dass ich Dir empfohlen habe, sind einige sehr brauchbare Prozeduren, um diese Dinge herauszubekommen.

Wenn ich richtig erinnere, hast Du in deinem Index nach der 3. Spalte gesucht? oder? Dann wäre es logisch:

Fall 1: Der Index ist minimal kleiner als die Tabelle - also wird der Index benutzt. (Der Index muss aber voll durchgelesen werden wg. letzter Indexspalte)

Fall 2: Der Index ist NICHT wesentlich kleiner als die Tabelle. Deshalb ist es besser gleich die Tabelle zu scannen als den Index und die zugehörigen Blocks aus der Tabelle.

Test: versuch das Ganze mal mit der 1. und 2. Spalte. Dann müsste immer der Index genommen werden.

Viele Grüße

Peter

#7

Hallo Peter:

Deine Erklärung klingt plausible.
Ich werde nochmal einige Tests durchführen.

Die Bücher von Thomas Kyte „Effective Oracle by design“ und „Expert One-On-One“ habe ich mir letzens gebraucht zugelegt.

Das erste gefällt mir sehr sehr gut, zum zweiten bin ich bis jetzt allerdings noch nicht gekommen (leider deckt es auch nur die Version8i ab), das Kapitel über die Indizes habe ich mir aber für morgen vorgenommen.

Vielen Dank für Deine Mühe
Regine

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#8

Hallo Regine,
versuchs mal mit „dbms_stats.gather_schema_stats(…)“
Und warum ?
Weil „analyze“ alt ist und die neuen Features der Datenbank nicht unterstützt (Vorsicht dbms_stats unterstützt nicht alle Felder in den DBA_TABLES z.b. CHAIN_CNT)
Also bei mir hat das gut geklappt.

SQL>desc my_locati
Name Null? Typ

OBJ_ID NOT NULL CHAR(22)
REG_ID NOT NULL CHAR(22)
STREET_ID CHAR(22)
ADDRESS_ID CHAR(22)
COMM_ID CHAR(22)
COMMPART_ID CHAR(22)
LOC_DESCRIPTION VARCHAR2(100)

PK : OBJ_ID, REG_ID , STREET_ID

SQL> select /*+ALL_ROWS */ count(*) from my_locati;
COUNT(*)

5662351
Abgelaufen: 00:00:11.01

SQL> explain plan for
select /*+ALL_ROWS */ COMMPART_ID
from my_locati
where reg_id = ‚1004‘
order by OBJ_ID,REG_ID,STREET_ID;
EXPLAIN PLAN ausgef³hrt.
Abgelaufen: 00:00:00.00
SQL>select * from table(dbms_xplan.display());

| 0 | SELECT STATEMENT |
| 1 | SORT ORDER BY |
| 2 | TABLE ACCESS FULL | MY_LOCATI

SQL> exec dbms_stats.gather_schema_stats(OWNNAME=>‚MY‘,ESTIMATE_PERCENT=>10,OPTIONS =>‚GATHER‘,METHOD_OPT=>‚FOR ALL INDEXED COLUMNS SIZE 2‘)

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.
Abgelaufen: 00:01:02.02

SQL> explain plan for
select /*+ALL_ROWS */ COMMPART_ID
from my_locati
where reg_id = ‚1004‘
order by OBJ_ID,REG_ID,STREET_ID;
EXPLAIN PLAN ausgef³hrt.
Abgelaufen: 00:00:00.00
SQL>select * from table(dbms_xplan.display());

| 0 | SELECT STATEMENT |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_LOCATI
| 2 | INDEX FULL SCAN | MY_LOCATI_PRIME

#9

Hallo Regine!

ich habe gelesen, dass ab ORACLE9i bei einem Composite Index
der Index auch genommen wird, wenn im WHERE nur z.B. Column3
vorkommt (Index-Skip-Scan ).

Stimmt bedingt. Richtigerweise müsste es eher heissen „…der Index in ganz wenigen höchst untypischen so gut wie nie vorkommenden Faällen auch genommen wird…“ :wink:. Scherz beiseite: Dass das vorkommt kannst du getrost vergessen, wenn nicht die vorderen Spalten deines Indexes von geringer Selektivität sind. Ist das nicht der Fall brauchst du so wie früher auch zwei (oder mehr) Indices.

Im Folgenden habe ich übersichtshalber die Textabfolge im Zitat geändert:

23/08/2005 15:51:02 SELECT STATEMENT
23/08/2005 15:51:02 INDEX FAST FULL SCAN

In meiner 1.Abfrage nimmt er meinen Index, obwohl ich nur das
3.Feld im WHERE habe (was ja neu sein soll).

Das ist zwar neu (relativ), aber mit einem Index Skip Scan hat das nix zu tun (steht ja auch nicht da!). Was er tut ist kein Full Table Scan sondern ein „Full Index Scan“ - Er kommt zum Schluss, dass das lesen des gesamten (!) Indexes ihm gleichzeitig auch alle Daten bringt, die du haben willst - was sollte er also auch noch in der Tabelle suchen…

Selektioniert
wird das Feld „NOMBRE“, das im Index enthalten ist.

sic!

Wenn ich aber nun ein „select *“ oder ein Feld selektioniere,
das nicht im Index enthalten ist, macht er mir einen FULL
TABLE SCAN.

Ja, weil der „full index scan“ ihm nicht mehr hilft (da stehen eben nicht alle, sondern nur die indizierten Werte drin!)

HTH
Martin