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‘;
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]
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]
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]
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.
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]
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
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…“ . 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!)
. 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.