VIEW: SYS.DBA_PART_COL_STATISTICS

Source

View Text - Preformatted

select u.name, o.name, o.subname, tp.cname, h.distcnt,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.lowval
       else null
       end,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.hival
       else null
       end,
       h.density, h.null_cnt,
       case when nvl(h.distcnt,0) = 0 then h.distcnt
            when h.row_cnt = 0 then 1
	    when exists(select 1 from sys.histgrm$ hg
                        where tp.obj# = hg.obj# and tp.intcol# = hg.intcol#
                          and hg.ep_repeat_count > 0 and rownum < 2) then h.row_cnt
            when bitand(h.spare2, 64) > 0
              then h.row_cnt
            when (bitand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or
                  (h.bucket_cnt >= h.distcnt and h.density*h.bucket_cnt < 1))
                then h.row_cnt
            else h.bucket_cnt
       end,
       h.sample_size, h.timestamp#,
       decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
       decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
       decode(bitand(h.spare2, 8), 8, 'INCREMENTAL ', '') ||
         decode(bitand(h.spare2, 128), 128, 'HIST_FOR_INCREM_STATS ', '') ||
         decode(bitand(h.spare2, 256), 256, 'HISTOGRAM_ONLY ', '') ||
         decode(bitand(h.spare2, 512), 512, 'STATS_ON_LOAD ', ''),
       h.avgcln,
       case when nvl(h.row_cnt,0) = 0 then 'NONE'
            when exists(select 1 from sys.histgrm$ hg
                        where tp.obj# = hg.obj# and tp.intcol# = hg.intcol#
                          and hg.ep_repeat_count > 0 and rownum < 2) then 'HYBRID'
            when bitand(h.spare2, 64) > 0
              then 'TOP-FREQUENCY'
            when (bitand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or
                  (h.bucket_cnt >= h.distcnt and h.density*h.bucket_cnt < 1))
                then 'FREQUENCY'
            else 'HEIGHT BALANCED'
       end
from sys.obj$ o, sys.hist_head$ h, tp$ tp, user$ u
where o.obj# = tp.obj# and o.owner# = u.user#
  and tp.obj# = h.obj#(+) and tp.intcol# = h.intcol#(+)
  and o.type# = 19 /* TABLE PARTITION */
  and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, O.SUBNAME
, TP.CNAME
, H.DISTCNT
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.LOWVAL ELSE NULL END
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.HIVAL ELSE NULL END
, H.DENSITY
, H.NULL_CNT
, CASE WHEN NVL(H.DISTCNT
, 0) = 0 THEN H.DISTCNT WHEN H.ROW_CNT = 0 THEN 1 WHEN EXISTS(SELECT 1
FROM SYS.HISTGRM$ HG
WHERE TP.OBJ# = HG.OBJ#
AND TP.INTCOL# = HG.INTCOL#
AND HG.EP_REPEAT_COUNT > 0
AND ROWNUM < 2) THEN H.ROW_CNT WHEN BITAND(H.SPARE2
, 64) > 0 THEN H.ROW_CNT WHEN (BITAND(H.SPARE2
, 32) > 0 OR H.BUCKET_CNT > 2049 OR (H.BUCKET_CNT >= H.DISTCNT
AND H.DENSITY*H.BUCKET_CNT < 1)) THEN H.ROW_CNT ELSE H.BUCKET_CNT END
, H.SAMPLE_SIZE
, H.TIMESTAMP#
, DECODE(BITAND(H.SPARE2
, 2)
, 2
, 'YES'
, 'NO')
, DECODE(BITAND(H.SPARE2
, 1)
, 1
, 'YES'
, 'NO')
, DECODE(BITAND(H.SPARE2
, 8)
, 8
, 'INCREMENTAL '
, '') || DECODE(BITAND(H.SPARE2
, 128)
, 128
, 'HIST_FOR_INCREM_STATS '
, '') || DECODE(BITAND(H.SPARE2
, 256)
, 256
, 'HISTOGRAM_ONLY '
, '') || DECODE(BITAND(H.SPARE2
, 512)
, 512
, 'STATS_ON_LOAD '
, '')
, H.AVGCLN
, CASE WHEN NVL(H.ROW_CNT
, 0) = 0 THEN 'NONE' WHEN EXISTS(SELECT 1
FROM SYS.HISTGRM$ HG
WHERE TP.OBJ# = HG.OBJ#
AND TP.INTCOL# = HG.INTCOL#
AND HG.EP_REPEAT_COUNT > 0
AND ROWNUM < 2) THEN 'HYBRID' WHEN BITAND(H.SPARE2
, 64) > 0 THEN 'TOP-FREQUENCY' WHEN (BITAND(H.SPARE2
, 32) > 0 OR H.BUCKET_CNT > 2049 OR (H.BUCKET_CNT >= H.DISTCNT
AND H.DENSITY*H.BUCKET_CNT < 1)) THEN 'FREQUENCY' ELSE 'HEIGHT BALANCED' END FROM SYS.OBJ$ O
, SYS.HIST_HEAD$ H
, TP$ TP
, USER$ U WHERE O.OBJ# = TP.OBJ#
AND O.OWNER# = U.USER#
AND TP.OBJ# = H.OBJ#(+)
AND TP.INTCOL# = H.INTCOL#(+)
AND O.TYPE# = 19 /* TABLE PARTITION */
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL