Number of distinct keys change after Index rebuild

[Former Member]

Hi Community,

today I observed somthing strange when I rebuild an Index. Before I started the Index Rebuild I checked in DB02 the Index details where a number of "Distinct keys" are reported.

Next I started Index Rebuild using DB02 - once finished I checked again the details of the related Index and I found the number of Distinct key plus "some" additional. The related was Index COEJ~1 which had before rebuild 404.000 distinct keys .... and after rebuild 46.000.000 - That sounds strange to me because I was using the System exclusivly - Up to now I thought distinct means distinct - but now ?

Does anybody have a clue what DB02 index details means with "distinct key" and of course why they change that dramatically after index rebuild?

Kind regards

Alex

[Former Member]

Alexander,

The only authoritative source is the Oracle catalog. Next time you rebuild an index, check the statistics before and after:

select last_analyzed, distinct_keys from dba_indexes
where index_name = '<name>';

The only explanation I can imagine is, as the previous respondent pointed out, that the statistics were very old ("old" in the sense that the table had undergine drastic changes since the stats were last updated, e.g. a large data load).

Regards,

Mark

[Former Member]

Hello Mark, hello Volker,

thanks for your responses - you are right with your assumption that my stats were very old

... and its a shame that I did not recognize this fact by myself

Kind regards

Alex

[volker_borowski2]

Hi,

that value is a database statistics value and might before have been very old.

The stats may have been recreated / recalculated after the new creation.

Volker