Database Reference
In-Depth Information
5 organization index
6 NOCOMPRESS
7 as
8 select distinct owner, object_type, object_name
9 from all_objects
10 /
Table created.
Now we can measure the space used. We'll use the
ANALYZE INDEX VALIDATE STRUCTURE
command for this.
This command populates a dynamic performance view named
INDEX_STATS
, which will contain only one row at most
with the information from the last execution of that
ANALYZE
command:
EODA@ORA12CR1> analyze index iot_pk validate structure;
Index analyzed.
EODA@ORA12CR1> select lf_blks, br_blks, used_space,
2 opt_cmpr_count, opt_cmpr_pctsave
3 from index_stats;
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
240 1 1726727 2 37
This shows our index is currently using 240 leaf blocks (where our data is) and 1 branch block (blocks Oracle uses
to navigate the index structure) to find the leaf blocks. The space used is about 1.7MB (1,726,727 bytes). The other two
oddly named columns are trying to tell us something. The
OPT_CMPR_COUNT
(optimum compression count) column is
trying to say, “If you made this index
COMPRESS 2
, you would achieve the best compression.” The
OPT_CMPR_PCTSAVE
(optimum compression percentage saved) is telling us if we did the
COMPRESS 2
, we would save about one-third of the
storage and the index would consume just two-thirds the disk space it is now.
■
Note
the next chapter, “Indexes,” covers the index structure in more detail.
To test that theory, we'll rebuild the IOT with
COMPRESS
1 first:
EODA@ORA12CR1> alter table iot move compress 1;
Table altered.
EODA@ORA12CR1> analyze index iot_pk validate structure;
Index analyzed.
EODA@ORA12CR1> select lf_blks, br_blks, used_space,
2 opt_cmpr_count, opt_cmpr_pctsave
3 from index_stats;
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
213 1 1529506 2 28
