Tuesday, February 24, 2009

Indexes not being used due to different data types and data casting problems

We have a table XDP_ORDER_HEADERS, which has a column named EXTERNAL_ORDER_NUMBER of VARCHAR2 type, but, in essence, we store numeric values in it.
SELECT EXTERNAL_ORDER_NUMBER FROM XDP_ORDER_HEADERS;
  
EXTERNAL_ORDER_NUMBER
1000291
1000292
1000293
1000294
1000295
1000296
1000297
1000298
1000299
100030
1000300

We create an index named XDP_ORDER_HEADERS_IDX on that column.
Since we know that in this column we store numbers, executing the query:
SELECT *
  FROM XDP_ORDER_HEADERS
 WHERE EXTERNAL_ORDER_NUMBER = 1000296;

should use our index and run faster, right?
Wrong!
The execution plan shows us that a Full Table Scan is used instead by the CBO:

PlanIO CostCPU CostBytesCost
SELECT STATEMENT CHOOSE7.505835,979,2871908.35
1 TABLE ACCESS FULL DP.XDP_ORDER_HEADERS7.505835,979,2871908.35

Since this column is of VARCHAR2 type, we should compare it with VARCHAR2 values, if we want the right index to be used.
So, the query that will take use of our index is the following:
SELECT *
  FROM XDP_ORDER_HEADERS
 WHERE EXTERNAL_ORDER_NUMBER = '1000296';
 
PlanIO CostCPU CostBytesCost
SELECT STATEMENT CHOOSE423.041905
2 TABLE ACCESS BY INDEX ROWID XDP.XDP_ORDER_HEADERS423.041905
1 INDEX RANGE SCAN NON-UNIQUE XDP.XDP_ORDER_HEADERS_IDX314.4824

No comments:

Post a Comment