Home » SQL & PL/SQL » SQL & PL/SQL » Index (Oracle 9i)
Index [message #336567] Mon, 28 July 2008 03:09 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Dear All,
is it possible to creat an index on long column.?
Re: Index [message #336568 is a reply to message #336567] Mon, 28 July 2008 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: Index [message #336571 is a reply to message #336567] Mon, 28 July 2008 03:16 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
is it possible to create index on lob column?
Can u please explain me why ?
Re: Index [message #336586 is a reply to message #336567] Mon, 28 July 2008 05:24 Go to previous messageGo to next message
orasuman7
Messages: 4
Registered: July 2008
Location: BANGALORE
Junior Member
You cannot use the TO_LOB function to convert a LONG column to a LOB column in the subquery of a CREATE TABLE ... AS SELECT statement if you are creating an index-organized table. Instead, create the index-organized table without the LONG column, and then use the TO_LOB function in an INSERT ... AS SELECT statement.

Re: Index [message #336587 is a reply to message #336571] Mon, 28 July 2008 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just try it:
SQL> create table t (col clob);

Table created.

SQL> create index t_i on t(col);
create index t_i on t(col)
                      *
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

Regards
Michel
Re: Index [message #336639 is a reply to message #336587] Mon, 28 July 2008 08:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> create table t (col clob)
  2  /

Table created.

SCOTT@orcl_11g> insert into t values
  2  ('Yes, you can create a context index on a clob column and
  3  use that index for Oracle Text searches.')
  4  /

1 row created.

SCOTT@orcl_11g> create index t_i on t(col) indextype is ctxsys.context
  2  /

Index created.

SCOTT@orcl_11g> select * from t where contains (col, 'Oracle') > 0
  2  /

COL
--------------------------------------------------------------------------------
Yes, you can create a context index on a clob column and
use that index for Oracle Text searches.


SCOTT@orcl_11g> 

Re: Index [message #336641 is a reply to message #336639] Mon, 28 July 2008 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create index t_i on t(col) indextype is ctxsys.context
  2  /
create index t_i on t(col) indextype is ctxsys.context
                                               *
ERROR at line 1:
ORA-29833: indextype does not exist

Not with default options.

Regards
Michel
Re: Index [message #336644 is a reply to message #336641] Mon, 28 July 2008 08:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Mon, 28 July 2008 06:48
SQL> create index t_i on t(col) indextype is ctxsys.context
  2  /
create index t_i on t(col) indextype is ctxsys.context
                                               *
ERROR at line 1:
ORA-29833: indextype does not exist

Not with default options.

Regards
Michel



"Default options"? I guess if you are on a production system, Oracle Text is a separate license. If you are using even Express Edition on a home computer, you get Oracle Text with it. I can't imagine a system without it. You have definitely been deprived of a lot of functionality. On some home systems it is simply a case of not having run the scripts necessary to create the ctxsys schema and unlock it or not having granted the ctxapp role to the user attempting to create such an index.


Re: Index [message #336660 is a reply to message #336644] Mon, 28 July 2008 09:52 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes this is exactly what I meant, sorry for the short cut.

Regards
Michel
Previous Topic: select clob
Next Topic: Reconciliation of Transactions
Goto Forum:
  


Current Time: Sat Dec 10 11:04:44 CST 2016

Total time taken to generate the page: 0.06840 seconds