针对多列中搜索前缀的 Oracle 查询优化(用于在键入名字/第二个名称或登录名时自动完成 Ajax)

Question

我们的USR表保存：

fname varchar2
sname varchar2
login varchar2

我尝试在 HTML input 元素上使用 Ajax 实现模糊自动完成。用户可以键入名字或第二个名称或登录名。为了获得更好的用户体验，我还想在空格上提供拆分键入的单词，并搜索两个或三个单词。

首先我调查这个索引：

CREATE INDEX INDEX_USR_FNAME ON USR (UPPER(FNAME) ASC);
...

查询时使用：

select fname, sname, login from USR
  where rownum < 10 and upper(fname) like '&word1%';

如：

TABLE ACCESS BY INDEX ROWID
  INDEX RANGE SCAN

但是当我尝试匹配任何列时：

select fname, sname, login from USR
  where rownum < 10
    and (upper(fname) like '&word1%' or upper(sname) like '&word1%'
                                     or upper(login) like '&word1%');

我得到：

TABLE ACCESS FULL

我也尝试实现查询以匹配 2 个单词：

select fname, sname, login from USR
  where rownum < 10
    and (upper(fname) like '&word1%' or upper(fname) like '&word2%')
    and (upper(sname) like '&word1%' or upper(sname) like '&word2%')
    and (upper(login) like '&word1%' or upper(login) like '&word2%');

和3个字：

select fname, sname, login from USR
  where rownum < 10
    and (upper(fname) like '&word1%' or upper(fname) like '&word2%'
                                     or upper(fname) like '&word3%')
    and (upper(sname) like '&word1%' or upper(sname) like '&word2%'
                                     or upper(sname) like '&word3%')
    and (upper(login) like '&word1%' or upper(login) like '&word2%'
                                     or upper(login) like '&word3%');

我还得到：

TABLE ACCESS FULL

是否可以使用索引代替TABLE ACCESS FULL？

Oracle 是否将索引用于复杂的布尔条件？

Answer 1

数据库引擎在某种情况下经常遇到or问题。 您可以尝试此版本的查询：

select *
from ((select fname, sname, login
       from USR
       where upper(fname) like '&word1%'
      ) union all
      (select fname, sname, login
       from USR
       where upper(sname) like '&word1%'
      ) union all
      (select fname, sname, login
       from USR
       where upper(login) like '&word1%'
      )
     ) t
where rownum < 10;

假设您确实有三个索引，那么每个子查询都应该为条件使用一个索引。 然后，外部查询返回前 9 行。 如果要重复，还可以将rownum条件放在每个子查询中。