Oracle

Oracle

Posts 1-7 of 7
  • Markus Wagner
    Markus Wagner
    The company name is only visible to registered members.
    Index Problem mit Sub-Select in IN-Parameter
    Hallo,

    Ich habe folgendes Problem. Bei Verwendung eines Sub-Selects, der einzelne Werte liefert (197321,197322,197323, ...),
    verwendet Oracle einen Full Table Scan der Haupttabelle (tab2). Wenn alle diese Werte, welche der Sub-Select liefert,
    fix angegegeben werden, wird ein Index Range Scan für die Haupttabelle (tab2) angewandt.

    Was ist hier der Unterschied und was kann man verbessern?

    DB: Oracle 10g Enterprise Edition Release 10.2.0.1.0 unter Solaris 10

    Vielen Dank für jede Hilfe.

    Markus Wagner



    Anbei die angelegten Indizes

    CREATE INDEX i_tab1_index ON tab1 ( datum, flag_inst );
    CREATE INDEX i_tab2_index ON tab2 ( tab2Idx, kontro );


    Select mit Sub-Select in IN-Statement
    -------------------------------------

    SELECT count(epidx) as rowAnz
    FROM tab2
    WHERE tab2Idx IN ( SELECT tab1IDX FROM tab1
    WHERE datum BETWEEN '20120101' AND '20120117'
    AND flag_inst = '1' )
    AND kontro = '9876521'


    Explain Plan:

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------
    Plan hash value: 1100884518

    --------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 21 | 364 (5)| 00:00:05 |
    | 1 | SORT AGGREGATE | | 1 | 21 | | |
    |* 2 | HASH JOIN | | 6418 | 131K| 364 (5)| 00:00:05 |
    |* 3 | INDEX FAST FULL SCAN | I_TAB2_INDEX | 6417 | 32085 | 342 (5)| 00:00:05 |
    | 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | 6853 | 107K| 20 (0)| 00:00:01 |
    |* 5 | INDEX SKIP SCAN | I_TAB1_INDEX | 259 | | 4 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------

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

    2 - access("EPIDX"="TAB1IDX")
    3 - filter("KONTRO"='9876521')
    5 - access("DATUM">='20120101' AND "FLAG_INST"='1' AND "DATUM"<='20120117')
    filter("FLAG_INST"='1')




    Select mit fixen Werten in IN-Statement
    ---------------------------------------

    SELECT count(epidx) as rowAnz
    FROM tab2
    WHERE tab2Idx IN ( 197221, 197243, 197246, 197248 )
    AND kontro = '9876521'


    Explain Plan

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------
    Plan hash value: 2580069467

    --------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | 5 | | |
    | 2 | INLIST ITERATOR | | | | | |
    |* 3 | INDEX RANGE SCAN| I_TAB2_INDEX | 1 | 5 | 6 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------

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

    3 - access(("EPIDX"=197221 OR "EPIDX"=197243 OR "EPIDX"=197246 OR
    "EPIDX"=197248) AND "KONTRO"='9876521')
    • 20 Jan 2012, 09:04 am
  • Post visible to registered members
  • Georg Feinermann
    Georg Feinermann
    The company name is only visible to registered members.
    Re^2: Index Problem mit Sub-Select in IN-Parameter
    Hallo Herr Wagner,

    die Antwort von Karl würde ich gerne noch ergänzen:

    Sollte der Optimizer mit seiner Vermutung, dass ca. 6K Zeilen gelesen werden grob falsch liegen, dann liegt es mit großer Sicherheit an falschen Statistiken. Berechnen Sie in diesem Fall die Statistiken neu.

    Grundsätzlich gilt: Ein full table scan ist kein Teufelszeug. Es kann effizienter sein, als ein Index Zugriff. Entscheidend ist die Anzahl der IO Zugriffe, die die Datenbank durchführen muss. Die IOs sind besonders langsam und bei einem Plan mit Index muss die Datenbank zuerst auf den Index zugreifen (mindestest 1 IO, je nach Indexstruktur) und dann auf die Daten - noch ein IO. Ein full table scan kann weniger IOs bedeuten und ist daher oftmals schneller.

    Aber der Optimizer kann nur dann einen guten Plan erstellen, wenn die Grundlage - die Statistiken - wirklich stimmen.

    Viele Grüße
    Georg Feinermann
    • 20 Jan 2012, 11:37 am
  • Post visible to registered members
  • Jörg Sobottka
    Jörg Sobottka
    The company name is only visible to registered members.
    Re: Index Problem mit Sub-Select in IN-Parameter
    Hallo Herr Wagner,

    Markus Wagner schrieb:
    Ich habe folgendes Problem. Bei Verwendung eines Sub-Selects, der einzelne Werte liefert (197321,197322,197323, ...),
    verwendet Oracle einen Full Table Scan der Haupttabelle (tab2). Wenn alle diese Werte, welche der Sub-Select liefert,
    fix angegegeben werden, wird ein Index Range Scan für die Haupttabelle (tab2) angewandt.
     
    Was ist hier der Unterschied und was kann man verbessern?
    Anbei die angelegten Indizes
     
    CREATE INDEX i_tab1_index ON tab1 ( datum, flag_inst );
    CREATE INDEX i_tab2_index ON tab2 ( tab2Idx, kontro );
          Wieso nehmen Sie denn tab1idx nicht in den Index i_tab1_index als letzte Spalte auf? So ist der Optimizer gezwungen, neben dem Zugriff auf den Index auch immer noch einen Zugriff auf die Tabelle tab1 zu machen.

    SELECT count(epidx) as rowAnz
    FROM tab2
    WHERE tab2Idx IN ( SELECT tab1IDX FROM tab1
    WHERE datum BETWEEN '20120101' AND '20120117'
    AND flag_inst = '1' )
    AND kontro = '9876521'
    -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 364 (5)| 00:00:05 | | 1 | SORT AGGREGATE | | 1 | 21 | | | |* 2 | HASH JOIN | | 6418 | 131K| 364 (5)| 00:00:05 | |* 3 | INDEX FAST FULL SCAN | I_TAB2_INDEX | 6417 | 32085 | 342 (5)| 00:00:05 | | 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | 6853 | 107K| 20 (0)| 00:00:01 | |* 5 | INDEX SKIP SCAN | I_TAB1_INDEX | 259 | | 4 (0)| 00:00:01 |
    Ausserdem macht der Optimizer keinen Full Table Scan, sondern einen Index Fast Full Scan, das ist schon mal ein Unterschied. Sollte die Aufnahme der zusätzlichen Spalte nicht alleine schon eine Änderung des Execution Plans bewirken, würde ich persönlich zuerst einmal einen NO_USE_HASH-Hint ausprobieren.

    viele Grüsse
    Jörg Sobottka
    Delta Energy Solution AG - ISV Partner of the Year 2011 (Oracle Schweiz GmbH)
    • 20 Jan 2012, 11:44 am
  • Randolf Geist
    Randolf Geist
    The company name is only visible to registered members.
    Re: Index Problem mit Sub-Select in IN-Parameter
    Hallo Herr Wagner,

    Markus Wagner schrieb:
    Hallo,
     
    Ich habe folgendes Problem. Bei Verwendung eines Sub-Selects, der einzelne Werte liefert (197321,197322,197323, ...),
    verwendet Oracle einen Full Table Scan der Haupttabelle (tab2). Wenn alle diese Werte, welche der Sub-Select liefert,
    fix angegegeben werden, wird ein Index Range Scan für die Haupttabelle (tab2) angewandt.
     
    Was ist hier der Unterschied und was kann man verbessern?
    Sie haben schon einige gute Hinweise hier bekommen.

    Der Cost-Based Optimizer von Oracle erstellt im Rahmen der gegebenen Zugriffsmöglichkeiten grundsätzlich sinnvolle Pläne, wenn er die Mengenabschätzungen richtig hinbekommt.

    In Ihrem Fall scheint dies aber nicht so zu sein - wenn ich Sie richtig verstehe, kommen aus der Beispiel-Subquery nur vier Werte zurück, der Optimizer schätzt aber eben über 6000.

    Bei einer solchen großen relativen Differenz (4 vs. 6000) ist es wahrscheinlich, dass die favorisierten Pläne des Optimizers nicht optimal sind.

    Sie sollten von daher sich die Frage stellen, warum der Optimizer so daneben liegt. Natürlich sollten die Statistiken, auf denen die Kosten-Abschätzung beruht, möglichst repräsentativ für ihre Daten und Abfragen sein, aber das geht leider nicht immer, selbst wenn sie absolut aktuell sind.

    Ein möglicher Grund für Ihr Problem könnte sein, dass ein falscher Datentyp für DATUM verwendet wird - das ist ein relativ einfaches Mittel, um den Optimizer durcheinander zu bringen.

    Machen Sie folgenden Test in SQL*Plus:

    SET AUTOTRACE ON

    SELECT COUNT(*) as CNT FROM tab1
    WHERE datum BETWEEN '20120101' AND '20120117'
    AND flag_inst = '1'
    ;

    und vergleichen den tatsächlichen Count mit der Abschätzung im Plan.

    Sollte dies ähnlich falsch sein, wie in dem von Ihnen gezeigten Ausführungsplan, dann liegt hier das Hauptproblem.

    DATUM scheint als (VAR)CHAR(2) oder ähnliches deklariert zu sein, stellt aber ein Datum dar. Das weiss der Optimizer aber nicht und wendet String-Vergleichsarithmetik für die Bedingung BETWEEN '20120101' AND '20120117' an. Dies führt ohne weitere Hilfe zu einer Fehlabschätzung der Datenmenge.

    Falls dies der Fall ist, gibt es einige Optionen, die Sie probieren können, um dem Optimizer zu helfen - die Verwendung des korrekten Datentyps DATE wäre natürlich am besten, ist aber nicht so einfach (nachträglich) zu implementieren, je nach Situation.

    Mit freundlichen Grüßen,
    Randolf Geist

    Oracle ACE Director
    OakTable Network Member

    http://oracle-randolf.blogspot.com
    • 20 Jan 2012, 3:21 pm
  • Georg Freiberger
    Georg Freiberger    Premium Member
    The company name is only visible to registered members.
    Re: Index Problem mit Sub-Select in IN-Parameter
    Guten Morgen Markus!
    Vielleicht ist es am einfachsten wenn du beim select angibst, welchen index oracle verwenden soll:

    SELECT count(epidx) as rowAnz
    FROM tab2
    WHERE tab2Idx IN ( SELECT /*+ INDEX(i_tab2_index) */ tab1IDX FROM tab1
    WHERE datum BETWEEN '20120101' AND '20120117'
    AND flag_inst = '1' )
    AND kontro = '9876521'

    Dann solte eigentlich der Optimizer "overruled" werden und oracle sollte diesen index verwenden...
    Falls "datum" immer einen Datumswert oder immer eine Zahl beinhaltet, ist auch die Änderung des Datentyps sinnvoll.

    Viele Grüße,
    Georg.
    • 26 Jan 2012, 08:32 am