Skip to Main Content
  • Questions
  • Can I force query to use the varchar index if the condition uses a number value

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Krystian.

Asked: January 22, 2019 - 11:36 am UTC

Last updated: February 07, 2019 - 2:02 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

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.

and Connor said...

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)




Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Krystian Rakoczy, February 06, 2019 - 8:15 am UTC

Hi Connor,

thanks a lot for your answer.

However, I'm not able to use a function based index, as in this field there's a couple of values with characters. And sometimes such values are inserted to the table.

If I created an index based on the to_number function, I would expect ORA-01722 while inserting.
Connor McDonald
February 07, 2019 - 2:02 am UTC

Surely that's a disaster waiting to happen - because that means you also run the same risk every time you query the table with a numeric bind ?

what's this then

Racer I., February 06, 2019 - 9:02 am UTC

Hi,

@OP :
> If I created an index based on the to_number function, I would expect ORA-01722 while inserting.

Then this should also happen if that application currently binds as number :

create table t1 (some_value VARCHAR2(10));

select * from t1 where some_value = 1;

insert into t1 values ('a');

commit;

select * from t1 where some_value = 1;
-> ORA-01722


So there must be some information about your system missing.

Query rewrite might work.

regards,

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.