Database Reference
In-Depth Information
As you can see, the index is in fact smaller: about 1.5MB, with fewer leaf blocks. But now it is saying, “You can
still get another 28% off,” as we didn't chop off that much yet. Let's rebuild with
COMPRESS 2
:
EODA@ORA12CR1> alter table iot move compress 2;
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
---------- ---------- ---------- -------------- ----------------
151 1 1086483 2 0
Now we are significantly reduced in size, both by the number of leaf blocks as well as overall used space, by about
1MB. If we go back to the original numbers
EODA@ORA12CR1> select (1-.37)* 1726727 from dual;
(1-.37)*1726727
---------------
1087838.01
we can see the
OPT_CMPR_PCTSAVE
was pretty much dead-on accurate. The preceding example points out an
interesting fact with IOTs. They are tables, but only in name. Their segment is truly an index segment.
I am going to defer discussion of the
PCTTHRESHOLD
option at this point, as it is related to the next two options for
IOTs:
OVERFLOW
and
INCLUDING
. If we look at the full SQL for the next two sets of tables,
T2
and
T3
, we see the following
(I've used a
DBMS_METADATA
routine to suppress the storage clauses, as they are not relevant to the example):
EODA@ORA12CR1> begin
2 dbms_metadata.set_transform_param
3 ( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
4 end;
5 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select dbms_metadata.get_ddl( 'TABLE', 'T2' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T2')
--------------------------------------------------------------------------------
CREATE TABLE "EODA"."T2"
( "X" NUMBER(*,0),
"Y" VARCHAR2(25),
"Z" DATE,
PRIMARY KEY ("X") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
TABLESPACE "USERS"
