How to Index on a CLOB column?
699346Oct 16 2009 — edited Oct 19 2009I have a need to index on a CLOB column. Initial attempts to index will result in ORA-02373.
In research, I was able to find an that a LOB column could be moved to another tablespace then indexed.
So I plan to use these statements (below), to create a text index on CLOB column STATEMENT_TEXT.
Questions: What storage should be used for the CLOB column?
What index type is needed to create a text index on the CLOB column?
ALTER TABLE ANY_STATEMENT MOVE
TABLESPACE INFOWH2 STORAGE(INITIAL 4096K)
LOB (STATEMENT_TEXT) STORE lobsegment
(TABLESPACE INFOWH2 STORAGE (INITIAL 4096K));
Then:
CREATE INDEX IDXANYSTMTTEXTBINARY
ON LOB(STATEMENT_TEXT)
INDEXTYPE IS CTXSYS.CONTEXT;
Here is the table in part:
CREATE TABLE ANY_STATEMENT
(
ANY_STATEMENT_ID CHAR(18 BYTE) NOT NULL,
CODE VARCHAR2(4 BYTE),
STATEMENT_TYPE_ID_FK CHAR(18 BYTE) NOT NULL,
UPDATED_BY VARCHAR2(50 BYTE) DEFAULT USER,
LAST_DATE_UPDATED DATE DEFAULT SYSDATE,
DESTINATION_ID_FK CHAR(18 BYTE),
LANGUAGE_ID_FK CHAR(18 BYTE) NOT NULL,
SUPERCEDED_DATE DATE,
FORMULA_REFERENCE NUMBER,
FORMULA_SERIAL VARCHAR2(15 BYTE),
CREATED_BY_ID_FK CHAR(18 BYTE) DEFAULT USER NOT NULL,
DATE_CREATED DATE DEFAULT SYSDATE NOT NULL,
ON_HOLD CHAR(1 BYTE),
TEMP_REFERENCE_NUMBER NUMBER,
APPLICATION_NOTES VARCHAR2(2048 BYTE),
HOLD_REASON VARCHAR2(1024 BYTE),
STATEMENT_TEXT CLOB,
REASON CLOB
)
TABLESPACE INFOWH
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 112K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCOMPRESS
LOB (STATEMENT_TEXT) STORE AS
( TABLESPACE INFOWH
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
NOCACHE
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
LOB (REASON) STORE AS
( TABLESPACE INFOWH
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
NOCACHE
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING;