Database Version: Oracle 11.2.0.3.0
Table Structure:
CREATE TABLE OWNER.WORK_TABLE
(
COL_A NUMBER NOT NULL,
COL_B NUMBER(3) NOT NULL,
COL_C NUMBER(2) NOT NULL,
COL_D NUMBER(20),
COL_E VARCHAR2(20 BYTE),
COL_F VARCHAR2(4 BYTE),
COL_G DATE,
COL_H VARCHAR2(20 BYTE),
COL_I DATE,
COL_J DATE
);
Index:
There is a unique key index OWNER.PK_WORK_TABLE on COL_A+COL_B+COL_C (in that order). All 3 columns are numeric.
Table count : 9 million records.
Below is a query that I am working on. This query has been running long on our production due to the Full table scan access on the OWNER.WORK_TABLE tables. This is a frequently run query and we are incurring HIGH IOs.
EXPLAIN PLAN FOR
select * from OWNER.WORK_TABLE tab where tab.COL_A like '78532221%'
select * from table(dbms_xplan.display);
Plan hash value: 3977955394
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 416K| 52M| 42279 (1)| 00:08:28 |
|* 1 | TABLE ACCESS FULL| WORK_TABLE | 416K| 52M| 42279 (1)| 00:08:28 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR("tab".COL_A") LIKE '%78532221')
Findings:
1. I see that even with a index with the leading column same as the search column, the table is going for a FULL table scan. This is happening as the values in the leading column in the index (COL_A) is stored as a number but the wildcar search string - '78532221%' is coming in as a string so definitely the Index won't be used.
2. One way is to create a function based index on TO_CHAR(COL_A) on the TABLE OWNER.WORK_TABLE. I tested this and this works just fine. No issues here.
Question:
Is there a way we can get the PK_WORK_TABLE index to be used by having the wildcard string ('78532221%') in the LIKE operator to be passed in a number format ? Does LIKE always pass the search value as a STRING?
I want to see if there are provisions to get it fixed at the SQL level before I resort to the function index creation.
Appreciate your help!