Home » Server Options » Text & interMedia » CLOB Index creation
CLOB Index creation [message #23844] Thu, 09 January 2003 15:11 Go to next message
Matthew Iskra
Messages: 16
Registered: April 2002
Location: Sacramento CA USA
Junior Member
I am old dba, but new to using Oracle CLOB's.

I am trying to create an index on a clob. My SQL is as such.

create index scsd.test_clob_idx
on scsd.test_clob(xml_stuff)
tablespace test_clob
INDEXTYPE IS ctxsys.context
/

The SQL will not let me specify a tablespace. When I don't, the index is put in the default tablespace for the use which is a bad thing.

How can I get a CLOB index to go to a tablespace that I define?

Thank you for your time and experience.

--Matthew Iskra
Re: CLOB Index creation [message #23849 is a reply to message #23844] Thu, 09 January 2003 15:47 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
It is not a CLOB index - it is a Text (or interMedia) index on a CLOB column.

You have to create a storage preference prior to the index creation and then reference the preference when creating the index. See the Oracle Text docs for more info.
CLOB table and index script [message #23851 is a reply to message #23849] Thu, 09 January 2003 16:54 Go to previous messageGo to next message
Matthew Iskra
Messages: 16
Registered: April 2002
Location: Sacramento CA USA
Junior Member
Thank you Mr. Barry. I found the reference.

What still puzzles me is that the CLOB and it's index must be attached, or in the same tablespace, if I use the storage preference. This seems a rather klunky way to do things. This also means that I cannot create the index without rebuilding the table. This is not acceptable for many production environments.

So, will the following script be correct?

-- cut here --

CREATE TABLE test_clob
(
id NUMBER(12) NOT NULL,
notes CLOB DEFAULT NULL,
CONSTRAINT pk_test_clob PRIMARY KEY (id)
)
TABLESPACE test_clob
LOB("test_clob") STORE AS
(
TABLESPACE test_clob
);

CREATE INDEX test_clob_idx
ON test_clob(notes)
INDEXTYPE IS ctxsys.context;
Re: CLOB table and index script [message #23853 is a reply to message #23849] Thu, 09 January 2003 17:44 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Why do you think they have to be in the same tablespace? You can specify any TS you want in the storage preference of the CTXSYS index.

By preference, I'm referring to:

ctx_ddl.create_preference('storage_pref', 'basic_storage');
ctx_ddl.set_attribute('storage_pref', 'i_table_clause', 'tablespace some_tablespace');
ctx_ddl.set_attribute('storage_pref', 'k_table_clause', 'tablespace some_tablespace');
ctx_ddl.set_attribute('storage_pref', 'r_table_clause', 'tablespace some_tablespace');
ctx_ddl.set_attribute('storage_pref', 'n_table_clause', 'tablespace some_tablespace');
ctx_ddl.set_attribute('storage_pref', 'i_index_clause', 'tablespace some_tablespace');


And then:

CREATE INDEX test_clob_idx
  ON test_clob(notes)
  INDEXTYPE IS ctxsys.context
  parameters ('storage storage_pref');
Previous Topic: All about oracle intermedia
Next Topic: object CTXSYS.CONTAINS is invalid
Goto Forum:
  


Current Time: Thu Mar 28 05:46:04 CDT 2024