Hi,
I've got the following sql statement:
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;
The APPLICATION.APPLICATION_NUMBER is a VARCHAR field and has an index.
When I run the query with a varchar2 bind variable, the index is used and the query runs quite fast:
--------------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------------
However, if the bind variable type is number, the query is terribly slow (as expected, it doesn't use the index, running FTS instead):
-----------------------------------------------------------------------------------
| 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 |
-----------------------------------------------------------------------------------
I tried to force the query to use the index (using hint), but without success.
As I can't change the logic of the application and can't create a function based index - is there any way to influence the optimize to use the index? If I run a query with a slightly changed condition (C.APPLICATION_NUMBER =TO_CHAR (:b1)) it uses the index and returns correct values. Though, as I mentioned, I can't change the application code.
I'm curious on
"and can't create a function based index"
Why not?
That seems the logical answer here, no?
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)