DB Index not being called

Question

I know this question has been asked more than once here. But I am not able to resolve my issue so posting it again for help.

I have a table called Transaction in Oracle database (11g) with 2.7 million records. There is a not-null varchar2(20) (txn_id) column which contains numeric values. This is not the primary key of the table, and most of the values are unique. By most of the values I mean there are cases where one value can be there 3-4 times in the table.

If I perform a simple query of select based on TXN_ID it take about 5 seconds or more to return the result.

Select * from Transaction t where t.txn_id = 245643

I have an index created on this column, but when I check the explain plan for above query, it is using full table scan. This query is being used many times in the application which is making the application slow.

Can you please provide some help what might be causing this issue?

Answer 1

You are comparing a varchar column with a numeric literal (245643). This forces Oracle to convert one side of the equality, and off hand, it seems as though it's choosing the "wrong" side. Instead of having to guess how Oracle will handle this conversion, use a character literal:

SELECT * FROM Transaction t WHERE t.txn_id = '245643'