Re: help:what is the difference btwn two analyze index?

From: Yong Huang <yong321_at_yahoo.com>
Date: 22 Jun 2001 13:20:12 -0700
Message-ID: <b3cb12d6.0106221220.36166654@posting.google.com>

Very interesting remark. I just did a test. A freshly created database 8.1.7 on NT. Created a table test as select * from dba_objects. Created an index on test(owner) nologging (note: I have 14 users in the database). Analyzed it compute statistics and analyzed it validate structure. Then altered the index rebuild compress. Did the two analyzes again. Here's what I find:

Change in user_indexes due to compression:

compression: DISABLED -> ENABLED
prefix_length: NULL -> 1
leaf_blocks: 57 -> 38 (i.e. 2/3 of 57)

distinct_keys: 14 -> 24632 (Why?!)
avg_leaf_blocks_per_key: 4 -> 1
avg_data_blocks_per_key: 24 -> 1

Change in index_stats due to compression:

blocks: 64 -> 48

br_rows_length: 1036 -> 680
lf_blks: 57 -> 38
lf_rows_length:  405254 -> 270952
lf_blk_length: 8000 -> 7996
br_rows: 56 -> 37

btree_space: 464032 -> 311880
used_space: 406290 -> 271632
pre_rows: 0 -> 51
pre_rows_length: 0 -> 620

Everything else stays the same. index_stats.distinct_keys stays at 14. Exactly what Jonathan suspected. So the conclusion is trust index_stats when you want to know the number of distinct keys.

Yong Huang
yong321_at_yahoo.com

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<993156119.20183.2.nnrp-13.9e984b29_at_news.demon.co.uk>...
> One possible reason for the discrepancy in rows
> could be the result of having a compressed index.

> u518615722_at_spawnkill.ip-mobilphone.net wrote in message ...
> >Could somebody tell me what is the difference between
> >
> >analyze index name compute statistics
> >then select against user_indexes
> > BLEVEL INDEX_NAME DISTINCT_KEYS
> >---------- ------------------------------ -------------
> > 2 HISTORICAL_PK 18473672
> >
> >analyze index name validate structure
> >then select against index_stats
> >
> > HEIGHT BLOCKS NAME DISTINCT_KEYS
> >---------- ---------- ------------------------------ -------------
> > 3 45120 HISTORICAL_PK 11692267
> >
> >Which one gives the more accurate results?
Received on Fri Jun 22 2001 - 15:20:12 CDT