oracle全文索引之datastore_3_FILE_DATASTORE

DATASTORE是FILE_DATASTORE的測試[@more@][oracle@rhel140 ~]$ cat OUTPUT.TXT
ORACLE
oracle

[oracle@rhel140 LOG]$ cat EMPXT_000_2012_2680.LOG
SOURCE
ABC


SQL> conn myuser/myuser
Connected.
SQL> CREATE TABLE T (ID NUMBER, DOCS VARCHAR2(1000));

Table created.

SQL> INSERT INTO T VALUES (1, 'OUTPUT.TXT');

1 row created.

SQL> commit;

Commit complete.


SQL> BEGIN
2 CTX_DDL.CREATE_PREFERENCE('TEST_FILE', 'FILE_DATASTORE');
3 CTX_DDL.SET_ATTRIBUTE('TEST_FILE', 'PATH', '/home/oracle');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL> CREATE INDEX IND_T_DOCS ON T (DOCS) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('DATASTORE TEST_FILE'); 2

Index created.

SQL> SELECT * FROM T WHERE CONTAINS(DOCS, 'ORACLE') > 0;

ID DOCS
---- --------------------
1 OUTPUT.TXT


需要注意的是在建立PREFERENCE的PATH屬性時不能用客戶端的目錄和檔案，一定是伺服器端的目錄和檔案，
我用WINDOWS的目錄時，在dr$index_error中就會出現下面的錯誤
SQL> select * from ctxsys.dr$index_error;

ERR_IDX_ID ERR_TIMES ERR_TEXTKEY
---------- --------- ------------------
ERR_TEXT
--------------------------------------------------------------------------------
1181 13-MAY-09 AAAM52AAEAAAAGkAAA
DRG-11513: unable to open or write to file /home/oracle/OUTPUT.TXT


SQL> INSERT INTO T VALUES (2, 'LOG/EMPXT_000_2012_2680.LOG');

1 row created.

SQL> commit;

Commit complete.

SQL> exec CTX_DDL.SYNC_INDEX('IND_T_DOCS');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T WHERE CONTAINS(DOCS, 'SOURCE') > 0;

no rows selected

Oracle似乎只支援在FILE_DATASTORE中設定的路徑中進行搜尋，即使像上面這個例子中第2條記錄，文件儲存在

指定路徑下的子目錄中，這種情況也是沒有辦法進行索引的。

如果將檔案位置從/home/oracle/LOG挪到/home/oracle目錄下，則可以進行索引：

SQL> host mv /home/oracle/LOG/EMPXT_000_2012_2680.LOG /home/oracle

SQL> UPDATE T SET DOCS = 'EMPXT_000_2012_2680.LOG' WHERE ID = 2;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> exec CTX_DDL.SYNC_INDEX('IND_T_DOCS');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T WHERE CONTAINS(DOCS, 'SOURCE') > 0;

ID
----------
DOCS
--------------------------------------------------------------------------------
2
EMPXT_000_2012_2680.LOG

測試還發現，只要將檔案置於/home/oracle目錄下即可，即使DOCS中儲存的檔案位置是子目錄的資訊。
下面不改變檔案的儲存位置，只是修改儲存在資料庫中的文件的地址：

SQL> UPDATE T SET DOCS = 'LOG/EMPXT_000_2012_2680.LOG' WHERE ID = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> exec CTX_DDL.SYNC_INDEX('IND_T_DOCS');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T WHERE CONTAINS(DOCS, 'SOURCE') > 0;

ID
----------
DOCS
--------------------------------------------------------------------------------
2
LOG/EMPXT_000_2012_2680.LOG

看yangtingkun的實驗是可以在一個PATH裡指定多少目錄，但我試了是不行的，不知是為什麼

SQL> host mv /home/oracle/EMPXT_000_2012_2680.LOG /home/oracle/LOG/EMPXT_000_2012_2680.LOG

SQL> exec CTX_DDL.DROP_PREFERENCE('TEST_FILE');

PL/SQL procedure successfully completed.

SQL> BEGIN
2 CTX_DDL.CREATE_PREFERENCE('TEST_FILE', 'FILE_DATASTORE')
3 CTX_DDL.CREATE_PREFERENCE('TEST_FILE', 'FILE_DATASTORE')


SQL> BEGIN
2 CTX_DDL.CREATE_PREFERENCE('TEST_FILE', 'FILE_DATASTORE');
3 CTX_DDL.SET_ATTRIBUTE('TEST_FILE', 'PATH', '/home/oracle;/home/oracle/LOG');
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> DROP INDEX IND_T_DOCS;

Index dropped.

SQL> CREATE INDEX IND_T_DOCS ON T (DOCS) INDEXTYPE IS CTXSYS.CONTEXT
2 PARAMETERS ('DATASTORE TEST_FILE');

Index created.

SQL> SELECT * FROM T WHERE CONTAINS(DOCS, 'ORACLE') > 0;

no rows selected

SQL> SELECT * FROM T WHERE CONTAINS(DOCS, 'SOURCE') > 0;

no rows selected

表ctxsys.dr$index_error的錯誤如下，就是找不到目錄

[oracle@rhel140 LOG]$ sqlplus ctxsys/ctxsys

SQL> select * from ctxsys.dr$index_error;

ERR_IDX_ID ERR_TIMES ERR_TEXTKEY
---------- --------- ------------------
ERR_TEXT
--------------------------------------------------------------------------------
1261 14-MAY-09 AAAM6dAAEAAAAG+AAA
DRG-11513: unable to open or write to file /home/oracle;/home/oracle/LOG/OUTPUT.
TXT

1261 14-MAY-09 AAAM6dAAEAAAAG/AAA
DRG-11513: unable to open or write to file /home/oracle;/home/oracle/LOG/EMPXT_0
00_2012_2680.LOG