如何在预言机上选择可选参数(减少预言机的执行时间)

Question

我必须减少这个的执行时间。当我刚刚插入"：i_member_name"时，此语句处理浪费了大约 30 分钟，所有其他都是空的。

SELECT 
A.MEMBER_NUM, A.MEMBER_NAME, B.MEMBER_INFO FROM REG_MEMBER A, MEMBER_DETAIL B
WHERE A.MEMBER_ID = B.MEMBER_ID 
AND (LOWER(A.MEMBER_NAME)  LIKE LOWER(:i_member_name || '%'))     
AND (LOWER(B.MEMBER_INFO) LIKE LOWER(:i_member_info || '%'))
AND (A.MEMBER_NUM  LIKE :i_member_num || '%')
AND (LOWER(B.MEMBER_ADD) LIKE LOWER('%' || :i_member_add || '%'))

如果我像这样执行，那就没问题了，只是浪费了 30 秒。

SELECT 
    A.MEMBER_NUM, A.MEMBER_NAME, B.MEMBER_INFO FROM REG_MEMBER A, MEMBER_DETAIL B
    WHERE A.MEMBER_ID = B.MEMBER_ID 
    AND (LOWER(A.MEMBER_NAME)  LIKE LOWER(:i_member_name || '%'))     
  --AND (LOWER(B.MEMBER_INFO) LIKE LOWER(:i_member_info || '%'))
  --AND (A.MEMBER_NUM  LIKE :i_member_num || '%')
  --AND (LOWER(B.MEMBER_ADD) LIKE LOWER('%' || :i_member_add || '%'))

所以我有一个问题：如何检查参数是否为空，然后不要针对更多的执行时间阅读。 像这样的东西

         SELECT A.MEMBER_NUM, A.MEMBER_NAME, B.MEMBER_INFO FROM REG_MEMBER A, MEMBER_DETAIL B         
                    WHERE 
(LOWER(A.MEMBER_NAME)  LIKE LOWER(:i_member_name || '%'))    
         and   case when :i_member_num is null then return 
        else (A.MEMBER_NUM LIKE :i_member_num || '%')
    end

Answer 1

在 SQL 中检查可选参数的最快方法通常是使用 NVL 。

Oracle 通常可以将NVL表达式扩展为两个单独的子计划。 FILTER操作将在运行时仅选择计划的一部分。 例如，它可能使用具有可优化优化的绑定值(如 'Smith%'(的INDEX RANGE SCAN;并且它可能使用具有不可优化优化的绑定值(如 NULL(的TABLE ACCESS FULL。

下面是使用问题中对象的一小部分的示例：

--Create table and index.
create table reg_member(member_num number, member_name varchar2(100));
create index reg_member_idx on reg_member(lower(member_name));
--Generate explain plan.
EXPLAIN PLAN FOR
SELECT
    A.MEMBER_NUM, A.MEMBER_NAME
FROM REG_MEMBER A
WHERE NVL(:i_member_name, LOWER(A.MEMBER_NAME)) = LOWER(A.MEMBER_NAME);
--Show explain plan.
select * from table(dbms_xplan.display);
Plan hash value: 3501926772
----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |     2 |   130 |     3   (0)| 00:00:01 |
|   1 |  VIEW                                  | VW_ORE_377C5901 |     2 |   130 |     3   (0)| 00:00:01 |
|   2 |   UNION-ALL                            |                 |       |       |            |          |
|*  3 |    FILTER                              |                 |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| REG_MEMBER      |     1 |   117 |     1   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | REG_MEMBER_IDX  |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    FILTER                              |                 |       |       |            |          |
|*  7 |     TABLE ACCESS FULL                  | REG_MEMBER      |     1 |   117 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:I_MEMBER_NAME IS NOT NULL)
   5 - access(LOWER("MEMBER_NAME")=:I_MEMBER_NAME)
   6 - filter(:I_MEMBER_NAME IS NULL)
   7 - filter(LOWER("MEMBER_NAME") IS NOT NULL)
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

在某些情况下，NVL FILTER操作可以很好地工作。 但是很难说它是否能解决你的特定问题，因为有很多变量：对象是否有正确的索引，所有谓词如何协同工作，等等。 但NVL是一个很好的起点。