CLOB項目を作成するとINDEXが作成されます

LOBデータを確認する

テーブルを作成する際、CLOBタイプの項目を作成するとLOBセグメントとLOBインデックスが自動で作成されます。

検証用データ作成

create table TBL304
(
  lno    number
 ,name   varchar2(30)
 ,memo   clob
);
create index idx01_tbl304 on tbl304(name)
;
insert into TBL304 (LNO,NAME,MEMO) values ('1','北川','納豆は苦手');

select i.TABLE_OWNER,i.TABLE_NAME,i.INDEX_NAME,i.GENERATED from DBA_INDEXES i
where i.TABLE_OWNER='DABADA' and i.TABLE_NAME='TBL304'
order by i.OWNER,i.INDEX_NAME
;
--
-- CLOBカラムを定義する事で自動でシステムがインデックスを作成する
-- インデックス名はSYS_ILXXXXのように自動で生成
-- 
                                                                 GENE
TABLE_OWNER    TABLE_NAME           INDEX_NAME                   RATED
-------------- -------------------- ---------------------------- -----
DABADA         TBL304               IDX01_TBL304                 N
DABADA         TBL304               SYS_IL0000113683C00003$$     Y     ←システムにて自動で作成される

SQL>

DBA_LOBSと結合してLOBデータのサイズを求める

またDBA_LOBSを参照すると、インデックスだけでなくLOBセグメント（CLOBデータが実際に格納される）も確認できます。DBA_SEGMENTSと結合してサイズを調べる事ができます。

select t.OWNER as TABLE_OWNER,t.TABLE_NAME,t.COLUMN_NAME

,s.OWNER as SEGMENT_OWNER,s.SEGMENT_NAME,s.SEGMENT_TYPE

,s.SEGMENT_SUBTYPE,s.BYTES

from DBA_SEGMENTS s

inner join DBA_LOBS t on s.OWNER = s.OWNER

and s.SEGMENT_NAME =

case

when s.SEGMENT_TYPE='LOBINDEX'

then t.INDEX_NAME

when s.SEGMENT_TYPE='LOBSEGMENT'

then t.SEGMENT_NAME

else null

end

where t.OWNER='DABADA' and t.TABLE_NAME='TBL304'

;

select t.OWNER as TABLE_OWNER,t.TABLE_NAME,t.COLUMN_NAME ,s.OWNER as SEGMENT_OWNER,s.SEGMENT_NAME,s.SEGMENT_TYPE ,s.SEGMENT_SUBTYPE,s.BYTES from DBA_SEGMENTS s inner join DBA_LOBS t on s.OWNER = s.OWNER and s.SEGMENT_NAME = case when s.SEGMENT_TYPE='LOBINDEX' then t.INDEX_NAME when s.SEGMENT_TYPE='LOBSEGMENT' then t.SEGMENT_NAME else null end where t.OWNER='DABADA' and t.TABLE_NAME='TBL304' ;

select t.OWNER as TABLE_OWNER,t.TABLE_NAME,t.COLUMN_NAME
,s.OWNER as SEGMENT_OWNER,s.SEGMENT_NAME,s.SEGMENT_TYPE
,s.SEGMENT_SUBTYPE,s.BYTES
from DBA_SEGMENTS s 
inner join DBA_LOBS t on s.OWNER = s.OWNER 
and s.SEGMENT_NAME =
case  
   when s.SEGMENT_TYPE='LOBINDEX'
   then t.INDEX_NAME
   when s.SEGMENT_TYPE='LOBSEGMENT'
   then t.SEGMENT_NAME
   else null
end
where t.OWNER='DABADA' and t.TABLE_NAME='TBL304'
;

LOBデータのサイズ出力例

                                         SEGMENT                                                SEGMENT
TABLE_OWNER    TABLE_NAME COLUMN_NAME    OWNER      SEGMENT_NAME                 SEGMENT_TYPE   SUBTYPE         BYTES
-------------- ---------- -------------- ---------- ---------------------------- -------------- ---------- ----------
DABADA         TBL304     MEMO           DABADA     SYS_IL0000113683C00003$$     LOBINDEX       ASSM            65536
DABADA         TBL304     MEMO           DABADA     SYS_LOB0000113683C00003$$    LOBSEGMENT     SECUREFILE     131072

SQL>

LOBデータのサイズ出力例

set lin 200

set pages 50

COLUMN TABLE_OWNER FORMAT A14

COLUMN TABLE_NAME FORMAT A10

COLUMN COLUMN_NAME FORMAT A14

COLUMN SEGMENT_OWNER FORMAT A10

COLUMN SEGMENT_OWNER HEADING 'SEGMENT|OWNER'

COLUMN SEGMENT_NAME FORMAT A28

COLUMN SEGMENT_TYPE FORMAT A14

COLUMN SEGMENT_SUBTYPE FORMAT A10

COLUMN SEGMENT_SUBTYPE HEADING 'SEGMENT|SUBTYPE'

COLUMN TABLESPACE_NAME FORMAT A10

COLUMN TABLESPACE_NAME HEADING 'TABLESPACE|NAME'

set lin 200 set pages 50 COLUMN TABLE_OWNER FORMAT A14 COLUMN TABLE_NAME FORMAT A10 COLUMN COLUMN_NAME FORMAT A14 COLUMN SEGMENT_OWNER FORMAT A10 COLUMN SEGMENT_OWNER HEADING 'SEGMENT|OWNER' COLUMN SEGMENT_NAME FORMAT A28 COLUMN SEGMENT_TYPE FORMAT A14 COLUMN SEGMENT_SUBTYPE FORMAT A10 COLUMN SEGMENT_SUBTYPE HEADING 'SEGMENT|SUBTYPE' COLUMN TABLESPACE_NAME FORMAT A10 COLUMN TABLESPACE_NAME HEADING 'TABLESPACE|NAME'

set lin 200
set pages 50
COLUMN TABLE_OWNER  FORMAT A14
COLUMN TABLE_NAME  FORMAT A10
COLUMN COLUMN_NAME  FORMAT A14
COLUMN SEGMENT_OWNER  FORMAT A10
COLUMN SEGMENT_OWNER HEADING 'SEGMENT|OWNER'
COLUMN SEGMENT_NAME  FORMAT A28
COLUMN SEGMENT_TYPE  FORMAT A14
COLUMN SEGMENT_SUBTYPE  FORMAT A10
COLUMN SEGMENT_SUBTYPE HEADING 'SEGMENT|SUBTYPE'
COLUMN TABLESPACE_NAME  FORMAT A10
COLUMN TABLESPACE_NAME HEADING 'TABLESPACE|NAME'