Oracle 中的功能索引交叉引用

Question

我有两个这样的表

   create table A_DUMMY (
        TRADE_ID     VARCHAR2(16)
        TRADE_DATA   VARCHAR2(500)
   )

   create table B_DUMMY (
        EXT_TRADE_ID VARCHAR2(16)
        EXT_DATA     VARCHAR2(250)
   )

并且有一个构建类似的视图

   CREATE OR REPLACE VIEW DUMMY_VIEW("TRADE_DATA", "EXT_DATA")
   AS
   SELECT A.TRADE_DATA, B.EXT_DATA FROM A_DUMMY A, B_DUMMY B 
   WHERE 
          GET_TRADE_NUMBER(A.TRADE_ID,'-') = GET_TRADE_NUMBER(B.EXT_TRADE_ID,'_') 
        OR
          GET_TRADE_NUMBER(A.TRADE_ID,'-') = B.EXT_TRADE_ID

为了优化它，我在 A_DUMMY 中的 TRADE_ID 和 B_DUMMY 中的 EXT_TRADE_ID 上创建了功能索引。

该函数如下所示：

   create or replace function 
      GET_TRADE_NUMBER(trade in varchar2, separator in varchar2) 
   return varchar2
     deterministic
   as
   begin    
        return SUBSTR(trade, 0, INSTR(trade, separator, 1, 1) - 1);
   end;

功能索引如下所示

   create index A_DUMMY_IDX ON A_DUMMY(GET_TRADE_NUMBER(TRADE_ID,'-'));
   create index B_DUMMY_IDX ON B_DUMMY(GET_TRADE_NUMBER(EXT_TRADE_ID,'_'));

数据如下所示：

   INSERT INTO a_dummy VALUES ('7874-LND', 'item1');
   INSERT INTO a_dummy VALUES ('7845-NY', 'item2'); 
   INSERT INTO a_dummy VALUES ('7844-NY', 'item3');

   INSERT INTO b_dummy VALUES ('7844', 'item4');
   INSERT INTO b_dummy VALUES ('7845_LND', 'item5');
   INSERT INTO b_dummy VALUES ('7874_LND', 'item5'); 

如何让 Oracle 在为 DUMMY_VIEW 提供的查询中使用此索引？

因为，看起来，无论我按照解释计划做什么，Oracle 都会忽略它们。

Answer 1

如果没有示例数据和函数的详细信息，很难诊断为什么 Oracle 不使用基于函数的索引。

我将演示一个使用索引的情况：

/* Setup */
CREATE OR REPLACE FUNCTION fnc (trade_id VARCHAR2) 
   RETURN VARCHAR2 
   DETERMINISTIC IS
BEGIN
   RETURN LOWER(trade_id);
END fnc;
/

INSERT INTO a_dummy VALUES ('a', 'item1');
INSERT INTO a_dummy VALUES ('A', 'item2');
INSERT INTO a_dummy VALUES ('b', 'item3');

INSERT INTO b_dummy VALUES ('a', 'item4');
INSERT INTO b_dummy VALUES ('B', 'item5');
INSERT INTO b_dummy VALUES ('C', 'item5');

通过此设置，我们注意到索引用于简单查询：

SQL> SELECT A.TRADE_DATA, B.EXT_DATA
  2    FROM A_DUMMY A, B_DUMMY B
  3   WHERE fnc(A.TRADE_ID) = B.EXT_TRADE_ID;    

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=3 Bytes=1197)
   1    0   NESTED LOOPS (Cost=8 Card=3 Bytes=1197)
   2    1     TABLE ACCESS (FULL) OF 'B_DUMMY' (TABLE) (Cost=5 Card=3 Bytes=411)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'A_DUMMY' (TABLE) (Cost=1 [...]
   4    3       INDEX (RANGE SCAN) OF 'A_DUMMY_IDX' (INDEX) (Cost=0 Card=1)

..但不幸的是不用于您的示例查询。 OR 运算符可能会阻止优化器使用索引。我建议您使用等效的查询：

SQL> SELECT A.TRADE_DATA, B.EXT_DATA
  2    FROM A_DUMMY A, B_DUMMY B
  3   WHERE fnc(A.TRADE_ID) = fnc(B.EXT_TRADE_ID)
  4  UNION ALL
  5  SELECT A.TRADE_DATA, B.EXT_DATA
  6    FROM A_DUMMY A, B_DUMMY B
  7   WHERE fnc(A.TRADE_ID) = B.EXT_TRADE_ID
  8     AND fnc(A.TRADE_ID) != fnc(B.EXT_TRADE_ID);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=5 Bytes=1995)
   1    0   UNION-ALL
   2    1     NESTED LOOPS (Cost=8 Card=3 Bytes=1197)
   3    2       TABLE ACCESS (FULL) OF 'A_DUMMY' (TABLE) (Cost=5 Card=3 Byt[...]
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'B_DUMMY' (TABLE) (Cost=1 [...]
   5    4         INDEX (RANGE SCAN) OF 'B_DUMMY_IDX' (INDEX) (Cost=0 Card=1)
   6    1     NESTED LOOPS (Cost=8 Card=2 Bytes=798)
   7    6       TABLE ACCESS (FULL) OF 'B_DUMMY' (TABLE) (Cost=5 Card=3 Byt[...]
   8    6       TABLE ACCESS (BY INDEX ROWID) OF 'A_DUMMY' (TABLE) (Cost=1 [...]
   9    8         INDEX (RANGE SCAN) OF 'A_DUMMY_IDX' (INDEX) (Cost=0 Card=1)

作为旁注：您要不使用过滤器连接两个表，不带索引的 HASH 连接可能是计算连接的最快方法。 完整扫描不是总是邪恶的，索引并不总是好的。