Re: Very unstable execution plan

I have attached both plans as retrieved using

    SELECT *
    FROM table(dbms_xplan.display_cursor('202urtp1qwkpa', null, 'BYTES COST NOTE ROWS ALLSTATS PREDICATE '));

after running the statement twice with and without statistics on the table.

I have also attached the full output of EXPLAIN PLAN for both situations.

This is the index definition:

CREATE INDEX IDX_TMP_VU_SPARTE
    ON TMP_VU_SPARTE (VERTRAG_EXT_REF ASC, ERSETZT_AM ASC, ERSTELLT_AM ASC, DEKLARATION_GUELTIG_VON ASC, DEKLARATION_GUELTIG_BIS ASC); The table itself is defined like this:

CREATE TABLE TMP_VU_SPARTE
(

     VERTRAG_EXT_REF          VARCHAR2(255 Byte),
     VU_SPARTE_SCHLUESSEL     VARCHAR2(100 Byte),
     VU_SPARTE_NAME           VARCHAR2(255 Byte),
     DEKLARATION_GUELTIG_VON  DATE                 NOT NULL,
     DEKLARATION_GUELTIG_BIS  DATE                 NOT NULL,
     ERSTELLT_AM              TIMESTAMP(6),
     ERSETZT_AM               TIMESTAMP(6),
     DEKLARATION_ID           NUMBER(18)           NOT NULL,
     DEKLARATION_EXT_REF      VARCHAR2(255 Byte)   NOT NULL

The actual join in the view is something like this:

SELECT ....
FROM tmp_vu_sparte vu

    INNER JOIN tmp_vu_sparte vu2

            ON vu.vertrag_ext_ref = vu2.vertrag_ext_ref
           AND vu2.deklaration_gueltig_von <= vu.deklaration_gueltig_von
           AND vu2.deklaration_gueltig_bis > vu.deklaration_gueltig_von
           AND vu2.erstellt_am < vu.ersetzt_am
           AND vu2.ersetzt_am > vu.erstellt_am

Note that this part on itself will *always* use a FTS on the table (which is understandable) - the index is only used when the view is accessed as part of the co-related update.

Regards
Thomas

--
http://www.freelists.org/webpage/oracle-l