VIEW: SYS.KU$_TAB_ONLY_STATS_VIEW

Source

View Text - Preformatted

select  t.obj#, t.trigflag, o.name, null, null, 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),
           t.samplesize, TO_CHAR(t.analyzetime, 'YYYY-MM-DD HH24:MI:SS'),
          (select value(tcsv) from sys.ku$_tab_cache_stats_view tcsv
           where t.obj# = tcsv.obj_num),
          cast(multiset(select value(csv)
                        from   sys.ku$_col_stats_view csv
                        where  csv.obj_num = t.obj#)
                        as ku$_col_stats_list_t)
  from    sys.obj$ o, sys.tab$ t
  where   o.obj# = t.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#)

View Text - HTML Formatted

SELECT T.OBJ#
, T.TRIGFLAG
, O.NAME
, NULL
, NULL
, 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)
, T.SAMPLESIZE
, TO_CHAR(T.ANALYZETIME
, 'YYYY-MM-DD HH24:MI:SS')
, (SELECT VALUE(TCSV)
FROM SYS.KU$_TAB_CACHE_STATS_VIEW TCSV
WHERE T.OBJ# = TCSV.OBJ_NUM)
, CAST(MULTISET(SELECT VALUE(CSV)
FROM SYS.KU$_COL_STATS_VIEW CSV
WHERE CSV.OBJ_NUM = T.OBJ#) AS KU$_COL_STATS_LIST_T)
FROM SYS.OBJ$ O
, SYS.TAB$ T
WHERE O.OBJ# = T.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#)