VIEW: SYS.KU$_IONT_VIEW

Source

View Text - Preformatted

select t.obj#, t.property,
        (select value(s) from ku$_storage_view s
         where     i.file#  = s.file_num
             and   i.block# = s.block_num
             and   i.ts#    = s.ts_num),
        (select value(s) from ku$_deferred_stg_view s
         where s.obj_num = i.obj#),
        (select ts.name from ts$ ts where i.ts# = ts.ts#),
        (select ts.blocksize from ts$ ts where i.ts# = ts.ts#),
        i.pctfree$, i.initrans, i.maxtrans, t.flags,
        mod(i.pctthres$,256), i.spare2,
        (select c.name from col$ c
                 where c.obj# = i.bo#
                 and   c.col# = i.trunccnt and i.trunccnt != 0),
         cast( multiset(select * from ku$_constraint0_view con
                        where con.obj_num = t.obj#
                        and con.contype not in (7,11)
                       ) as ku$_constraint0_list_t
             ),
         cast( multiset(select * from ku$_constraint1_view con
                        where con.obj_num = t.obj#
                       ) as ku$_constraint1_list_t
             ),
         cast( multiset(select * from ku$_constraint2_view con
                        where con.obj_num = t.obj#
                       ) as ku$_constraint2_list_t
             ),
         cast( multiset(select * from ku$_pkref_constraint_view con
                        where con.obj_num = t.obj#
                       ) as ku$_pkref_constraint_list_t
             ),
        (select value(ov) from ku$_ov_table_view ov
         where ov.bobj_num = t.obj#
         and bitand(t.property, 128) = 128)  -- IOT has overflow
  from tab$ t, ind$ i
  where bitand(t.property,64+512) = 64  -- IOT but not overflow
    and t.pctused$ = i.obj#             -- for IOTs, pctused has index obj#

View Text - HTML Formatted

SELECT T.OBJ#
, T.PROPERTY
, (SELECT VALUE(S)
FROM KU$_STORAGE_VIEW S
WHERE I.FILE# = S.FILE_NUM
AND I.BLOCK# = S.BLOCK_NUM
AND I.TS# = S.TS_NUM)
, (SELECT VALUE(S)
FROM KU$_DEFERRED_STG_VIEW S
WHERE S.OBJ_NUM = I.OBJ#)
, (SELECT TS.NAME
FROM TS$ TS
WHERE I.TS# = TS.TS#)
, (SELECT TS.BLOCKSIZE
FROM TS$ TS
WHERE I.TS# = TS.TS#)
, I.PCTFREE$
, I.INITRANS
, I.MAXTRANS
, T.FLAGS
, MOD(I.PCTTHRES$
, 256)
, I.SPARE2
, (SELECT C.NAME
FROM COL$ C
WHERE C.OBJ# = I.BO#
AND C.COL# = I.TRUNCCNT
AND I.TRUNCCNT != 0)
, CAST( MULTISET(SELECT *
FROM KU$_CONSTRAINT0_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ#
AND CON.CONTYPE NOT IN (7
, 11) ) AS KU$_CONSTRAINT0_LIST_T )
, CAST( MULTISET(SELECT *
FROM KU$_CONSTRAINT1_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ# ) AS KU$_CONSTRAINT1_LIST_T )
, CAST( MULTISET(SELECT *
FROM KU$_CONSTRAINT2_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ# ) AS KU$_CONSTRAINT2_LIST_T )
, CAST( MULTISET(SELECT *
FROM KU$_PKREF_CONSTRAINT_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ# ) AS KU$_PKREF_CONSTRAINT_LIST_T )
, (SELECT VALUE(OV)
FROM KU$_OV_TABLE_VIEW OV
WHERE OV.BOBJ_NUM = T.OBJ#
AND BITAND(T.PROPERTY
, 128) = 128) -- IOT HAS OVERFLOW
FROM TAB$ T
, IND$ I
WHERE BITAND(T.PROPERTY
, 64+512) = 64 -- IOT BUT NOT OVERFLOW
AND T.PCTUSED$ = I.OBJ# -- FOR IOTS
, PCTUSED HAS INDEX OBJ#