Take the 2-minute tour ×

In my backend procedure i have a varchar2 parameter and i am using it in the SQL query to search with number column. Will this cause any kind of performance issues ?

for ex:

Proc (a varchar)
select * from table where deptno = a;

Here deptno is number column in table and a is varchar .

share|improve this question
add comment

1 Answer

It might do. The database will resolve the differences in datatype by casting DEPTNO to a VARCHAR2. This will prevent the optimizer from using any (normal) index you have on that column. Depending on the data volumes and distribution, an indexed read may not always be the most efficient access path, in which case the data conversion doesn't matter.

So it does depend. But what are your options if it does matter (you have a highly selective index on that column)?

One solution would be to apply an explicit data conversion in your query:

select * from table 
where deptno = to_number(a);

This will cause the query to fail if A contains a value which won't convert to a number.

A better solution would be to change the datatype of A so that the calling program can only pass a numeric value. This throws the responsibility for duff data where it properly belongs.

The least attractive solution is to keep the procedure's signature and the query as is, and build a function-based index on the column:

create index emp_deptchar_fbi on emp(to_char(deptno));

Read the documentation to find out more about function-based indexes.

share|improve this answer
So i must ensure that query parameters are in sync with procedure parameters to avoid performance impacts in the long run ..Thanks:) –  njnbat Jul 4 '13 at 12:26
There are more options than that. Check my edited answer. –  APC Jul 4 '13 at 12:31
add comment

Your Answer


