OraFAQ Forum: Server Administration » Index On CLOB Column

Home » RDBMS Server » Server Administration » Index On CLOB Column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Index On CLOB Column [message #51476]

Fri, 24 May 2002 06:37

Kasthuri Ilankamban
Messages: 1
Registered: May 2002

Junior Member

I have a question about creating index on CLOB
column.
When I try to create index on CLOB, I get:

ERROR at line 1:
ORA-02327: cannot create index on expression
with
datatype LOB

We don't have license for Oracle Context. What
kind of
index can I create on CLOB datatype?

Thank you,
Kasthuri

Report message to a moderator

Re: Index On CLOB Column [message #51480 is a reply to message #51476]

Fri, 24 May 2002 06:57

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Please post the exact command you are using.

BTW -- from the README:
Oracle Corporation intends to desupport the LOB index clause in a future release of Oracle. DO NOT put the LOB index in a different tablespace than the LOB data. In a future Oracle release, if the LOB index is in a different tablespace than the LOB data, export/import will coalesce the LOB index with the LOB data.

Report message to a moderator

Re: Index On CLOB Column [message #51482 is a reply to message #51480]

Fri, 24 May 2002 09:53

orafaq@orafaq.net
Messages: 2
Registered: March 2002

Junior Member

The create index command I used is:

create index lobtable_idx on lobtable ( text_doc);

text_doc is a clob column with text data.

Thanks,
Kasthuri

Report message to a moderator

Re: Index On CLOB Column [message #51484 is a reply to message #51482]

Fri, 24 May 2002 10:30

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

if you are actually looking for CONTEXT OPTIONS
the following link will be helpful for you.

http://www.orafaq.net/faqctx.htm

else

When a table is created with a LOB column, Oracle automatically creates an index for the LOB locator. This auto-created index, which has a name of the form SYS_IL<#>$, takes on the tablespace default storage parameters if an index clause with a storage clause is not supplied in the lob clause of the CREATE TABLE command.
SQL> create table emp_lob (empno number, resume clob);

Table created.

SQL> insert into emp_lob values (2000, 'Frank Naude''s resume...');

1 row created.

1* select index_name,index_type,table_name from user_indexes
SQL> /

INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
SYS_IL0000028217C00002$$ LOB EMP_LOB

Report message to a moderator

Previous Topic:	Re: job log
Next Topic:	Re: Need OCP 8i DBA Cheet sheets

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 16 18:17:55 CDT 2024