Database Reference
In-Depth Information
Now, to show that neither is more efficient space-wise, we'll measure their space utilization:
EODA@ORA12CR1> analyze index t_idx_1 validate structure;
Index analyzed.
EODA@ORA12CR1> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave
2 from index_stats;
BTREE_SPACE PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- --------- -------------- ----------------
2526832 89 2 28
EODA@ORA12CR1> analyze index t_idx_2 validate structure;
Index analyzed.
EODA@ORA12CR1> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave
2 from index_stats;
BTREE_SPACE PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- --------- -------------- ----------------
2510776 90 0 0
They use nearly the exact same amount of space—there are no major differences there. However, the first index
is a lot more
compressible
if we use index key compression, as evidenced by the
OPT_CMPR_PCTSAVE
value. There is an
argument for arranging the columns in the index in order from the least discriminating to the most discriminating.
Now let's see how they perform, to determine if either index is generally more efficient than the other. To test this,
we'll use a PL/SQL block with hinted queries (so as to use one index or the other):
EODA@ORA12CR1> alter session set sql_trace=true;
Session altered.
EODA@ORA12CR1> declare
2 cnt int;
3 begin
4 for x in ( select /*+FULL(t)*/ owner, object_type, object_name from t )
5 loop
6 select /*+ INDEX( t t_idx_1 ) */ count(*) into cnt
7 from t
8 where object_name = x.object_name
9 and object_type = x.object_type
10 and owner = x.owner;
11
12 select /*+ INDEX( t t_idx_2 ) */ count(*) into cnt
13 from t
14 where object_name = x.object_name
15 and object_type = x.object_type
16 and owner = x.owner;
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
