Using the LIKE clause with indexes

I have created a index on customer_id in my table. In my frontend application, I am querying the records on the basis of "customer_id like '%'. In this case I cannot use the index created on customer_id column, so could you please suggest some way how I can tune my query so that it can use the customer_id index? Or is there any other solution? In every case I have to use the "like clause".

    Requires Free Membership to View

    Login

There is no reason why using LIKE should subvert the use of your index EXCEPT under the following circumstance. If customer_id is a numeric datatype, when you use a LIKE operator you're comparing the numeric value stored in customer_id with a string (LIKE '123%'). When you do this, you're not comparing like datatypes and therefore Oracle won't use the index. If you're going to be coding using the LIKE operator, you might find that you need to make the customer_id column a character datatype.

Take a look at this example:

SQL> desc company
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 COMPANY_ID                                NOT NULL NUMBER
 NAME                                               VARCHAR2(100)
 INCORP_DATE                                        DATE
 FILING_DATE                                        DATE
 
 Index COMP_IDX on COMPANY_ID
 

 SQL> select * from company ;
 
 COMPANY_ID NAME                 INCORP_DA FILING_DA
 ---------- -------------------- --------- ---------
          1 ABC Co.              03-APR-45 03-MAR-45
          2 Miller and Sons      23-OCT-02 23-OCT-22
          3 Temple Feed and Seed 02-MAR-71 10-MAR-71
          4 Jack Penneys         27-JUN-60 27-JUN-60
          5 Target               11-NOV-85 11-NOV-85
          6 Dilbert Co.          01-JAN-90 01-JAN-90
          7 Bubba Gump Shrimp    20-JUL-68 20-JUL-68
          8 Hopewell Farms       23-AUG-92 23-AUG-92
          9 HoneyBerry Farm      10-MAY-01 10-MAY-01
         10 Acme Inc.            14-FEB-50 14-FEB-50
        

SQL> select * from company where company_id like '1%' ;

COMPANY_ID NAME                 INCORP_DA FILING_DA
---------- -------------------- --------- ---------
         1 ABC Co.              03-APR-45 03-MAR-45
        10 Acme Inc.            14-FEB-50 14-FEB-50


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=29)
   1    0   TABLE ACCESS (FULL) OF 'COMPANY' (Cost=1 Card=1 Bytes=29)
Now, if I change the datatype of the company_id column to varchar2 and re-create the index, watch what happens. 
SQL> desc company
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 COMPANY_ID                                         VARCHAR2(5)
 NAME                                               VARCHAR2(100)
 INCORP_DATE                                        DATE
 FILING_DATE                                        DATE


SQL> select * from company where company_id like '1%';

COMPA NAME                 INCORP_DA FILING_DA
----- -------------------- --------- ---------
1     ABC Co.              03-APR-45 03-MAR-45
10    Acme Inc.            14-FEB-50 14-FEB-50


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY'
   2    1     INDEX (RANGE SCAN) OF 'COMP_IDX' (NON-UNIQUE)


This was first published in December 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top