0

There's a table MYHIST containing 42M rows.

The table has a primary key column MYHIST_ID NUMBER(22) with a valid index PK_MYHIST on it, and 50 more columns (of types VARCHAR2, NUMBER and DATE), which seems to be not interesting regarding this topic.

Suppose, that statistics for the table (and it's PK index) has been gathered 5 minutes ago.

select index_name, table_name, index_type, num_rows, distinct_keys, sample_size, status
  from user_indexes
 where table_name = 'MYHIST';

 INDEX_NAME | TABLE_NAME | INDEX_TYPE | NUM_ROWS | DISTINCT_KEYS | SAMPLE_SIZE | STATUS
----------------------------------------------------------------------------------------
  PK_MYHIST |    MYHIST  |    NORMAL  | 42297450 |    42297450   |    42297450 | VALID

Unexpectedly, the simplest query with a filter by MYHIST_ID ends with full scan. It's guaranteed that no more than 1000 rows in the table having MYHIST_ID > 4615200.

    select *
      from MYHIST t
     where MYHIST_ID > 4615200
     order by MYHIST_ID desc;

    Plan:

    --------------------------------------------------------------------------------------
    | Id  | Operation            | Name    | Rows     | Bytes       | Cost    | Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |         | 38067431 | 14655960935 | 3872743 | 12:54:33 |
    |   1 |   SORT ORDER BY      |         | 38067431 | 14655960935 | 3872743 | 12:54:33 |
    | * 2 |    TABLE ACCESS FULL | MYHIST  | 38067431 | 14655960935 |  746842 | 02:29:23 |
    --------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ------------------------------------------
    * 2 - filter("MYHIST_ID">4615200)

With an index hint the plan appears to be even worse.

    select /*+ index(t PK_MYHIST) */ *
      from MYHIST t
     where MYHIST_ID > 4615200
     order by MYHIST_ID desc;

    Plan:
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name       | Rows     | Bytes       | Cost    | Time     |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |            | 38067431 | 14655960935 | 9274232 | 30:54:51 |
    |   1 |   SORT ORDER BY                |            | 38067431 | 14655960935 | 9274232 | 30:54:51 |
    |   2 |    TABLE ACCESS BY INDEX ROWID | MYHIST     | 38067431 | 14655960935 | 6148331 | 20:29:40 |
    | * 3 |     INDEX RANGE SCAN           | PK_MYHIST  | 38067431 |             |   85356 | 00:17:05 |
    ---------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ------------------------------------------
    * 3 - access("MYHIST_ID">4615200)

Could you explain what causes the situation, when the index doesn't give an expected performance?

3

As there are more than 10% of the rows with access("MYHIST_ID">4615200), a full table scan is the fastest way to select them.

If you would try a much higher number (eg. "MYHIST_ID">46152000), Oracle's optimizer may prefer using that index.

| improve this answer | |
  • Actually, yes I've just found that the last zero in the number was lost during my manipulations. So my question is a dumb one, but you've provided a completely correct and simple reason. – diziaq Aug 13 '18 at 13:00
  • I was afraid that was what happened. PS. watch the number of rows in the plan. – Gerard H. Pille Aug 13 '18 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.