Index key compression - performance benefits?
- From: "Keith Moore" <kmoore@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 29 Aug 2006 11:37:51 -0500 (CDT)
It has been recommended that we do index key compression on several
indexes for a "pseudo" data warehouse. The recommendation was related to
performance improvement, not space savings. The database version is 9i
release 2.
Personally, I am doubtful that the performance improvement will be very
great. My understanding is that performance will only be improved in cases
where you are doing a full index scan or a large index range scan. The
only places I have seen this is where the developer incorrectly used an
index hint and the query should have been doing a full table scan instead.
The query used to identify the indexes came from Steve Adam's web site. It
is shown below. While I don't understand all the calculations involved, I
can see that it orders the results by the amount of space saved (greatest
of one_save and full_save) and does not relate directly to performance.
The web site indicates the query works with 8i, but has not (yet) been
tested with 9i. Does anyone know if it works with Oracle 9i?
Also, any comments on the theory behind the calculations for one_save and
full_save would be appreciated.
Keith
Here is the query:
select
u.name ||'.'|| o.name index_name,
decode(
sign(s.full_save - s.one_save),
-1, 1,
decode(s.cols, 1, 1, 2)
) min_compress,
decode(
sign(s.full_save - s.one_save),
-1, greatest(1, least(s.max_ok, s.cols - 1)),
s.cols
) max_compress
from
(
select
x.obj#,
x.cols,
x.leaf_bytes,
x.full_save,
x.max_ok,
h1.avgcln * (x.rowcnt - h1.null_cnt - h1.distcnt) - 4 * h1.distcnt
one_save
from
( select
i.obj#,
i.cols,
i.rowcnt,
(sum(h.avgcln) + 10) * i.rowcnt leaf_bytes,
sum(h.avgcln) * (i.rowcnt - i.distkey) - 4 * i.distkey full_save,
max(decode(sign(i.rowcnt - 2 * h.distcnt), -1, 0, ic.pos#)) max_ok
from
sys.ind$ i,
sys.icol$ ic,
sys.hist_head$ h
where
i.leafcnt > 1 and
i.type# in (1,4,6) and -- exclude special types
bitand(i.property, 8) = 0 and -- exclude compressed
ic.obj# = i.obj# and
h.obj# = i.bo# and
h.intcol# = ic.intcol#
group by
i.obj#,
i.cols,
i.rowcnt,
i.distkey
) x,
sys.icol$ c1,
sys.hist_head$ h1
where
c1.obj# = x.obj# and
c1.pos# = 1 and
h1.obj# = c1.bo# and
h1.intcol# = c1.intcol#
) s,
sys.obj$ o,
sys.user$ u
where
greatest(s.full_save, s.one_save) > 0.05 * s.leaf_bytes and
o.obj# = s.obj# and
o.owner# != 0 and
u.user# = o.owner#
order by
greatest(s.full_save, s.one_save) desc
/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
