Home » SQL & PL/SQL » SQL & PL/SQL » Use index (10.2.0.4,windows 2003)
Use index [message #414327] Tue, 21 July 2009 11:49 Go to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
Hi all,

Client has reported the below SQL query that is timing out due after running for long.

SELECT minut.add_code,tclst.class,NULL AS cust_id,minut.email AS email,minut.fname as fname,minut.lname AS lname,minut.zip,minut.org FROM test.minut, test.tclst 
    WHERE test.minut.add_code =  test.tclst.add_code AND minut.fname LIKE '%CH%' AND minut.lname LIKE '%KA%' AND minut.email LIKE '%le%' 
    AND minut.add_code = '%67%' AND minut.org LIKE '%le%';

Indexes on Minut:

add_code - primary key index
org
country,zip,mezname1,mezname2
zip
phone

Indexes on tclst:

bcode 
bgroup
add_code foreign key index to minut 
class
cst_disc



Explain Plan for the above query:
      SELECT STATEMENT ALL_ROWS
      Cost: 950
 4       TABLE ACCESS BY INDEX ROWID TABLE TEST.TCLST
          Cost: 2 Bytes: 12  Cardinality: 1
     3           NESTED LOOPS
                  Cost: 950  Bytes: 60 Cardinality: 1
            1         TABLE ACCESS FULL TABLE TEST.MINUT
                       Cost: 948 Bytes: 48 Cardinality: 1
            2         INDEX RANGE SCAN INDEX TEST.FK_MINUT_TCLST_ADDCODE
                       Cost:1 Cardinality: 1


I'm trying to repro the problem in our database without any luck. I'm not sure how I can find out for which fields in minut I need to create index for. I created a test index in our database for
minut(fname,lname,email,org) since we do not have client's data I just to see that the query uses the index instead of full table scan for minut.

create index test_idx on minut (fname,lname,email,org)


I ran the query again after creating the above index, still it doesnt use this test_idx. I forced the test_idx in the query:

SELECT /*+ INDEX (minut test_idx )*/ minut.add_code,tclst.class,NULL AS cust_id,minut.email AS email,minut.fname as fname,minut.lname AS lname,minut.zip,minut.org FROM test.minut, test.tclst  WHERE test.minut.add_code =  test.tclst.add_code AND minut.fname LIKE '%CH%' AND minut.lname LIKE '%KA%' AND minut.email LIKE '%le%' 
    AND minut.add_code = '%67%' AND minut.org LIKE '%le%';

EXPLAIN PLAN:

   SELECT STATEMENT ALL_ROWS
      Cost: 842
 5       TABLE ACCESS BY INDEX ROWID TABLE TEST.TCLST
          Cost: 2 Bytes: 12  Cardinality: 1
     4           NESTED LOOPS
                  Cost: 842  Bytes: 60 Cardinality: 1
            2         TABLE ACCESS BY INDEX ROWID TABLE TEST.MINUT
                       Cost: 840 Bytes: 48 Cardinality: 1
                  1          INDEX FULL SCAN INDEX TEST.TEST_IDX
                              Cost: 839 Cardinality: 1
            3         INDEX RANGE SCAN INDEX TEST.FK_MINUT_TCLST_ADDCODE
                       Cost:1 Cardinality: 1


Only when I force the index in the query, it is used else the query doesnt use the index. Is it because of the wild cards used in the 'like' in where clause? Please give me your thoughts.

Thanks a lot
Re: Use index [message #414328 is a reply to message #414327] Tue, 21 July 2009 12:02 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://forums.oracle.com/forums/thread.jspa?threadID=931998&tstart=0
Re: Use index [message #414367 is a reply to message #414328] Tue, 21 July 2009 16:23 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What's your point BlackSwan?
Re: Use index [message #414464 is a reply to message #414327] Wed, 22 July 2009 05:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do you have up to date statistics on the tables?
Re: Use index [message #415085 is a reply to message #414327] Fri, 24 July 2009 12:37 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
i just want to make sure of something. Your are checking for the exact string "%67%" in minut.add_code because the equals sign indicates that you do not want to use wild cards and the use of the leading wild cards in the likes would invalidate all usage of the indexes on those tables.

Previous Topic: finding entries with an return on the end
Next Topic: How to retrieve duplicate records from a table [merged]
Goto Forum:
  


Current Time: Sat Dec 03 20:13:56 CST 2016

Total time taken to generate the page: 0.14494 seconds