VARCHAR2 LIKE 搜尋的索引策略

設想

數據庫

• 甲骨文數據庫 11g

桌子

• 使用者

  • 身份證號，PK
  • CODE 編號，索引，唯一，不為空
  • FIRST_NAME VARCHAR(100)，不為空
  • LAST_NAME VARCHAR(100)，不為空
  • 電子郵件 VARCHAR(50)，不為空

搜尋

• 除 ID 和 CODE 外，所有其他列都是大小寫混合的。
• 我需要通過字元串搜尋使用者。搜尋應該不區分大小寫。
• 該字元串是部分值。（例如：“foo”搜尋“foobar”）
• 模式可以出現在列上值的任何部分。（例如：‘ob’ 代表 fo ob ar）
• 該模式可以出現在任何表格欄位中。

SQL

SELECT *
FROM users u
WHERE 
u.code = COALESCE( to_number( REGEXP_REPLACE(:pattern, '[^0-9]+', '') ), -1)
OR upper(u.email) LIKE upper(:pattern)
OR upper(u.last_name) LIKE upper(:pattern) 
OR upper(u.first_name) LIKE upper(:pattern) 

查詢計劃

• 結果顯示帶有 OR 過濾謂詞的全表掃描

問題

1. 優化此查詢的最佳策略是什麼？我們能以某種方式降低成本嗎？
2. 在 VARCHAR2 列上使用索引是否有益？即使使用 LIKE 運算符進行搜尋？
3. 我們可以使用不同的 SQL 來利用索引並在最後加入結果嗎？

文本搜尋引擎

• 我知道我們可以使用文本搜尋引擎來處理這個問題。但是讓我們討論一下使用數據庫可以做什麼。

相關連結

• 虛擬列（建議：@Michael Kutz）：ToadWorld - Oracle 虛擬列和索引
• Oracle Text（建議：@Balazs Papp）：Oracle - Oracle Text 開髮指南
• Oracle Text - 檢查是否已安裝：Oracle 論壇主題
• Oracle 文本 - 避免碎片化：Oracle - 文本索引維護製度

不，忘記LIKE操作員，條件first_name LIKE '%name%'永遠不會有效。正常索引不能用於條件的範圍掃描first_name LIKE '%name%'。如果first_name是索引，則索引全掃描是數據庫可以使用的最佳選擇。而且您仍然需要處理區分大小寫的問題。

（索引範圍掃描可用於條件 as first_name like 'name%'，其中值的開頭是固定的）

這是 Oracle Text 的任務。

不，忘記虛擬列，你不能在虛擬列上創建文本索引，如果你嘗試一下，你會得到：

DRG-11304: function-based indexes are not supported by this indextype

我已經回答了一個類似的問題：Oracle “matches” multiple columns

但這裡又是一個例子：

create table t1
(
 id number primary key,
 code number not null,
 first_name varchar2(100 char) not null,
 last_name varchar2(100 char) not null,
 email varchar2(50 char) not null
);

insert into t1 values (1, 111, 'Balazs', 'Papp', 'dba@stackexchange.com');
insert into t1 values (2, 222, 'linux', 'unil', 'index@stackexchange.com');
commit;

begin
ctx_ddl.create_preference('t1_multi_column_datastore', 'multi_column_datastore');
ctx_ddl.set_attribute('t1_multi_column_datastore', 'columns', 'code, first_name, last_name, email');
ctx_ddl.create_preference('t1_lexer', 'basic_lexer');
ctx_ddl.set_attribute('t1_lexer', 'mixed_case','no');
end;
/

create index i1 on t1 (first_name) indextype is ctxsys.context
 parameters ('lexer t1_lexer datastore t1_multi_column_datastore');

然後查詢：

SQL> select * from t1 where contains (first_name, '%STACK%') > 0;

       ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
        1        111 Balazs     Papp       dba@stackexchange.com
        2        222 linux      unil       index@stackexchange.com

SQL> select * from t1 where contains (first_name, '%11%') > 0;

       ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
        1        111 Balazs     Papp       dba@stackexchange.com

SQL> select * from t1 where contains (first_name, '%Uni%') > 0;

       ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
        2        222 linux      unil       index@stackexchange.com

請注意我沒有指定所有列，仍然是在所有列中搜尋的查詢（在 中列舉multi_column_datastore）。我first_name在索引和查詢中指定，但查詢返回的記錄與email, codeorlast_name列匹配。

請注意，我沒有在查詢中指定與區分大小寫相關的任何內容。它是使用mixed_case=no屬性指定的（預設情況下，Oracle Text 單詞搜尋不區分大小寫：Case-Sensitive Searching）。

查詢使用索引：

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  a4vw67psmh4x3, child number 0
-------------------------------------
select * from t1 where contains (first_name, '%Uni%') > 0

Plan hash value: 1218792127

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   544 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | I1   |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("CTXSYS"."CONTAINS"("FIRST_NAME",'%Uni%')>0)

引用自：https://dba.stackexchange.com/questions/211676