Index key compression - performance benefits?

From: Keith Moore <kmoore_at_zephyrus.com>
Date: Tue, 29 Aug 2006 11:37:51 -0500 (CDT)
Message-ID: <48463.206.227.128.10.1156869471.squirrel@lady.zephyrus.com>

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 /

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 29 2006 - 11:37:51 CDT