VIEW: SYS.KU$_10_1_PTAB_STATS_VIEW

Source

View Text - Preformatted

select  t.obj#, bt.trigflag,
          (select value(sov) from ku$_schemaobj_view sov
           where sov.obj_num = t.obj#),
          t.bo#, bitand(bt.property, 2097152), t.blkcnt, t.rowcnt, t.avgrln,
          decode(bitand(t.flags, 24), 24, 3, 16, 2, 8, 1, 0),
          (select value(tcsv) from sys.ku$_tab_cache_stats_view tcsv
           where t.obj# = tcsv.obj_num),
          cast(multiset(select value(pcsv)
                        from   sys.ku$_10_1_ptab_col_stats_view pcsv
                        where  pcsv.tab_obj_num = bo# and
                               pcsv.p_obj_num = t.obj# and
                               bitand(bt.property, 2097152) = 0)
                        as ku$_10_1_col_stats_list_t)
  from    sys.tab$ bt, sys.tabpart$ t
  where   t.bo# = bt.obj# AND
          NOT EXISTS (                   /* table does not have associations */
                SELECT 1
                FROM   sys.association$ a
                where  a.obj# = t.obj#) and
          NOT EXISTS (           /* type in table does not have associations */
                SELECT  1
                FROM    sys.obj$ tt, sys.coltype$ ct, sys.association$ a
                WHERE   t.obj# = ct.obj# AND
                        ct.toid = tt.oid$ AND
                        tt.obj# = a.obj#) AND
          BITAND(t.flags,2) != 0
UNION ALL
  select  tsp.obj#, bt.trigflag,
          (select value(sov) from ku$_schemaobj_view sov
           where sov.obj_num = tsp.obj#),
          t.bo#, bitand(bt.property, 2097152), tsp.blkcnt, tsp.rowcnt,
          tsp.avgrln,
          decode(bitand(tsp.flags, 24), 24, 3, 16, 2, 8, 1, 0),
          (select value(tcsv) from sys.ku$_tab_cache_stats_view tcsv
           where t.obj# = tcsv.obj_num),
          cast(multiset(select value(pcsv)
                        from   sys.ku$_10_1_ptab_col_stats_view pcsv
                        where  pcsv.tab_obj_num = bo# and
                               pcsv.p_obj_num = tsp.obj# and
                               bitand(bt.property, 2097152) = 0)
                        as ku$_10_1_col_stats_list_t)
  from    sys.tab$ bt, sys.tabcompart$ t, sys.tabsubpart$ tsp
  where   tsp.pobj# = t.obj# AND
          t.bo# = bt.obj# AND
          NOT EXISTS (                   /* table does not have associations */
                SELECT 1
                FROM   sys.association$ a
                where  a.obj# = t.obj#) and
          NOT EXISTS (           /* type in table does not have associations */
                SELECT  1
                FROM    sys.obj$ tt, sys.coltype$ ct, sys.association$ a
                WHERE   t.obj# = ct.obj# AND
                        ct.toid = tt.oid$ AND
                        tt.obj# = a.obj#) AND
          BITAND(tsp.flags,2) != 0

View Text - HTML Formatted

SELECT T.OBJ#
, BT.TRIGFLAG
, (SELECT VALUE(SOV)
FROM KU$_SCHEMAOBJ_VIEW SOV
WHERE SOV.OBJ_NUM = T.OBJ#)
, T.BO#
, BITAND(BT.PROPERTY
, 2097152)
, T.BLKCNT
, T.ROWCNT
, T.AVGRLN
, DECODE(BITAND(T.FLAGS
, 24)
, 24
, 3
, 16
, 2
, 8
, 1
, 0)
, (SELECT VALUE(TCSV)
FROM SYS.KU$_TAB_CACHE_STATS_VIEW TCSV
WHERE T.OBJ# = TCSV.OBJ_NUM)
, CAST(MULTISET(SELECT VALUE(PCSV)
FROM SYS.KU$_10_1_PTAB_COL_STATS_VIEW PCSV
WHERE PCSV.TAB_OBJ_NUM = BO# AND PCSV.P_OBJ_NUM = T.OBJ# AND BITAND(BT.PROPERTY
, 2097152) = 0) AS KU$_10_1_COL_STATS_LIST_T)
FROM SYS.TAB$ BT
, SYS.TABPART$ T
WHERE T.BO# = BT.OBJ# AND NOT EXISTS ( /* TABLE DOES NOT HAVE ASSOCIATIONS */ SELECT 1
FROM SYS.ASSOCIATION$ A
WHERE A.OBJ# = T.OBJ#) AND NOT EXISTS ( /* TYPE IN TABLE DOES NOT HAVE ASSOCIATIONS */ SELECT 1
FROM SYS.OBJ$ TT
, SYS.COLTYPE$ CT
, SYS.ASSOCIATION$ A
WHERE T.OBJ# = CT.OBJ# AND CT.TOID = TT.OID$ AND TT.OBJ# = A.OBJ#) AND BITAND(T.FLAGS
, 2) != 0 UNION ALL SELECT TSP.OBJ#
, BT.TRIGFLAG
, (SELECT VALUE(SOV)
FROM KU$_SCHEMAOBJ_VIEW SOV
WHERE SOV.OBJ_NUM = TSP.OBJ#)
, T.BO#
, BITAND(BT.PROPERTY
, 2097152)
, TSP.BLKCNT
, TSP.ROWCNT
, TSP.AVGRLN
, DECODE(BITAND(TSP.FLAGS
, 24)
, 24
, 3
, 16
, 2
, 8
, 1
, 0)
, (SELECT VALUE(TCSV)
FROM SYS.KU$_TAB_CACHE_STATS_VIEW TCSV
WHERE T.OBJ# = TCSV.OBJ_NUM)
, CAST(MULTISET(SELECT VALUE(PCSV)
FROM SYS.KU$_10_1_PTAB_COL_STATS_VIEW PCSV
WHERE PCSV.TAB_OBJ_NUM = BO# AND PCSV.P_OBJ_NUM = TSP.OBJ# AND BITAND(BT.PROPERTY
, 2097152) = 0) AS KU$_10_1_COL_STATS_LIST_T)
FROM SYS.TAB$ BT
, SYS.TABCOMPART$ T
, SYS.TABSUBPART$ TSP
WHERE TSP.POBJ# = T.OBJ# AND T.BO# = BT.OBJ# AND NOT EXISTS ( /* TABLE DOES NOT HAVE ASSOCIATIONS */ SELECT 1
FROM SYS.ASSOCIATION$ A
WHERE A.OBJ# = T.OBJ#) AND NOT EXISTS ( /* TYPE IN TABLE DOES NOT HAVE ASSOCIATIONS */ SELECT 1
FROM SYS.OBJ$ TT
, SYS.COLTYPE$ CT
, SYS.ASSOCIATION$ A
WHERE T.OBJ# = CT.OBJ# AND CT.TOID = TT.OID$ AND TT.OBJ# = A.OBJ#) AND BITAND(TSP.FLAGS
, 2) != 0