如何避免使用 LIKE 运算符进行全表扫描

Question

我在PL/SQL预言机中使用不同的正确查询更改查询时遇到问题。当前查询 ：

SELECT MAX (workzone)
           FROM sccd_device_uim_tab
          WHERE NAME LIKE 18075009 || '%';

我的客户端需要修改查询，因为：

Full table with a % should definitely be avoided.

这个问题有什么解决方案？

提前致谢

---

从包添加查询

PROCEDURE sccd_get_impactservice_manual (
      in_faultid          IN       VARCHAR2,
      in_deviceid         IN       VARCHAR2,
      in_status           IN       VARCHAR2,
      in_opendate         IN       DATE,
      in_closedate        IN       DATE,
      out_impact_result   OUT      tcur,
      out_count_service   OUT      NUMBER,
      out_workzone        OUT      VARCHAR2,
      p_ret_char          OUT      VARCHAR2,
      p_ret_number        OUT      NUMBER,
      p_ret_msg           OUT      VARCHAR2
   )
   IS
   BEGIN
      SELECT orauser.ossa_get_sto_from_device (in_deviceid)
        INTO out_workzone
        FROM DUAL;
      IF out_workzone IS NULL
      THEN
         SELECT MAX (workzone)
           INTO out_workzone
           FROM sccd_device_uim_tab
          WHERE NAME = in_deviceid; --//this is I try to solve
          --WHERE NAME LIKE in_deviceid || '%'; //this is the current issue
      END IF;

Answer 1

其中名称像in_deviceid ||'%';

绝对应该避免使用带有 % 的完整表。

你错了。当您使用 LIKE 运算符作为'STRING-%'时，如果列上有任何索引，Oracle 将执行索引扫描。由于统计信息是最新的，您应该不会看到全表扫描。

让我们看一个测试用例：

SQL> CREATE TABLE emp_new AS SELECT ename FROM emp;
Table created.
SQL>
SQL> EXEC DBMS_STATS.gather_table_stats('LALIT', 'EMP_NEW');
PL/SQL procedure successfully completed.
SQL>

喜欢ename||'%' ： 不带索引：

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM emp_new WHERE ename LIKE ename||'%';
Explained.
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 565523140
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     2 |    12 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP_NEW |     2 |    12 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
   1 - filter("ENAME" LIKE "ENAME"||'%')
13 rows selected.
SQL>

因此，正如预期的那样，Oracle 会执行全表扫描。

喜欢ename||'%' ： 带索引：

SQL> CREATE INDEX idx_nm ON emp_new(ename);
Index created.
SQL>
SQL> EXEC DBMS_STATS.gather_table_stats('LALIT', 'EMP_NEW');
PL/SQL procedure successfully completed.
SQL>
SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM emp_new WHERE ename LIKE ename||'%';
Explained.
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 848277793
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     2 |    12 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_NM |     2 |    12 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
   1 - filter("ENAME" LIKE "ENAME"||'%')
13 rows selected.
SQL>

因此，您可以清楚地看到正在使用索引。