Oracle 如果条件使用数字值，我可以强制查询使用varchar索引吗

嗨，

我有以下sql语句:

SELECT  C.APPLICATION_NUMBER, D.AGREEMENT_TYPE, C.SUPPLIER_REFERENCE
FROM APPLICATION_DATA D, APPLICATION C
WHERE  C.APPLICATION_NUMBER =:b1 
AND D.APPLICATION_ID = C.APPLICATION_ID;
复制

APPLICATION_NUMBER是一个VARCHAR字段，并且有一个索引。

当我使用varchar2绑定变量运行查询时，使用索引，并且查询运行速度非常快:

--------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                     |     7 (100)|          |
|   1 |  NESTED LOOPS                         |                     |     7   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                     |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| APPLICATION         |     5   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | APP_APPNUMBER_IDX   |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                  | APPLICATION_DATA_PK |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | APPLICATION_DATA    |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
复制

但是，如果绑定变量类型是数字，则查询非常慢 (如预期的那样，它不使用索引，而是运行FTS):

-----------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |   107K(100)|          |
|   1 |  NESTED LOOPS                |                     |   107K  (1)| 00:00:05 |
|   2 |   NESTED LOOPS               |                     |   107K  (1)| 00:00:05 |
|*  3 |    TABLE ACCESS FULL         | APPLICATION         |   107K  (1)| 00:00:05 |
|*  4 |    INDEX UNIQUE SCAN         | APPLICATION_DATA_PK |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| APPLICATION_DATA    |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
复制

我试图强制查询使用索引 (使用提示)，但没有成功。

因为我不能改变应用程序的逻辑，也不能创建基于函数的索引 -- 有没有办法影响优化使用索引？如果我运行的查询条件略有更改 (C.APPLICATION_NUMBER = TO_CHAR (:b1))，它将使用索引并返回正确的值。不过，正如我提到的，我不能改变应用程序代码。

我很好奇

“并且无法创建基于函数的索引”

为什么不呢？

这似乎是合乎逻辑的答案，不是吗？

SQL> create table t as
  2  select distinct object_type from dba_objects;

Table created.

SQL> create table t1 as
  2  select object_id, to_char(data_object_id) char_col, created, owner, object_type, object_name
  3  from dba_objects;

Table created.

SQL> create index T1_IX on t1 ( char_col);

Index created.

SQL>
SQL> variable num number
SQL> variable str varchar2(30);
SQL>
SQL> exec :num := 12345;

PL/SQL procedure successfully completed.

SQL> exec :str := '12345';

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select count(*) from t, t1 where t1.object_type = t.object_type and char_col = :str;

  COUNT(*)
----------
         0

1 row selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  814t1hjgz5da3, child number 0
-------------------------------------
select count(*) from t, t1 where t1.object_type = t.object_type and
char_col = :str

Plan hash value: 704178638

-------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | E-Rows |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |        |       |       |          |
|   1 |  SORT AGGREGATE                       |       |      1 |       |       |          |
|*  2 |   HASH JOIN                           |       |      1 |  1538K|  1538K|  485K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | T1_IX |      1 |       |       |          |
|   5 |    TABLE ACCESS FULL                  | T     |     46 |       |       |          |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."OBJECT_TYPE"="T"."OBJECT_TYPE")
   4 - access("CHAR_COL"=:STR)

SQL> select count(*) from t, t1 where t1.object_type = t.object_type and char_col = :num;

  COUNT(*)
----------
         0

1 row selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f4xx6t3h8gda9, child number 0
-------------------------------------
select count(*) from t, t1 where t1.object_type = t.object_type and
char_col = :num

Plan hash value: 949044725

------------------------------------------------------------------------
| Id  | Operation           | Name | E-Rows |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |        |       |       |          |
|   1 |  SORT AGGREGATE     |      |      1 |       |       |          |
|*  2 |   HASH JOIN         |      |      1 |  1538K|  1538K|  494K (0)|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |       |       |          |
|   4 |    TABLE ACCESS FULL| T    |     46 |       |       |          |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."OBJECT_TYPE"="T"."OBJECT_TYPE")
   3 - filter(TO_NUMBER("CHAR_COL")=:NUM)

SQL> create index t1_ix2 on t1 ( to_number(char_col)) ;

Index created.

SQL>
SQL> select count(*) from t, t1 where t1.object_type = t.object_type and char_col = :num;

  COUNT(*)
----------
         0

1 row selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f4xx6t3h8gda9, child number 0
-------------------------------------
select count(*) from t, t1 where t1.object_type = t.object_type and
char_col = :num

Plan hash value: 3299206697

--------------------------------------------------------------------------------------------
| Id  | Operation                             | Name   | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |        |        |       |       |          |
|   1 |  SORT AGGREGATE                       |        |      1 |       |       |          |
|*  2 |   HASH JOIN                           |        |    732 |  1856K|  1856K| 1595K (0)|
|   3 |    TABLE ACCESS FULL                  | T      |     46 |       |       |          |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1     |    732 |       |       |          |
|*  5 |     INDEX RANGE SCAN                  | T1_IX2 |    293 |       |       |          |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."OBJECT_TYPE"="T"."OBJECT_TYPE")
   5 - access("T1"."SYS_NC00007$"=:NUM)


复制