Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Unanswered: Why doesn't Oracle use an index on column a, b in queries only b is in the criteria

    Hi,

    I am trying to understand the optimizer better.

    I have created a table with 4 columns:
    CREATE TABLE T_10P6(ID NUMBER NOT NULL, NAME VARCHAR(250) NOT NULL, A1 NUMBER NOT NULL, A2 NUMBER NOT NULL);
    CREATE INDEX T_10P6_IX ON T_10P6(A1,A2);

    Table has 1M records. A1 and A2 has values between 1 and 100.

    When performing the following select query
    SELECT * FROM T_10P6 WHERE A1=1 AND A2=2
    the optimizer chooses the t_10p6_ix index.

    But when performing the following select query (1 or 2)
    1) SELECT * FROM T_10P6 WHERE A2=2
    2) SELECT * FROM T_10P6 WHERE A1 IS NOT NULL AND A2=2
    the optimizer prefers FTS over the t_10p6_ix index.

    I tried the following query:
    SELECT * FROM T_10P6 WHERE A1 BETWEEN -10000 AND 1000 AND A2=2
    And the optimizer used the index.

    My question is that:
    Why does Oracle ignore the index when it is clear that using it will get much better results instead of doing FTS?

    p.s.
    The test was performed on both 8.1.74 and 9.2.0.4 with similar results mentioned above.





    Thanks,

    Tal (otal@mercury.co.il).

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    did you analyze the table and the indexes after you loaded the data and created the indexes?

    1. create table
    2. load data
    3. create indexes
    4. analyze table, analyze indexes
    5. run query
    PHP Code:
    12:07:48 platform@kod1create table test (colA varchar2(30), colB varchar2(30));

    Table created.

    Elapsed00:00:00.00
    12
    :08:32 platform@kod1insert into test select object_idobject_name from dba_objects;

    10058 rows created.

    Elapsed00:00:06.04
    12
    :09:10 platform@kod1set autotrace traceonly explain;
    12:11:46 platform@kod1SELECT FROM test WHERE cola ='52527' AND colb 'ENERGY_MARKET';
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   TABLE ACCESS 
    (FULLOF 'TEST'

    Elapsed00:00:00.00
    12
    :12:28 platform@kod1create index test_ind  on test (colacolbnologging compress;

    Index created.

    Elapsed00:00:01.01
    12
    :12:44 platform@kod1analyze table test compute statistics;

    Table analyzed.

    Elapsed00:00:00.07
    12
    :13:04 platform@kod1analyze table test compute statistics for all indexes;

    Table analyzed.

    Elapsed00:00:00.05
    12
    :13:11 platform@kod1SELECT FROM test WHERE cola ='52527' AND colb 'ENERGY_MARKET';
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=22)
       
    1    0   INDEX (RANGE SCANOF 'TEST_IND' (NON-UNIQUE) (Cost=1 Card
              
    =1 Bytes=22
    any questions?

    (I would like to thank Tom Kite for encouraging me to show examples of concepts)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Why doesn't Oracle use an index on column a, b in queries only b is in the criteria

    Why is it clear that using the index would be better? Without any restriction on the first column in the index, Oracle would have to scan the whole index looking for matching A2 values, and then read the relevant record from the table. It is probably quicker to just scan the table.

    There is a feature called "index skip scan" which rewrites the query like this:

    select * from t where a1=1 and a2=2
    union all
    select * from t where a1=2 and a2=2
    ...
    union all
    select * from t where a1=100 and a2=2

    ...but only of the number of distinct A1 values is small, which it isn't here. Normal behaviour is only to use an index when the query puts conditions on columns in the "leading edge" of the index.

  4. #4
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73
    Originally posted by The_Duck
    did you analyze the table and the indexes after you loaded the data and created the indexes?

    1. create table
    2. load data
    3. create indexes
    4. analyze table, analyze indexes
    5. run query
    PHP Code:
    12:07:48 platform@kod1create table test (colA varchar2(30), colB varchar2(30));

    Table created.

    Elapsed00:00:00.00
    12
    :08:32 platform@kod1insert into test select object_idobject_name from dba_objects;

    10058 rows created.

    Elapsed00:00:06.04
    12
    :09:10 platform@kod1set autotrace traceonly explain;
    12:11:46 platform@kod1SELECT FROM test WHERE cola ='52527' AND colb 'ENERGY_MARKET';
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   TABLE ACCESS 
    (FULLOF 'TEST'

    Elapsed00:00:00.00
    12
    :12:28 platform@kod1create index test_ind  on test (colacolbnologging compress;

    Index created.

    Elapsed00:00:01.01
    12
    :12:44 platform@kod1analyze table test compute statistics;

    Table analyzed.

    Elapsed00:00:00.07
    12
    :13:04 platform@kod1analyze table test compute statistics for all indexes;

    Table analyzed.

    Elapsed00:00:00.05
    12
    :13:11 platform@kod1SELECT FROM test WHERE cola ='52527' AND colb 'ENERGY_MARKET';
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=22)
       
    1    0   INDEX (RANGE SCANOF 'TEST_IND' (NON-UNIQUE) (Cost=1 Card
              
    =1 Bytes=22
    any questions?

    (I would like to thank Tom Kite for encouraging me to show examples of concepts)
    Thanks for trying but you did not answer the question.

    Tal.

  5. #5
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Re: Why doesn't Oracle use an index on column a, b in queries only b is in the criteria

    Originally posted by andrewst
    Why is it clear that using the index would be better? Without any restriction on the first column in the index, Oracle would have to scan the whole index looking for matching A2 values, and then read the relevant record from the table. It is probably quicker to just scan the table.

    There is a feature called "index skip scan" which rewrites the query like this:

    select * from t where a1=1 and a2=2
    union all
    select * from t where a1=2 and a2=2
    ...
    union all
    select * from t where a1=100 and a2=2

    ...but only of the number of distinct A1 values is small, which it isn't here. Normal behaviour is only to use an index when the query puts conditions on columns in the "leading edge" of the index.
    You say: "It is probably quicker to just scan the table".

    Well, I thought the same as you, but, in my case I have an index that has 2500 db blocks and table that has 37000 db blocks.

    Can you honestly say that doing FTS will be much faster than scanning the whole index and looking for a single key of a2 from 100 that exist (I mentioned this in the first note)?

    Thanks,

    Tal.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    I think I read your original post incorrectly.
    Sorry.

    The WHY is that for the first query "WHERE A2=2" your where clause is based on the second column of the index and not the first so the index is meaningless for this clause. The index won't be used by the optimizer if you are not using the proper order of the columns that are indexed.

    the second query has to do with the NULL search criteria I think. Matching on NULLs causes all kinds of weird things to happen.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    If Oracle believes it will have to scan the entire index - only to return all table rows, it will read 37000 + 2500 blocks. Yes, the FTS is quicker. Not only is it 2500 blocks fewer, it is a simpler and faster operation.

    A very over simplified explanation.....

    Using the index, read one index leaf (not a block), locate and read appropriate table block. Repeat for all rows in table + all deleted rows since index was built. Result = multiple disk activity (scattered reads) per table row. (yes, it's a simplification - a lot of the scattered reads are resolved in memory via OS disk-prefetch, caches in SGA etc).

    Alternatively, scan entire table. Disk interleaving, good tablespace management etc should result in (what could be called) streaming, ie, disk i/o at a far higher rate.

    I think both Tony and The_Duck and have correctly suggested why the entire index will be scanned.

    Whilst there are always exceptions and it won't get it right every time, Oracle usually takes a sensible route.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73
    1st, Thanks for all replies.

    I have asked a dear frend and he explained that only when the first column of the index (that is supposed to be skipped) has low rate of distinct values the index skip happens.

    I guess every one of you had some truthe in his message

    I tried it with two columns a1 with 2 values, a2 with 10000 values and index on a1, a2, then queried using only a2 in the where and it worked, the index was used.

    I totally agree Oracle chooses the right path, I gust try to understand why

    Thanks,

    Tal.

Posting Permissions

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