Trivial question about max number of distinct values in a B-tree index

Question

I am trying to learn about indexing. I looked at the actual indexes used in the database at work.

I looked in to two random indexes:

SELECT
INDEX_NAME, INDEX_TYPE, LEAF_BLOCKS, DISTINCT_KEYS 
FROM ALL_INDEXES
WHERE TABLE_NAME = 'TRANS'
AND INDEX_NAME IN ('TRANS_PK','TRANS_ORD_NO')

This gives:

INDEX_NAME  | INDEX_TYPE | LEAF_BLOCKS | DISTINCT_KEYS |
TRANS_PK    | NORMAL     | 13981       |    3718619    | 
TRANS_ORD_NO| NORMAL     | 17052       |    43904      |

This is what makes no sense to me; shouldn't distinct(column_name) from the actual table yield the same number? It doesn't!

SELECT COUNT(DISTINCT ORD_NO) FROM trans

..gives 20273

TRANS_PK is an index for a column called NO.

SELECT COUNT(distinct NO) FROM trans

... gives 4 328 622

What am I not getting here? The select distinct should result in the same number as the "distinct keys" - column in the ALL_INDEXES- table?

Answer 1

Dictionary views ( including ALL_INDEXES ) don't have real-time data, but the new statistics values are refreshed during the analyze time, and become stale as the time passes.