Oracle Tips 20110614- about data type conversion

This post explains how to use the Explain plan for Oracle's SQL execution to debug sqls.
First create two tables:

create table char_tables (party_id varchar2(30), party_name varchar2(30))

create table number_tables (party_id number, party_name varchar2(30));

insert into number_tables select rownum, table_name from user_tables

insert into char_tables select rownum, table_name from user_tables

insert into char_tables select table_name, table_name|| rownum from user_tables

When issue the following SQL, there is an invalid number exception:
   select  n.party_id,c.party_id
 from  number_tables n,char_tables c
 where   n.party_id = c.party_id and n.party_id =1;

it maybe the data type conversion problem, but let's confirm it with explain plan.

explain plan
 for
select  n.party_id,c.party_id
 from  number_tables n,char_tables c
 where   n.party_id = c.party_id and n.party_id =1;
 commit;
 select * from table(dbms_xplan.display);
  The plan is as below:
  
  We can see from the plan that the sql engine try to convert the VARCHAR2 column into the NUMBER column.

  Let's try another sql:
  explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

the plan is as below:
  
  The SQL engine try to convert the column into the NUMBER type.

 EXPLAIN PLAN
 FOR 
DELETE FROM char_tables WHERE party_id not in (1,2,3);
  COMMIT;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  The plan is as below:
 
  For above, the NOT clause is translated into the <> operator.  This SQL doesn't work because some values of the party_id column in the char_tables CAN NOT BE CONVERTED INTO NUMBER.

Another SQL:
   explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

  The plan is as below:
 
  
   If the variable's type matches the column's type, no conversion is needed, otherwise, a conversion is applied.
Try to convert the VARCHAR2 into NUMBER.

  Let's considering the scenarios when indexes are used:
   create index char_index on char_tables(party_id) ;
   create index number_index on number_tables(party_id);

The SQL is as below:
   explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

 

  it's a full-table access query. The index is not used because the SQL was translated into the TO_NUMBER()...
 A function index ON TO_NUMBER(party_id) may works.

 Let's try the other SQL:
 
  It's not a full table access, but an index range access.

  So, in Oracle, since data conversion will be automatically executed, the developer has to pay attention to those default tips.