Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to Index on a CLOB column?

699346Oct 16 2009 — edited Oct 19 2009
I 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;
This post has been answered by Satish Kandi on Oct 17 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 16 2009
Added on Oct 16 2009
2 comments
3,011 views