Q

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".

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close