64位環境AUTO_FILTER和INSO_FILTER失效的問題

從10g開始，Oracle推出了AUTO_FILTER來替代INSO_FILTER，不過根據Oracle的描述，AUTO_FILTER更像是在INSO_FILTER上增加了一些自動的判斷而已。因此，可以認為二者是相同的問題。

 

 

和EYGLE聊天時他提到，碰到了一個奇怪的現象，11g的Linux平臺64位Oracle環境建立全文索引後，索引為空。同樣的問題在32位環境中沒有問題。

看一個簡單的例子：

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> HOST more /home/oracle/a.txt    
You remove the substitute key columns for a table by specifying NULL for the
column_list or column_table parameter in the SET_KEY_COLUMNS procedure in
the DBMS_APPLY_ADM package. If the table has a primary key, then the table's primary
key is used by any apply process for local changes to the database after you remove
the substitue primary key.

SQL> CREATE TABLE T_FILTER (ID NUMBER PRIMARY KEY, DOC VARCHAR2(30));

Table created.

SQL> INSERT INTO T_FILTER VALUES (1, 'a.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_FILTER_DOC ON T_FILTER (DOC) INDEXTYPE IS CTXSYS.CONTEXT
  2  PARAMETERS('DATASTORE TEST_FILE FILTER CTXSYS.AUTO_FILTER');

Index created.

SQL> SELECT COUNT(*) FROM DR$IND_T_FILTER_DOC$I;

  COUNT(*)
----------
        24

SQL> SELECT TOKEN_TEXT
  2  FROM DR$IND_T_FILTER_DOC$I;

TOKEN_TEXT
----------------------------------------------------------------
ADM
AFTER
APPLY
CHANGES
COLUMN
COLUMNS
DATABASE
DBMS
KEY
LIST
LOCAL
NULL
PACKAGE
PARAMETER
PRIMARY
PROCEDURE
PROCESS
REMOVE
SET
SPECIFYING
SUBSTITUE
SUBSTITUTE
TABLE
USED

24 rows selected.

可以看到，32位Linux上沒有問題，而在64位Linux上則出現下面的問題：

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> HOST more /opt/oracle/a.txt
You remove the substitute key columns for a table by specifying NULL for the
column_list or column_table parameter in the SET_KEY_COLUMNS procedure in
the DBMS_APPLY_ADM package. If the table has a primary key, then the table's primary
key is used by any apply process for local changes to the database after you remove
the substitute primary key.

SQL> CREATE TABLE T_FILTER (ID NUMBER PRIMARY KEY, DOC VARCHAR2(30));

Table created.

SQL> INSERT INTO T_FILTER VALUES (1, 'a.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', '/opt/oracle');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> CREATE INDEX IND_T_FILTER_DOC ON T_FILTER (DOC) INDEXTYPE IS CTXSYS.CONTEXT
  2  PARAMETERS('DATASTORE TEST_FILE FILTER CTXSYS.AUTO_FILTER');

Index created.

SQL> SELECT COUNT(*) FROM DR$IND_T_FILTER_DOC$I;

  COUNT(*)
----------
         0

查詢了一下metalik，感覺和文件Bug No. 7421367描述很接近，對於64位的環境，Oracle安裝的時候漏掉了一些用來過濾文件的driver，導致INSO_FILTER和AUTO_FILTER無效。

在Solaris上的64位Oracle11g，則沒有發現這個問題。