Oracle 使用索引类型上下文的性能问题

嗨，汤姆，

例如，我有一个表，其中将有数百万条记录。

  
CREATE TABLE employee
       (ID NUMBER NOT NULL ENABLE, 
 EMPLOYEE_NUMBER VARCHAR2(100 CHAR), 
 FULLNAME VARCHAR2(100 CHAR) NOT NULL ENABLE, 
 GENDER NUMBER(1,0), 
 CITY_NAME VARCHAR2(100 CHAR),
 FULL_TEXT_SEARCH CLOB);

FULL_TEXT_SEARCH是我在其中连接了EMPLOYEE_NUMBER，FIRSTNAME和CITY_NAME的列。

我已经将subtring_index属性设置为，

BEGIN
    ctx_ddl.set_attribute('CTXSYS.BASIC_WORDLIST', 'SUBSTRING_INDEX', 'TRUE');
END;

并在其上创建了上下文索引，定义如下

CREATE INDEX idx_employee_txt ON employee (full_text_search)
INDEXTYPE IS ctxsys.context PARAMETERS ( 'wordlist CTXSYS.BASIC_WORDLIST MEMORY 50M SYNC(ON COMMIT)' );

现在，我正在测试仅使用19000个注册表的查询，并且花了6.739秒才能获得结果 (10458注册表与过滤器匹配)，这里的查询

SELECT
    COUNT(1) OVER() AS totalRegistries,
    emp.ID,
    emp.EMPLOYEE_NUMBER,
    emp.FULLNAME,
    emp.GENDER,
    emp.CITY_NAME
FROM
    employee emp
WHERE
    CONTAINS(emp.full_text_search, '%001%') > 0
ORDER BY emp.FULLNAME ASC NULLS LAST
OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY;

如果我使用像 '% 001%'，它需要0.085秒。

我做错什么了吗？我认为仅19000注册管理机构就需要花费很多时间，如何改进我的实施以获得更好的性能？

包含查询的解释计划

操作
对象名称选项字节基数成本CPU_COST IO_COST
SELECT语句
1625 1 2 36093185 1
视图
SYS.null 1625 1 2 36093185 1
过滤谓词
和
从 $ _ 子查询 $ _ 002。行数 <= 0 50
从 $ _ 子查询 $ _ 002。行数> 0
窗口
排序297 1 2 36093185 1
表访问
公民按索引ROWID 297 1
域索引
Idx _ 公民 _ txt 1
访问谓词
CTXSYS.CONTAINS(CTZ.FULL_TEXT_SEARCH，'% 001%')>0

索引 (不仅仅是上下文索引) 的响应时间通常与 * 返回 * 给客户端的数据量成比例。

例如，如果您的employee表有10亿条记录并且仍然返回10458行，则上下文索引仍将为6秒，标准将 * lot * 更长。

要记住的一件事是，子字符串索引恰好是，即 * 所有 * 子字符串可能。

你可能想尝试几件事。

1) 选择人们可以搜索的最小/最大长度，例如

begin 
ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST'); 
ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH', '4');
ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', '10');
ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
end;

因此，不包括少于4个字符的索引条目，并且您的应用程序不会让它们搜索

2) 看看通配符索引是否有帮助

begin
     ctx_ddl.create_preference('mywordlist','BASIC_WORDLIST');
     ctx_ddl.set_attribute('mywordlist','WILDCARD_INDEX','TRUE');
end;

这些的可用性取决于版本。