Column Statistics

gistfile1.txt

select /*+ LEADING(tab) */  col.column_name, num_distinct as "Distinct", decode(num_distinct, 0, null, round((tab.num_rows - col.num_nulls) / col.num_distinct, case when (tab.num_rows - col.num_nulls) / col.num_distinct < 50 then 1 else 0 end)) as "Card",ceil((tab.num_rows - col.num_nulls) * col.density) as "Dens", indc.column_position || nvl2(indc.index_name, chr(64 + dense_rank() over(order by index_name)), '`') as "i", nullif(cusg.equality_preds,0) "eq", nullif(cusg.equijoin_preds,0)||'/'|| nullif(cusg.nonequijoin_preds,0) "join", nullif(cusg.range_preds,0)||'/'|| nullif(cusg.like_preds,0)||'/'|| nullif(cusg.null_preds,0) "r/l/n",num_nulls as "nulls", num_buckets as "Hist", round(sysdate - col.last_analyzed, 1) as "old", avg_col_len as "len", indc.index_name, case when indc.type# = 2 then 'B' when bitand(indc.property, 1) =1 then 'U' when bitand(indc.flags, 32) = 32 then 'C' || indc.prefix_length END as "F", round(indc.leaf_blocks*8/1024,1) as "Mb", indc.distinct_keys as "Keys", indc.AVG_DATA_BLOCKS_PER_KEY as "DBPK",extract( day from(systimestamp - cusg.timestamp)) as "so" from dba_tables tab, dba_tab_columns col, (select c.name as column_name, ic.pos# as column_position, iu.name as OWNER, o.name as INDEX_NAME, ot.name as TABLE_NAME, otu.name as TABLE_OWNER, i.type#, i.property, i.flags, i.spare2 as PREFIX_LENGTH, i.leafcnt as leaf_blocks, i.distkey as distinct_keys, i.dblkkey as AVG_DATA_BLOCKS_PER_KEY from sys.ind$ i, sys.obj$ o, sys.user$ iu, sys.icol$ ic, sys.col$ c, sys.obj$ ot, sys.user$ otu where o.obj# = i.obj# and iu.user# = o.owner# and ic.obj# = i.obj# and c.obj# = i.bo# and decode(bitand(i.property, 1024), 0, ic.intcol#, ic.spare2) = c.intcol# and ot.obj# = i.bo# and otu.user# = ot.owner#) indc, sys.user$ tu, sys.obj$ obj, sys.col$ scol, sys.col_usage$ cusg where tab.owner = col.owner and tab.table_name = col.table_name and indc.table_owner(+) = col.owner and indc.table_name(+) = col.table_name and indc.column_name(+) = col.column_name
and tu.name = tab.owner and obj.owner# = tu.user# and obj.name = tab.table_name and obj.name = tab.table_name and obj.type# = 2 and scol.obj# = obj.obj# and scol.name = col.column_name and cusg.obj# (+)= scol.obj# and cusg.intcol#(+) = scol.COL#
and tab.owner = 'HOLDINGDBO' and tab.table_name = 'OPEN_REC_PAY' order by index_name,"i"