Database Reference
In-Depth Information
blocks on freelist = 520
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select leaf_blocks from user_indexes where index_name = 'BIG_TABLE_PK';
LEAF_BLOCKS
-----------
523
As we can see, over half of the index is on the
FREELIST
now (520 blocks) and there are only 523 leaf blocks. If we
add 523 and 520, we get the original 1043. This means the blocks are totally empty and ready to be reused (blocks on
the
FREELIST
for an index must be empty, unlike blocks on the
FREELIST
for a heap organized table).
This demonstration highlights two points:
•
Space is reused on index blocks as soon as a row comes along that can reuse it.
•
When an index block is emptied, it can be taken out of the index structure and may be reused
later. This is probably the genesis of this myth in the first place: blocks are not visible as having
free space on them in an index structure as they are in a table. In a table, you can see blocks on
the
FREELIST
, even if they have data on them. In an index, you will only see completely empty
blocks on the
FREELIST
; blocks that have at least one index entry (and remaining free space)
will not be as clearly visible.
Myth: Most Discriminating Elements Should Be First
This seems like common sense. If you are going to create an index on the columns
C1
and
C2
in a table
T
with 100,000
rows, and you find
C1
has 100,000 distinct values and
C2
has 25,000 distinct values, you would want to create the index
on
T(C1,C2)
. This means that
C1
should be first, which is the commonsense approach. The fact is, when comparing
vectors of data (consider
C1, C2
to be a vector), it doesn't matter which you put first. Consider the following example.
We will create a table based on
ALL_OBJECTS
and an index on the
OWNER
,
OBJECT_TYPE
, and
OBJECT_NAME
columns
(least discriminating to most discriminating) and also on
OBJECT_NAME
,
OBJECT_TYPE
, and
OWNER
:
EODA@ORA12CR1> create table t as select * from all_objects;
Table created.
EODA@ORA12CR1> create index t_idx_1 on t(owner,object_type,object_name);
Index created.
EODA@ORA12CR1> create index t_idx_2 on t(object_name,object_type,owner);
Index created.
EODA@ORA12CR1> select count(distinct owner), count(distinct object_type),
2 count(distinct object_name ), count(*)
3 from t;
COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_TYPE)
COUNT(DISTINCTOBJECT_NAME) COUNT(*)
-------------------- -------------------------- -------------------------- ----------
34 36 30813 50253
