Impact of comparing varchar2 column to a number value in the WHERE clause of a SELECT
Database: 7.3.4.3.0 +
When writing queries we knowingly or unknowingly compare a column of type varchar2 with
a number value. We don't bother about the conversion as Oracle internally handles it.
Is there any impact as such of doing this?
If you're relying on the
default oracle mechanism for datatype conversion in your query, chances are
that your query might miss out on using the index and may go in for a
full table scan. This behaviour is noticed in varchar2 type columns. See the
example below.
SQL>desc am01
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER
COL2 NOT NULL VARCHAR2(100) -- Non-Unique
-- index present on this column
SQL>select * from am01;
COL1 COL2
---------- -----------------
1 1
1 2
1 3
1 4
1 5
1 7
1 6
SQL> select *
2 from am01
3 where col2 = '5'; -- the searching condition is in quotes so
-- char compared against a char, no convertion done.
-- index used on col2.
COL1 COL2
---------- ----------------------------------------------------
1 5
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY ROWID) OF 'AM01'
2 1 INDEX (RANGE SCAN) OF 'AM01_IND1' (NON-UNIQUE)
SQL> select *
2 from am01
3 where col2 = 5; -- char compared against a number
-- so oracle converts the datatype of one of the two.
-- no index used???
COL1 COL2
---------- ------------------------------------------
1 5
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (FULL) OF 'AM01'
Oracle is converting the column being
compared datatype to compare it against the number. This can further be
confirmed if you introduce an alphabet in the varchar2 column and then
compare it with a number, it gives ora-1722, invalid number error, as it is
trying to convert an alphabet value to number. Since oracle internally uses
function to convert the datatype of the column, the index will not be used
and a full table scan is taking place. The same query works fine if you are comparing a number datatype column with a char or number value, it uses the index in both the cases. Best viewed in medium text size. Please refresh this page (F5) to view the latest information.
This page was created on 16-dec-2001 and last updated on 16-dec-2001.
please forward all queries to amar_padhi@fastmail.fm