Oracle PK index extremely low performance 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 &gt; 4615200.

    select *
      from MYHIST t
     where MYHIST_ID &gt; 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"&gt;4615200)


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

    select /*+ index(t PK_MYHIST) */ *
      from MYHIST t
     where MYHIST_ID &gt; 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"&gt;4615200)


Could you explain what causes the situation, when the index doesn't give an expected performance?
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.