Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Sunday, May 20, 2007

Moving domain index to another tablespace

It seems to be very easy task that can be accomplished using rebuild option:
ALTER INDEX MY_INDEX REBUILD 
TABLESPACE NEW_TBS;
We can even do it online for most indexes:
ALTER INDEX MY_INDEX REBUILD 
TABLESPACE NEW_TBS ONLINE;
But trying this for domain index (ex. Spatial Index) causes error:
ALTER INDEX MY_GEOMETRY rebuild tablespace users;
ORA-29871: invalid alter option for a domain index
What is the reson and how to overcome this? Domain index is in fact set of other objects. For spatial index it is table (MDRT_*). Spatial Index can be moved quite easy with
ALTER INDEX MY_GEOMETRY 
REBUILD PARAMETERS('TABLESPACE=NEW_TBS');
Oracle Text CONTEXT index is set of tables:
  • DR$[index_name]$I
  • DR$[index_name]$K
  • DR$[index_name]$N
  • DR$[index_name]$R
Unfortunately to move context index you have to drop and recreate text index. But first storage parameters have to be defined:
begin
   ctx_ddl.create_preference('MY_TEXT_STORE', 'BASIC_STORAGE');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'I_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'K_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'R_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'N_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'I_INDEX_CLAUSE',
       'tablespace NEW_TBS COMPRESS 2');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'P_TABLE_CLAUSE',
       'tablespace NEW_TBS');
end;
/
and then just drop and create index with changed parameters
create index MY_TEXT_I on MY_TAB(text_column) 
indextype is ctxsys.context parameters(
'storage MY_TEXT_STORE');
For more information go to Oracle Text Application Developer's Guide

Cheers, Paweł

No comments:

 

Copyright © Paweł Barut
Printing from DOS to USB Printer