INDEX wirkt nicht !!!

[19mick64]

Hallo zusammen !

Habe folgendes Problem:

Arbeite mit einer ORACLE-Datenbank der Version 10.2.0.1.0


Die Tabelle T_TR_XYZ hat mehrere Indices,
darunter auch den Index I_TR_3
der sich auf die Spalte TR_SID (vom Typ: NUMBER - NOT NULL) bezieht.


SQL> select INDEX_NAME, INDEX_TYPE, UNIQUENESS, COMPRESSION, INITIAL_EXTENT,
NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,BLEVEL,LEAF_BL OCKS,DISTINCT_KEYS
from all_indexes where index_name = 'I_TR_3';

INDEX_NAME INDEX_TYPE UNIQUENES COMPRESS INITIAL_EXTENT
MIN_EXTENTS MAX_EXTENTS BLEVEL LEAF_BLOCKS DISTINCT_KEYS
------------------------------------------------------------------------
I_TR_3 NORMAL NONUNIQUE DISABLED 65536
1 2147483645 2 13579 683123






Die Tabelle T_TR_XYZ hat 6788690 Sätze.
SQL> select count(*) from T_TR_XYZ ;

COUNT(*)
----------
6788690


Wobei es nur 2251504 Sätze mit gefülltem TR_SID gibt !
SQL> select count(*) from T_TR_XYZ where TR_SID > 0;

COUNT(*)
----------
2251504

Vielleicht auch interessant:
Die Inhalte des Feldes TR_SID sind entweder 8- oder 9-stellig.

SQL> select length(TR_SID), count(*) from T_TR_XYZ
group by length (TR_SID);

LENGTH(TR_SID) COUNT(*)
-------------- ----------
1 4537186
8 2078135
9 173368



Und jetzt zu meiner Frage:
Bei einer gezielten Suche nach einer TR_SID hängt es davon ab,
wie groß die Zahl ist, nach der gesucht wird, ob der Index wirkt, oder nicht ?!?!?


select * from T_TR_XYZ where TR_SID = 1234;

SELECT STATEMENT, GOAL = ALL_ROWS
TABLE ACCESS FULL RNVS T_TR_XYZ


select * from T_TR_XYZ where TR_SID = 123456789;

SELECT STATEMENT, GOAL = ALL_ROWS
TABLE ACCESS FULL RNVS T_TR_XYZ



Erst ab einer Länge von 10 Stellen beginnt der Index zu wirken !

select * from T_TR_XYZ where TR_SID = 1234567890;

SELECT STATEMENT, GOAL = ALL_ROWS
TABLE ACCESS BY INDEX ROWID RNVS T_TR_XYZ
INDEX RANGE SCAN RNVS I_TR_3




Zu guter Letzt sei noch erwähnt, dass auf einer zweiten Test-Datenbank
mit identer Tabellen- und Indexstruktur
und fast identen Tabelleninhalten, dieses Phänomen nicht auftritt !



Kann mir jemand helfen ?????

[dimitri]

Ist die Tabelle auf beiden Datenbanken analysiert? Sind die Kardinalitäten die die ein Explain Plan liefert einigermaßen korrekt?

Dim

[19mick64]

Hallo DIM,

ja, habe die Tabelle mit
"dbms_stats.gather_table_stats"
analysiert !




Ein SQL-Trace liefert mir:

************************************************** ******************************

select *
from
t_tr_xyz where tr_sid = 37373


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 34.32 35.21 146222 153955 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 34.33 35.22 146222 153955 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 34

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL T_TR_XYZ (cr=153955 pr=146222 pw=0 time=35213695 us)

************************************************** ******************************




ein Explain-Plan (über sql-Developer) liefert mir:

Description Object Name Cost Cardinality Bytes
----------------------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS 33260 2262897 359800623
TABLE ACCESS FULL T_TR_XYZ 33260 2262897 359800623




Ob die Kardinalitäten ok sind, .... ????????




Hab auch schon versucht den besagten Index zu REBUILDen .....

hab ihn auch schon deleted und neu angelegt, .....

immer dasselbe Ergebnis !!!!



Vielen Dank für deine Hilfe,
Mick

[dimitri]

Also ich hab mir das jetzt nochmal genau durchgelesen.
Wichtig ist zum einen, dass in der Tabelle eine starke ungleichverteilung auftritt, denn von den 6788690 Einträgen sind nur 2251504 Einträge > 0 und die wiederum bewegen sich in einem Bereich von 10000000 bis 999999999.

In solchen Fällen sind Histogramme sehr wichtig, da der CBO ansonsten eine Gleichverteilung annimt und der Plan ggf. unausgewogen wird.

Ich würde die Tabelle mal mit folgendem Befehl analysieren:

DBMS_STATS.GATHER_TABLE_STATS(tabname=>'T_TR_XYZ',ownname=>'derowner',cascade=>true,method_opt=>'FOR ALL COLUMNS 254');

und anschließend prüfen, ob sich etwas getan hat.

Wird der Index verwendet, wenn Du einen Hint benutzt?

SELECT /*+INDEX(a I_TR_3)*/ * FROM T_TR_XYZ a WHERE tr_sid=123;

Dim

PS: Bitte beim Posten von Code immer Formatierungstags verwenden.

[19mick64]

Hallo Dim !

Zum Einen:
Ja, der Index wirkt, wenn ich den Hint verwende !
(das möchte ich allerdings vermeiden - die Suche kommt in unserer Anwendung x-mal vor)

Zum Anderen:
Dein Tipp war goldes wert !!!

Dein Vorschlag:

DBMS_STATS.GATHER_TABLE_STATS(tabname=>'T_TR_XYZ',ownname=>'derowner',cascade=>true,method_opt=>'FOR ALL COLUMNS 254');

lieferte mir zwar noch den Fehler:

FEHLER in Zeile 1:
ORA-20000: Cannot parse for clause: FOR ALL COLUMNS 254
ORA-06512: in "SYS.DBMS_STATS", Zeile 13056
ORA-06512: in "SYS.DBMS_STATS", Zeile 13076
ORA-06512: in Zeile 1

??????

aber ich forschte in dieser Richtung weiter und probierte das Folgende:

DBMS_STATS.GATHER_TABLE_STATS(tabname=>'T_TR_XYZ',ownname=>'derowner',cascade=>true,method_opt=>'FOR ALL INDEXED COLUMNS');

und siehe da:
DER INDEX WIRKT !!!!

So ganz klar ist mir die Sache zwar noch nicht -
aber nun hab ich das Ergebnis, das ich wollte !

VIELEN HERZLICHEN DANK FÜR DEINE HILFE !

LG
Mick

[dimitri]

Oh stimmt, da hab ich ein Wort vergessen. Es hätte

FOR ALL COLUMNS SIZE 254

heißen sollen.

Dein Parameter erzeugt für indizierte Spalten (was in Deinem Fall auch reicht) Histogramme und zwar mit einer Anzahl von Buckets, also Wertebereichen, die Oracle festlegt (irgendwas zwischen 1 uns 254 Stück).

Dim