VIEW: SYS.KU$_10_1_TAB_ONLY_STATS_VIEW

Source

View Text - Preformatted

select  t.obj#, t.trigflag,
          (select value(sov) from ku$_schemaobj_view sov
           where sov.obj_num = t.obj#),
          null, bitand(t.property, 2097152), t.blkcnt, t.rowcnt, t.avgrln,
          decode(bitand(t.flags, 768),
                 786, 3,            /* user specified stats and global stats */
                 512, 2,                                /* global stats only */
                 256, 1,                        /* user specified stats only */
                 /* Bug 8794227: t.flags values 768,512,256 gives info about
                    stats type but not about whether table is analyzed or not.
                    To find whether table analyzed or not, one more decode
                    function is added with value 16. */
                 0, decode(bitand(t.flags,16),
                           16,0,                        /* Table is analyzed */
                              4),   /* Table never analyzed or stats deleted */
                 0),
          (select value(tcsv) from sys.ku$_tab_cache_stats_view tcsv
           where t.obj# = tcsv.obj_num),
          cast(multiset(select value(tcsv)
                        from   sys.ku$_10_1_tab_col_stats_view tcsv
                        where  tcsv.tab_obj_num = t.obj# and
                               bitand(t.property, 2097152) = 0 )
                        as ku$_10_1_col_stats_list_t)
  from    sys.tab$ t
  where   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#)

View Text - HTML Formatted

SELECT T.OBJ#
, T.TRIGFLAG
, (SELECT VALUE(SOV)
FROM KU$_SCHEMAOBJ_VIEW SOV
WHERE SOV.OBJ_NUM = T.OBJ#)
, NULL
, BITAND(T.PROPERTY
, 2097152)
, T.BLKCNT
, T.ROWCNT
, T.AVGRLN
, DECODE(BITAND(T.FLAGS
, 768)
, 786
, 3
, /* USER SPECIFIED STATS
AND GLOBAL STATS */ 512
, 2
, /* GLOBAL STATS ONLY */ 256
, 1
, /* USER SPECIFIED STATS ONLY */ /* BUG 8794227: T.FLAGS VALUES 768
, 512
, 256 GIVES INFO ABOUT STATS TYPE BUT NOT ABOUT WHETHER TABLE IS ANALYZED OR NOT. TO FIND WHETHER TABLE ANALYZED OR NOT
, ONE MORE DECODE FUNCTION IS ADDED WITH VALUE 16. */ 0
, DECODE(BITAND(T.FLAGS
, 16)
, 16
, 0
, /* TABLE IS ANALYZED */ 4)
, /* TABLE NEVER ANALYZED OR STATS DELETED */ 0)
, (SELECT VALUE(TCSV)
FROM SYS.KU$_TAB_CACHE_STATS_VIEW TCSV
WHERE T.OBJ# = TCSV.OBJ_NUM)
, CAST(MULTISET(SELECT VALUE(TCSV)
FROM SYS.KU$_10_1_TAB_COL_STATS_VIEW TCSV
WHERE TCSV.TAB_OBJ_NUM = T.OBJ# AND BITAND(T.PROPERTY
, 2097152) = 0 ) AS KU$_10_1_COL_STATS_LIST_T)
FROM SYS.TAB$ T
WHERE 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#)