-- E.Nossova, Product TuTool (Grundlage: Skript von S.Adams) : www.tutool.de.
-- folgende Formeln wurden benutzt:
-- block header size = kcbh + ub4 + ktbbh + ((inittrans - 1)*ktbit) + kdbh,
-- available data space in block = ceil((block size - block header size)*(1 - pctfree/100)) - kdbt (oder ub4),
-- row space = sum(colsize + (1, if colsize <= 250, else 3)


/* reports normal indexes that should be rebuilt,
input parameter : density in %
        default : 75,
requirement : accurate optimizer statistics*/
 
set pagesize 10000
set verify off
set feedback off
set linesize 1000
set trim on
set trimspool on
set arraysize 1
 
column index_name format a40
column part_name format a20
column subpart_name format a20
column last_ddl_time  format a13
column x noprint new_value x
column last_analyzed format a13
column compression format a11
column user_stats format a10
column density format a8
 
set term off
 
select decode(user,'SYS','x','x_') x from sys.dual
/
 
alter session set nls_date_format='dd.mm.yyyy'
/

set term on

define density='&density'

define obj = 'nvl(isp.obj#, nvl(ip.obj#,i.obj#))'
define objp = 'nvl(ip.obj#,i.obj#)'
define rowcnt = 'decode(&obj, isp.obj#, isp.rowcnt, ip.obj#, ip.rowcnt, i.rowcnt)'
define leafcnt = 'decode(&obj, isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt)'
define pctf = 'decode(&obj, isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$)'
define initr = 'decode(&obj, isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans)'
define last_analyzed = 'nvl(decode(&obj, isp.obj#, isp.analyzetime, ip.obj#, ip.analyzetime, i.analyzetime),to_date(''01.01.1900'',''dd.mm.yyyy''))'
define compress = 'decode(&obj, isp.obj#, ''N/A'', ip.obj#, decode(bitand(ip.flags, 1024), 0, ''DISABLED'', 1024, ''ENABLED'',''N/A''), decode(bitand(i.flags, 32), 0, ''DISABLED'', 32, ''ENABLED'',''N/A''))'
define user_stats = 'decode(&obj, isp.obj#, decode(bitand(isp.flags, 64), 0, ''NO'', ''YES''), ip.obj#, decode(bitand(ip.flags, 64), 0, ''NO'', ''YES''), decode(bitand(i.flags, 64), 0, ''NO'', ''YES''))'

select /*+ ordered */
  u.name ||'.'|| o.name  index_name,
  op.subname part_name,
  decode(&obj, isp.obj#, o.subname, '') subpart_name,
  to_char(100*(1 - floor( &leafcnt -
    &rowcnt * (sum(h.avgcln*t.rowcnt/decode(i.rowcnt,0,1,i.rowcnt) + decode(sign(h.avgcln*t.rowcnt/decode(i.rowcnt,0,1,i.rowcnt) - 250),-1,1,0,1,3)) + rd.rowid_len) / (ceil((p.value - tl.kcbh - tl.ub4 - tl.ktbbh - ((&initr - 1) * tl.ktbit) - tl.kdbh)*(1 - &pctf/100)) - decode(tl.kdbt,0,tl.ub4,tl.kdbt))
  )/&leafcnt),'990.00') ||'%' density,
  floor( &leafcnt -
  &rowcnt * (sum(h.avgcln*t.rowcnt/decode(i.rowcnt,0,1,i.rowcnt) + decode(sign(h.avgcln*t.rowcnt/decode(i.rowcnt,0,1,i.rowcnt) - 250),-1,1,0,1,3)) + rd.rowid_len)  / (ceil((p.value - tl.kcbh - tl.ub4 - tl.ktbbh - ((&initr - 1) * tl.ktbit) - tl.kdbh)*(1 - &pctf/100)) - decode(tl.kdbt,0,tl.ub4,tl.kdbt))
  ) extra_leaf_blocks,
  p.value block_size,
  max(o.mtime) last_ddl_time,
  decode(max(&last_analyzed),to_date('01.01.1900','dd.mm.yyyy'),'not analyzed',max(&last_analyzed)) last_analyzed,
  max(&compress) compression,
  max(&user_stats) user_stats
from
  (select vsize(rowid) rowid_len from sys.dual) rd,
  (select sum(s1) kcbh, sum(s2) ub4, sum(s3) ktbbh, sum(s4) ktbit, sum(s5)kdbh, sum(s6) ub1, sum(s7) sb2, sum(s8) kdbt from 
       ( select type_size s1,0 s2,0 s3,0 s4,0 s5,0 s6,0 s7,0 s8 from sys.v_$type_size where type='KCBH'
         union all 
         select 0 s1,type_size s2,0 s3,0 s4,0 s5,0 s6,0 s7,0 s8 from sys.v_$type_size where type='UB4'
	 union all
         select 0 s1,0 s2,type_size s3,0 s4,0 s5,0 s6,0 s7,0 s8 from sys.v_$type_size where type='KTBBH'
	 union all
         select 0 s1,0 s2,0 s3,type_size s4,0 s5,0 s6,0 s7,0 s8 from sys.v_$type_size where type='KTBIT'
	 union all
         select 0 s1,0 s2,0 s3,0 s4,type_size s5,0 s6,0 s7,0 s8 from sys.v_$type_size where type='KDBH'
	 union all 
         select 0 s1,0 s2,0 s3,0 s4,0 s5,type_size s6,0 s7,0 s8 from sys.v_$type_size where type='UB1'
	 union all
         select 0 s1,0 s2,0 s3,0 s4,0 s5,0 s6,type_size s7,0 s8 from sys.v_$type_size where type='SB2'
	 union all
         select 0 s1,0 s2,0 s3,0 s4,0 s5,0 s6,0 s7,type_size s8 from sys.v_$type_size where type='KDBT'
	)
  ) tl,
  sys.ind$  i,
  sys.icol$  ic,
  ( select obj#, part#, bo#, ts#, rowcnt, leafcnt, initrans, pctfree$, analyzetime, flags from sys.indpart$ 
    union all
    select obj#, part#, bo#, defts#, rowcnt, leafcnt, definitrans, defpctfree, analyzetime, flags from sys.indcompart$ ) ip,
  sys.indsubpart$ isp,
  ( select ts#, blocksize value
    from sys.ts$
      )  p,
  sys.hist_head$  h,
  sys.obj$  o,
  sys.tab$ t,
  sys.user$  u,
  sys.obj$  op
where
  i.obj# = ip.bo#(+) and
  ip.obj# = isp.pobj#(+) and
  &leafcnt > 1 and
  i.type# in (1) and -- exclude special types
  i.pctthres$ is null and -- exclude IOT secondary indexes
  decode(&obj, isp.obj#, isp.ts#, ip.obj#, ip.ts#, i.ts#) = p.ts# and
  ic.obj# = i.obj# and
  h.obj# = i.bo# and
  h.intcol# = ic.intcol# and
  t.obj# = i.bo# and
  o.obj# = &obj and
  o.owner# != 0 and
  u.user# = o.owner# and
  op.obj# = &objp 
group by
  rd.rowid_len,
  tl.kcbh,
  tl.ub4,
  tl.ktbbh,
  tl.ktbit,
  tl.kdbh,
  tl.ub1,
  tl.sb2,
  tl.kdbt,
  u.name,
  o.name,
  op.subname, 
  decode(&obj, isp.obj#, o.subname, ''),
  &rowcnt,
  &leafcnt,
  &initr, 
  &pctf,
  p.value
  having
    100*(1 - floor( &leafcnt -
    &rowcnt * (sum(h.avgcln*t.rowcnt/decode(i.rowcnt,0,1,i.rowcnt) + decode(sign(h.avgcln*t.rowcnt/decode(i.rowcnt,0,1,i.rowcnt) - 250),-1,1,0,1,3)) + rd.rowid_len)  / (ceil((p.value - tl.kcbh - tl.ub4 - tl.ktbbh - ((&initr - 1) * tl.ktbit) - tl.kdbh)*(1 - &pctf/100)) - decode(tl.kdbt,0,tl.ub4,tl.kdbt))
    )/&leafcnt) between 0 and nvl('&density','75') and
    floor( &leafcnt -
    &rowcnt * (sum(h.avgcln*t.rowcnt/decode(i.rowcnt,0,1,i.rowcnt) + decode(sign(h.avgcln*t.rowcnt/decode(i.rowcnt,0,1,i.rowcnt) - 250),-1,1,0,1,3)) + rd.rowid_len)  / (ceil((p.value - tl.kcbh - tl.ub4 - tl.ktbbh - ((&initr - 1) * tl.ktbit) - tl.kdbh)*(1 - &pctf/100)) - decode(tl.kdbt,0,tl.ub4,tl.kdbt))
    ) >= 0
order by
  5 desc, 4 
/
 

set linesize 80
 
undefine rowcnt 
undefine leafcnt
undefine pctf
undefine initr
undefine last_analyzed
undefine compress
undefine obj
undefine density