Re: Oracle not using indexes on char/varchar columns

>> Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production <<

I did:

insert into test select distinct(substr(object_name, 1,4)) from all_objects;

..571 rows

select * from TEST where COL1=1;

• Query Plan: 10/08/2003 12:33:01 pm

Operation Name Rows Bytes Cost PStart PStop

----------------- ----------- ---- ----- ---- ------ ----- 
SELECT STATEMENT                1     4    2               
  INDEX FULL SCAN SYS_C002157   1     4    4    

But COL1 is a varchar2 column:

• jvmo_at_ids9i> select * from TEST where COL1=1;
• select * from TEST where COL1=1
• *
• ERROR at line 1:
• ORA-01722: invalid number

select * from TEST where COL1='1'; -- <<< use quotes!

• Query Plan: 10/08/2003 12:33:32 pm

Operation Name Rows Bytes Cost PStart PStop

------------------- ----------- ---- ----- ---- ------ ----- 
SELECT STATEMENT                  1     4    2               
  INDEX UNIQUE SCAN SYS_C002157   1     4    2   

jan Received on Wed Oct 08 2003 - 12:40:38 CDT