Re: Analyze Index and INDEX

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 21 Jul 1999 04:37:49 GMT
Message-ID: <7n3iqt$4g4$6@news.seed.net.tw>

Dipen Kotecha <dkotecha_at_ford.invalid> wrote in message news:3793380B.F1397C07_at_ford.invalid...
> We have a field called CHANGED on a table, and it has only two values,
> '1' or NULL. When I use :
>
> SELECT index_name, distinct keys from user_indexes
> WHERE index_name = 'CHANGED_IDX';
>
> I get a value of 2 for the distinct keys, which is correct.

If you use a bitmap index, that is correct. If you use a B-tree index, you should get a value of 1 for the distinct_keys column, since the null values are not stored in B-tree indexes.

> But if I use ANALYZE INDEX CHANGED_IDX VALIDATE STRUCTURE;
> and then select distinct_keys from INDEX_STATS I get 934 for the same
> index!!
> Does the distinct_keys in INDEX_STATS refer to something else or am I
> doing something wrong?

It's reasonable, cause of Oracle's index mechanism. If a index key value is changed or removed from the index, the space of the old key value is still occupied. For this reason, if the key values of a index change frequently, the index should be rebuild periodly.

The distinct_keys in USER_INDEXES counts the current key values. The distinct_keys in INDEX_STATS counts the all key values even been, the count may be more impalpable for a bitmap index.

The following script shows it:

SQL> create table test (c varchar2(1));

Table created.

SQL> begin
  2 for i in 0..9 loop
  3 insert into test values (to_char(i));   4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> create index ind_test on test (c);

Index created.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> analyze index ind_test validate structure;

Index analyzed.

SQL> select index_name, distinct_keys from user_indexes where index_name='IND_TEST';

INDEX_NAME                     DISTINCT_KEYS
------------------------------ -------------
IND_TEST                                  10

SQL> select name, distinct_keys from index_stats where name='IND_TEST';

NAME                           DISTINCT_KEYS
------------------------------ -------------
IND_TEST                                  10

SQL>
SQL> update test set c='0';

10 rows updated.