VIEW: SYS._utl$_gp_ind_parts

Source

View Text - Preformatted

SELECT 'P', Obj_IdxPart.name, Obj_IdxPart.subname, Obj_Tab.obj#,
       Obj_Tab.owner#, Obj_Idx.obj#, Obj_Idx.owner#,
       Usr.name, Obj_IdxPart.obj#, NULL, NULL, NULL,
       DECODE(bitand(IndPart.flags, 1), 1, 'U', 'V'),
       'G', Ind.degree,
       CASE WHEN Tab.rowcnt IS NOT NULL THEN Tab.rowcnt / PartObj.partcnt
            WHEN Seg.file# IS NULL THEN
              -- for deferred segment creation, set rowcnt to 0
              (CASE WHEN bitand(Tab.property, 17179869184) = 17179869184 THEN 0
               ELSE 1000000000
               END)
            ELSE CEIL((dbms_space_admin.segment_number_blocks(TSpc.ts#,
              Seg.file#, Seg.block#, Seg.type#, Seg.cachehint,
              NVL(Seg.spare1, 0),
              decode(bitand(Seg.spare1, 1), 1,
              Seg.hwmincr, Tab.dataobj#),
              Seg.blocks) *
              TSpc.blocksize) / (100 * PartObj.partcnt)) END,
       DECODE(bitand(Ind.property, 1024), 1024, 'Y', 'N')
FROM
     sys.user$ Usr,
     sys.obj$ Obj_Idx,
     sys.obj$ Obj_Tab,
     sys.partobj$ PartObj,
     sys.ind$ Ind,
     sys.obj$ Obj_IdxPart,
     sys.indpart$ IndPart,
     sys.ts$ TSpc,
     sys.tab$ Tab
LEFT OUTER JOIN
     sys.seg$ Seg
ON Seg.file# = Tab.file#
  AND Seg.block# = Tab.block#
  AND Seg.ts# = Tab.ts#
  AND Seg.type# = 5
WHERE IndPart.bo# = Ind.obj#
  AND Ind.type# <> 4               -- not IOT - TOP
  AND Ind.bo# = Obj_Tab.obj#
  AND IndPart.obj# = Obj_IdxPart.obj#
  AND Ind.obj# = Obj_Idx.obj#
  AND PartObj.obj# = Ind.obj#
  AND bitand(PartObj.flags, 1) = 0 -- 0x01 = Local index
  AND Obj_Idx.owner# = Usr.user#
  AND Tab.obj# = Obj_Tab.obj#
  AND Tab.ts# = TSpc.ts#

View Text - HTML Formatted

SELECT 'P'
, OBJ_IDXPART.NAME
, OBJ_IDXPART.SUBNAME
, OBJ_TAB.OBJ#
, OBJ_TAB.OWNER#
, OBJ_IDX.OBJ#
, OBJ_IDX.OWNER#
, USR.NAME
, OBJ_IDXPART.OBJ#
, NULL
, NULL
, NULL
, DECODE(BITAND(INDPART.FLAGS
, 1)
, 1
, 'U'
, 'V')
, 'G'
, IND.DEGREE
, CASE WHEN TAB.ROWCNT IS NOT NULL THEN TAB.ROWCNT / PARTOBJ.PARTCNT WHEN SEG.FILE# IS NULL THEN -- FOR DEFERRED SEGMENT CREATION
, SET ROWCNT TO 0 (CASE WHEN BITAND(TAB.PROPERTY
, 17179869184) = 17179869184 THEN 0 ELSE 1000000000 END) ELSE CEIL((DBMS_SPACE_ADMIN.SEGMENT_NUMBER_BLOCKS(TSPC.TS#
, SEG.FILE#
, SEG.BLOCK#
, SEG.TYPE#
, SEG.CACHEHINT
, NVL(SEG.SPARE1
, 0)
, DECODE(BITAND(SEG.SPARE1
, 1)
, 1
, SEG.HWMINCR
, TAB.DATAOBJ#)
, SEG.BLOCKS) * TSPC.BLOCKSIZE) / (100 * PARTOBJ.PARTCNT)) END
, DECODE(BITAND(IND.PROPERTY
, 1024)
, 1024
, 'Y'
, 'N') FROM SYS.USER$ USR
, SYS.OBJ$ OBJ_IDX
, SYS.OBJ$ OBJ_TAB
, SYS.PARTOBJ$ PARTOBJ
, SYS.IND$ IND
, SYS.OBJ$ OBJ_IDXPART
, SYS.INDPART$ INDPART
, SYS.TS$ TSPC
, SYS.TAB$ TAB LEFT OUTER JOIN SYS.SEG$ SEG ON SEG.FILE# = TAB.FILE#
AND SEG.BLOCK# = TAB.BLOCK#
AND SEG.TS# = TAB.TS#
AND SEG.TYPE# = 5 WHERE INDPART.BO# = IND.OBJ#
AND IND.TYPE# <> 4 -- NOT IOT - TOP
AND IND.BO# = OBJ_TAB.OBJ#
AND INDPART.OBJ# = OBJ_IDXPART.OBJ#
AND IND.OBJ# = OBJ_IDX.OBJ#
AND PARTOBJ.OBJ# = IND.OBJ#
AND BITAND(PARTOBJ.FLAGS
, 1) = 0 -- 0X01 = LOCAL INDEX
AND OBJ_IDX.OWNER# = USR.USER#
AND TAB.OBJ# = OBJ_TAB.OBJ#
AND TAB.TS# = TSPC.TS#