Index- Query is not using
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Index- Query is not using

  1. #1
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    I had created some indexes for query which was taking too much time and doing full table scan.
    But unfortunatly query is not using it.
    Query is created dynamicaly, it is created as per user selection. I can not update query. How can I make oracle to use index (assuming that query can not be updated)
    Query code is wriiten in application using python.




  2. #2
    Join Date
    Nov 2001
    Location
    bangalore
    Posts
    4
    use hints in your query i.e specify an index in your query.
    please refer usage of hints.
    kavitha s

  3. #3
    Join Date
    Aug 2001
    Posts
    111
    Make sure the optimisers as much stats about the table and indexes as possible

    analyze table compute statistics
    for table for all indexes for all indexed columns;

    Have Fun
    Performance... Push the envelope!

  4. #4
    Join Date
    Jun 2001
    Posts
    316
    Not sure 'bout this
    but deff worth a try

    try forcing the index!

    SELECT /*+ INDEX(table_name, index_name) */ * FROM table_name WHERE col LIKE '%SAM%'

    HTH

  5. #5
    Join Date
    Oct 2000
    Posts
    467
    Are you sure your query is good enough to pick the indexes ?
    Vinit

  6. #6
    Join Date
    Jun 2001
    Posts
    316
    i think so

    i did this

    explain plan
    set statement_id='4' for
    SELECT /*+ INDEX(table_name, indexname) */ * FROM table WHERE col LIKE '%SAM%'

    i got this

    4 15.11.01 10:55:29 SELECT STATEMENT
    4 15.11.01 10:55:29 TABLE ACCESS BY INDEX ROWID
    4 15.11.01 10:55:29 INDEX RANGE SCAN


    Then i did

    explain plan
    set statement_id='5' for
    SELECT * FROM table WHERE col LIKE '%SAM%'

    and i got this

    5 15.11.01 10:55:48 SELECT STATEMENT
    5 15.11.01 10:55:48 TABLE ACCESS FULL

    So i suppose it did use the index
    Pls correct me if iam wrong..
    Thanx
    Sam

  7. #7
    Join Date
    Oct 2000
    Posts
    467
    If you want to pick the indexes then you need to access the indexed columns in your where clause. Seconly i think it is not picking the indexes because of the LIKE clause.

    [Edited by vinit on 11-15-2001 at 06:18 AM]
    Vinit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width