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.
11-15-2001, 01:11 AM
use hints in your query i.e specify an index in your query.
please refer usage of hints.
11-15-2001, 02:21 AM
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;
11-15-2001, 04:32 AM
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%'
11-15-2001, 05:44 AM
Are you sure your query is good enough to pick the indexes ?
11-15-2001, 06:00 AM
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%'