【Oracle】Oracle Textを用いたフルテキストインデックスで表領域を指定して索引を作る

インデックスを作る際に、普通だったら以下のように簡単に表領域を指定して作れる

CREATE INDEX hoge ON table (id) TABLESPACE hoge_index

しかし、CONTEXT索引や CTXCAT索引などといったいわゆるOracle Textの索引を作る際にはそうは問屋がおろさない。
以下のようにドメイン索引を作ろうとすると無効なオプションだと怒られちゃいます。

SQL>create index hr.idx_jobs on hr.jobs (JOB_TITLE) indextype is ctxsys.context tablespace user_index;
create index hr.idx_jobs on hr.jobs (JOB_TITLE) indextype is ctxsys.context tablespace user_index
*
ERROR at line 1:
ORA-29850: invalid option for creation of domain indexes

表領域を指定するにはどうすれば。。。
今回はそんなお話。
↓へ続く

とりあえず今回は日本語の索引を作ると仮定してレクサーもついでに指定してます。

・前準備

最初にカスタムプリファレンスを作成する

SQL> begin
ctx_ddl.create_preference(‘HR.hr_lexer’,’JAPANESE_VGRAM_LEXER’);
ctx_ddl.create_preference(‘HR.hr_storage’,’BASIC_STORAGE’);
ctx_ddl.set_attribute(‘HR.hr_storage’, ‘I_TABLE_CLAUSE’, ‘tablespace USER_INDEX’);
ctx_ddl.set_attribute(‘HR.hr_storage’, ‘K_TABLE_CLAUSE’, ‘tablespace USER_INDEX’);
ctx_ddl.set_attribute(‘HR.hr_storage’, ‘R_TABLE_CLAUSE’, ‘tablespace USER_INDEX LOB(DATA) STORE AS (CACHE)’);
ctx_ddl.set_attribute(‘HR.hr_storage’, ‘N_TABLE_CLAUSE’, ‘tablespace USER_INDEX’);
ctx_ddl.set_attribute(‘HR.hr_storage’, ‘I_INDEX_CLAUSE’, ‘tablespace USER_INDEX COMPRESS 2’);
end;
/

・ポイント解説

>ctx_ddl.create_preference(‘HR.hr_lexer’,’JAPANESE_VGRAM_LEXER’);
日本語の索引付けとして関連つけてます
日本語レクサーにはJAPANESE_VGRAM_LEXERを使用。
>ctx_ddl.create_preference(‘HR.hr_storage’,’BASIC_STORAGE’);
今回の肝。
hr_storageという記憶域型のプリファレンスに表領域の指定をセットしていきます。
>ctx_ddl.set_attribute(‘HR.hr_storage’, ‘I_TABLE_CLAUSE’, ‘tablespace USER_INDEX’);
>ctx_ddl.set_attribute(‘HR.hr_storage’, ‘K_TABLE_CLAUSE’, ‘tablespace USER_INDEX’);
>ctx_ddl.set_attribute(‘HR.hr_storage’, ‘R_TABLE_CLAUSE’, ‘tablespace USER_INDEX LOB(DATA) STORE AS (CACHE)’);
>ctx_ddl.set_attribute(‘HR.hr_storage’, ‘N_TABLE_CLAUSE’, ‘tablespace USER_INDEX’);
>ctx_ddl.set_attribute(‘HR.hr_storage’, ‘I_INDEX_CLAUSE’, ‘tablespace USER_INDEX COMPRESS 2’);
tablespaceを3つ目の引数で指定します。
ここで重要なのがR_TABLE_CLAUSEとI_INDEX_CLAUSEのLOB(DATA) STORE AS (CACHE)’やCOMPRESS 2の指定について。
公式マニュアルによると、デフォルトに設定してるやつを設定しておいたほうが
パフォーマンスが上がるらしいのできちんと指定しておく。
上書きされちゃうのでちゃんと書いておこう！

・索引作成

前準備ができたところでいざ作成！

SQL> CREATE INDEX hr.idx_jobs ON hr.jobs ( job_title )
INDEXTYPE IS CTXSYS.CONTEXT ONLINE
PARAMETERS (‘
STORAGE hr_storage
LEXER hr_lexer
SYNC (EVERY “FREQ=MINUTELY;INTERVAL=15”)
‘);

PARAMETERS句で作ったプリファレンスなどを指定しています。
SYNCについてはこのへんみてください。
確認してみるとちゃんと変わってます。

SQL> select segment_name, segment_type, tablespace_name from dba_segments where segment_name like ‘%IDX_JOBS%’
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------------------------------------------–
DR$IDX_JOBS$I TABLE USER_INDEX
DR$IDX_JOBS$R TABLE USER_INDEX
DR$IDX_JOBS$X INDEX USER_INDEX

あとは以下を定期的に実行してメンテナンスを行なってください。

SQL> execute ctx_ddl.optimize_index(‘HR.IDX_JOBS’,’FULL’);

・余談

ちなみにこれ、索引を作った後にSTORAGE句などを指定してリビルドすると以下のように怒られる

SQL> ALTER INDEX HR.IDX_JOBS REBUILD
PARAMETERS (‘
STORAGE hr_storage
LEXER hr_lexer
SYNC (EVERY “FREQ=MINUTELY;INTERVAL=15”)
‘)
ONLINE;
ALTER INDEX HR.IDX_JOBS REBUILD
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX IDX_JOBS failed
DRG-11000: invalid keyword STORAGE

不思議ですが、リビルドするときはREPLACE句をつけてあげましょう。

SQL> ALTER INDEX HR.IDX_JOBS REBUILD
PARAMETERS (‘
REPLACE
STORAGE hr_storage
LEXER hr_lexer
SYNC (EVERY “FREQ=MINUTELY;INTERVAL=15”)
‘)
8 ONLINE;
Index altered.