Functional Index Cross Reference in Oracle

Question

I have two tables like this

   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)
   )

And you have a view that creates something like this

   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

To optimize this, I created a functional index TRADE_ID in A_DUMMY and EXT_TRADE_ID in B_DUMMY.

The function is as follows:

   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;

Functional indices are as follows

   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,'_'));

The data is as follows:

   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'); 

How can I get Oracle to use these indexes in the provided query for DUMMY_VIEW?

Because it seems that I am doing according to the plan of explanation, Oracle ignores them.

Answer 1

without sampled data and function details, it's hard to diagnose why Oracle is not using your functional indexes.

, :

/* 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 , , . , .