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?

Are you sure that index is on single column? – Lukasz Szozda Jun 13 '19 at 20:32 — Lukasz Szozda, Jun 13 '19 at 20:32

Barbaros Özhan · Accepted Answer · 2019-06-13T21:03:06.257

3

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.

edited Jun 13 '19 at 21:03

answered Jun 13 '19 at 20:33

Barbaros Özhan

59,113
10
31
55

For real? That would explain it :) Thank you! – AlmostSQLxprt Jun 13 '19 at 20:43
@AlmostSQLxprt you're welcome :) refresh your statistics and try to query them again if you have privilege to run analyze operations. – Barbaros Özhan Jun 13 '19 at 20:46
1

@AlmostSQLxprt In addition to this single issue, you might want to ensure that your system is following best practices for statistics gathering. By default, Oracle automatically gathers statistics every night on tables that have significantly changed. Check `select * from dba_optstat_operations where operation = 'gather_database_stats (auto)' order by start_time desc;`, and if statistics weren't gathered last night talk to your DBA. – Jon Heller Jun 14 '19 at 01:32

score 0 · Answer 2 · answered Jun 13 '19 at 20:50

Check also here: https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-2017 "Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index." - so on top of data pointers you also have many cross-level leafs.

score 0 · Answer 3 · answered Jun 13 '19 at 21:17

Index statistics can be gathered using the DBMS_STATS.GATHER_INDEX_STATS() procedure. The example below shows how the DISTINCT_KEYS value is updated after rows are inserted and index statistics are gathered. Note that Oracle samples a percentage of the data in the index by default, so the DISTINCT_KEYS may not reflect the actual number of distinct keys in the index. See the estimate_percent parameter in the link below.

https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68575

SQL> CREATE TABLE foo AS SELECT LEVEL id FROM dual CONNECT BY LEVEL <= 5;

Table FOO created.

SQL> CREATE INDEX foo_idx ON foo(id);

Index FOO_IDX created.

SQL> SELECT * FROM foo;

        ID
----------
         1
         2
         3
         4
         5

SQL> SELECT distinct_keys FROM all_indexes WHERE table_name = 'FOO';

DISTINCT_KEYS
-------------
            5

SQL> INSERT INTO foo VALUES(6);

1 row inserted.

SQL> INSERT INTO foo VALUES(7);

1 row inserted.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> SELECT distinct_keys FROM all_indexes WHERE table_name = 'FOO';

DISTINCT_KEYS
-------------
            5

-- Here's where we gather the statistics
SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS('MYSCHEMA', 'FOO_IDX');

PL/SQL procedure successfully completed.

SQL> SELECT distinct_keys FROM all_indexes WHERE table_name = 'FOO';

DISTINCT_KEYS
-------------
            7

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

3 Answers3