Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002

    Unanswered: select distinct is using the wrong index


    I have an indexes question?

    I have a table with 1M rows, the table structure is as follows:
    create table X(aTime DATE , a1 NUMBER(28,0), a2 NUMBER(28,0), a3 NUMBER(28,0), .... , a20 NUMBER(28,0) );

    I have also 2 indexes:
    - IDX1 is defined on (aTime, a1, a2, a3).
    - IDX2 is defined on (a1, a2, a3).

    I run the following query: "SELECT DISTINCT A1, A2, A3 from X;" and see that the optimizer chooses to do a FTS (full table scan) though I was expecting him to use IDX2 because it looks like a "covering" indexes giving all the answers to the query without fetching data rows.

    When I try to force him to use indexes, using the following hint "/*+INDEX(X IDX2)*/" the optimizer chooses IDX1.

    When I use same hint after I dropped IDX1 the optimizer still prefers to make FTS .

    Some general info:
    - a1, a2 and a3 have about 500 distinct values each).
    - db version
    - all tables and indexes are analyzed.

    I have two questions:
    Why does the optimizer prefers IDX1 (aTime, a1, a2, a3) over IDX2 (a1, a2, a3) when the query specifically requests distinct values of (aTime, a1, a2, a3), isn't there a shortcut the optimizer should use here?
    When I request the optimizer to use specific index (using hints), shouldn't it use the index I request regardless of his calculations? If not, is there any way to change this behavior?

    Tal Olier

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    Rhetorical questions which might help you to answer your own questions
    Can any of the values of a1, a2, and/or a3 ever be NULL?
    Can an index contain a NULL value?
    Oracle is trying to save you from yourself & your faulty reasoning.
    Be thankful Oracle ignores the nonsensical hints.

  3. #3
    Join Date
    Apr 2003
    Minneapolis, MN
    Is IDX a Clustered or Unclustered Index?

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts