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.
use hints in your query i.e specify an index in your query.
please refer usage of hints.
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;
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%'
Are you sure your query is good enough to pick the indexes ?
i think so
i did this
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
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..
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]