ops$tkyte%ORA10GR2> exec show_space( 'T_PK', user, 'INDEX' );
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 8
Total Blocks............................ 16
Total Bytes............................. 131,072
Total MBytes............................ 0
Unused Blocks........................... 4
Unused Bytes............................ 32,768
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 31,849
Last Used Block......................... 4

1) so looking at this Total Blocks = 16 & Unused Blocks = 4.
so my index size is about 12 blocks in size. Am i right Tom?

2) Also it is mentioned in the Oracle product documentation to use dbms_stats package to analyze tables & Index with dbms_stats.AUTO_SAMPLE_SIZE as estimate percent
instead of using ANALYZE table command right? they why you have used ANALYZE command in the above? Is it better than dbms_stats Tom?

Tom,

Reference to your above answer, below is my test case. Your comments to my understanding would be a great help.

Thanks,
Suvendu


bash-2.05$ sqlplus test/test

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 23 11:27:00 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

TEST@dev11g>drop index IDX_OWNER_T;

Index dropped.

TEST@dev11g>create index idx_owner_t on t(owner);

Index created.

TEST@dev11g>select index_name, STATUS,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED from user_indexes
2 where index_name='IDX_OWNER_T';

INDEX_NAME STATUS NUM_ROWS SAMPLE_SIZE LAST_ANAL
------------------------------ -------- ---------- ----------- ---------
IDX_OWNER_T VALID 487136 487136 23-JAN-09



1) So, the above output says, NO need to go for collecting the statistics after the creation. The creation of INDEX itself compute the statistics.
2) I understands, when NUM_ROWS and SAMPLE_SIZE points to the same values, the COMPUTE STATISTIC option being choosen instead of ESTIMATE statistics.


TEST@dev11g>l
1 select segment_name,bytes/1024/1024 from user_segments
2* where SEGMENT_NAME='IDX_OWNER_T'
TEST@dev11g>/

SEGMENT_NAME BYTES/1024/1024
-------------------- ---------------
IDX_OWNER_T 9


3) The above statement also says about the current size of the segment (index here).



TEST@dev11g>insert into t select * from t;

487136 rows created.

TEST@dev11g>insert /*+ append */ into t select * from t;

974272 rows created.

TEST@dev11g>commit;

Commit complete.

TEST@dev11g>alter index IDX_OWNER_T rebuild;

Index altered.

TEST@dev11g>select index_name, STATUS,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED from user_indexes
2 where index_name='IDX_OWNER_T';

INDEX_NAME STATUS NUM_ROWS SAMPLE_SIZE LAST_ANAL
------------------------------ -------- ---------- ----------- ---------
IDX_OWNER_T VALID 1948544 1948544 23-JAN-09


4) REBUID option is also computes the statistics.


TEST@dev11g>select segment_name,bytes/1024/1024 from user_segments
2 where SEGMENT_NAME='IDX_OWNER_T';

SEGMENT_NAME BYTES/1024/1024
-------------------- ---------------
IDX_OWNER_T 36

TEST@dev11g>analyze index IDX_OWNER_T validate structure;

Index analyzed.

TEST@dev11g>select segment_name,bytes/1024/1024 from user_segments
2 where SEGMENT_NAME='IDX_OWNER_T';

SEGMENT_NAME BYTES/1024/1024
-------------------- ---------------
IDX_OWNER_T 36

TEST@dev11g> exit;

5) ANALYZE ...VALIDATE STRUCTURE, could be ignore in this test case as it's used to report the block curroption of a segment.

------