优化预言机中的字符串搜索

Question

我有一个视图，其中包含与员工相关的所有数据。它有大约35万条记录。我必须制作名称搜索功能。这将检索与输入的关键字匹配的所有数据。

查询性能非常慢，检索数据需要 15-20 秒。价格-15000

我的查询：

 SELECT            H.PERSON_ID,
                   B.EMPLOYEE_ID,
                   INITCAP(B.FIRST_NAME) EMP_FNAME,
                   INITCAP(B.MIDDLE_NAME) EMP_MNAME,
                   INITCAP(B.LAST_NAME) EMP_LNAME,
                   B.EMPLOYEE_TYPE PERSON_DESC,
                   B.EMPLOYMENT_STATUS STATUS_TYPE,
                   EA.BASE_BRANCH
              FROM EMPLOYEE_BASIC_DTLS   B,
                   EMP_ASSIGNMENT_DTLS_MV EA,
                   EMPLOYEE_HIS_DEPNDENT_TBL  H
             WHERE B.PERSON_ID = EA.PERSON_ID
               AND B.PERSON_ID = H.PERSON_ID
               AND ((UPPER(B.FIRST_NAME) LIKE
                   ('%' || V_SEARCH_PARAM1 || '%')) OR
                   (UPPER(B.MIDDLE_NAME) LIKE
                   ('%' || V_SEARCH_PARAM1 || '%')) OR
                   (UPPER(B.LAST_NAME) LIKE
                   ('%' || V_SEARCH_PARAM1 || '%')))
               AND TRUNC(SYSDATE) BETWEEN EA.EFFECTIVE_START_DATE AND
                   EA.EFFECTIVE_END_DATE
               AND UPPER(H.RELATIONSHIP_CODE) = 'A';

由于EMPLOYEE_BASIC_DTLS是一个视图，所以我不能使用索引。

Answer 1

虽然您

确实不能将索引放在视图上，但您当然可以将索引放在基础表上。 但是，正如@JustinCave所指出的，即使您确实将索引添加到相应的表中，由于使用了LIKE，此查询仍然不会使用它们。 此外，由于UPPER函数应用于FIRST_NAME、MIDDLE_NAME和LAST_NAME列，因此您需要将索引定义为基于函数的索引。例如，如果EMPLOYEE_BASIC_TABLE视图访问的"real"表被称为EMPLOYEES则可以在FIRST_NAME列上定义一个基于函数的索引

CREATE INDEX EMPLOYEES_UPPER_FIRST_NAME ON EMPLOYEES (UPPER(FIRST_NAME));

我建议您考虑是否真的需要 LIKE 比较，因为解决这些问题以获得更好的性能将很困难。

如果您想调查 Oracle 文本索引，可以在此处找到文档。我想你会发现它更适合文档或文档片段索引，但也许它会给你一些想法。

分享和享受。

Answer 2

由于可以查找任何名称或名称的任何部分，因此无法创建包含要事先搜索的值的索引。所以这在这里对你没有帮助。Oracle 将执行全表扫描以检查每个字符串是否匹配。

不过，您可以做的是加快扫描速度。

例如，您可以通过/*+parallel（EMPLOYEE_BASIC_TABLE，4）*/并行化全表扫描来加快全表扫描的速度。（这是我在这里的建议。

或者，您可以通过每列有一个索引来避免全表扫描，因为您知道有许多重复使用的名称，因此每个名称只扫描一次。然后，您将按照 Bob Jarvis 的建议在基础表上使用基于函数的键，因为您在任何名称上使用上层函数。最快的是组合索引：

create bitmap index idx_name_search on EMPLOYEE_BASIC_TABLE (upper(first_name || '|' || middle_name || '|' || last_name))

所以只有一个索引可以查找。（当然，您必须在查询中完全使用此表达式：WHERE upper（first_name ||'|' ||middle_name ||'|' ||last_name）如"%JOHN%"。但是，您仍然不知道将提前搜索什么，并且由于"%JOHN%"可能仅影响 2% 的表数据，因此"%E%"可能会影响 80%。优化器永远不会知道。您至少可以猜测并且必须使用不同的选择语句，例如，当搜索字符串包含至少三个字母时，您将使用完整的表提示，另一个带有索引提示，否则您将使用。

你

看，你越想，这就会变得相当复杂。我建议先尝试并行提示。也许这已经足够加快了速度。