AmZone

Web Hosting by Netfirms | Free Domain Names by Netfirms

AmZone

A m Z o n e

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.

Press the Back button of you Browser to go to previous page
Home