Search code examples
sqloracle-databasequery-performance

use string value like numeric value in Oracle

I have a condition in my oracle query:

AND a.ACCARDACNT > '0880080200000006' and a.ACCARDACNT < '0880080200001000'

type of ACCARDACNT column in table is varchar and indexed but in that condition I want to use it as number. When I execute this query, the execution plan shows that CBO can use index and scan the table by index. is it true?

I want to use and compare them as number and also an indexed be used. Is there any solution?

Solution

  • Your current query should be able to use an index. But the problem is that you are comaparing text but expecting it to sort numerically. It may not, in general, because text sorts lexicographically in SQL (i.e. in dictionary order). So, to get the correct sorting behavior you will have to cast ACCARDACNT to a number:

    AND CAST(LTRIM(a.ACCARDACNT, '0') AS FLOAT) BETWEEN 880080200000007 AND 880080200000999