Regarding use of Index in oracle 8i...

[RajivSky]

Hello there,

Thank you for considering my post.
I have a problem regarding Index in Oracle 8i.
We have table named SA_AREA_EXCH_MAP_TAB and indexes as follows:
select index_name,column_position,substr(column_name,1,30 ) from DBA_ind_columns
where table_name='SA_AREA_EXCH_MAP_TAB' order by index_name,column_position;
INDEX_NAME COLUMN_POSITION SUBSTR(COLUMN_NAME,1,30)
----------------- ----------
SAEMT_3 1 AREA_CD
SAEMT_EXCH_IND 1 EXCH_CD
SAEMT_EXCH_IND 2 EXCH_SER
SAEM_PK 1 MAP_ID

Problem:
While we query by setting set autotrace on like:
Select * from SA_AREA_EXCH_MAP_TAB where area_cd='121' and exch_cd='1421'; or
SELECT * FROM SA_AREA_EXCH_MAP_TAB WHERE area_cd='121' AND exch_cd='1421';

It says that:
Select Statement Hint=Choose
Table access full

SELECT * FROM SA_AREA_EXCH_MAP_TAB WHERE exch_cd='1421' and exch_ser='14215';

It says that:
Select Statement Hint=Choose
Table Access by index rowid
Index Range Scan

i.e. Sometimes the indexes are not used and in some other condition it is uese,
we are not able to find the Major reason, I wonder if you could plese help us...

We have rebuilt the indexes but it is of no work,
but if we recreate the whole table and indexes then the indexes are used
but it is not possible that every time we create the table and indexes.
Please tell us if there is some solutions.

Thanking you
Rajiv

[jkoopmann]

unfortunately index usage relies much more than just having an index on the columns in your predicate list. it has to do with items such as how many rows in the table, amount of data you are going after, selectivity, cardinality, and physical make-up of data blocks in the table and index.

sometimes you can "test" the effectiveness of your index by removing the 'select *' from the query and change it to 'select <index_columns>'. this will tell you at least if you were to only go after index columns if you would use just the index. then start introducing other columns in the select list. also start looking at the statistics on the table and index paying attention to cardinality and selectivity.

hope this helps, there are many (well some) articles on the net for these two index properties.

[RajivSky]

Hello there,

Here is the further Clarification about my problem

We have table named SA_AREA_EXCH_MAP_TAB and indexes as follows:
select index_name,column_position,substr(column_name,1,30 ) from DBA_ind_columns where table_name='SA_AREA_EXCH_MAP_TAB' order by index_name,column_position;

INDEX_NAME COLUMN_POSITION SUBSTR(COLUMN_NAME,1,30)
----------- ------------------ ---------------
SAEMT_3 1 AREA_CD
SAEMT_EXCH_IND 1 EXCH_CD
SAEMT_EXCH_IND 2 EXCH_SER
SAEM_PK 1 MAP_ID

Problem:
Explain plan gives the following:

Select * from SA_AREA_EXCH_MAP_TAB where area_cd='121' and exch_cd='1421';
It says that:
Select Statement Hint=Choose
Table access full /***Note This****/

SELECT * FROM SA_AREA_EXCH_MAP_TAB WHERE exch_cd='1421' and exch_ser='14215';

It says that:
Select Statement Hint=Choose
Table Access by index rowid SA_AREA_EXCH_MAP_TAB
Index Range Scan SAEMT_EXCH_IND


In the above case the second query is having less records but still it is using the index
while the upper query is not using any index at all.

Now if I recreate the index as well as the table then both queries will use the indexes.

This table is the master table and contains only 1000 records. But during the batch processing,
a cursor with around 8 million records will seek this table (i.e the table will be scanned for
8 million times) , hence the processing will be slow if the index is not being used.
I have kept the index in buffer pool "keep". But after each batch processing, the scenario is the
same. I.e I recreate the tables and indexes before the batch processing (which is once a month)
then the process becomes fast. After the batch processing again the indexes will not be used.(The
above prob) and again the next time I recreate the tables.

This is really cumbersome and I am at my wits end as to what is the problem.

Please tell us if there is some useful solutions.
Thanking you
Rajiv

[jkoopmann]

since you are getting the indexes to be used when re-creating the table and index, my assumption would be that the data is not stable in the table (is being changed) and the index is getting trashed. You really need to figure out why and begin to look at the statistics around the table and indexes. again, selectivity and cardinality and figure out why the index is not being used. sorry to say but there is just no way around figuring out why an index doesn't get used without looking at the index statistics. there is no silver bullet.

that being said, and without really knowing the application, you may need to re-visit the logic in the application. if you have a cursor and it hits this query every time, is there a way you can join the to queries together instead of two distinct queries? this may in itself cause the use of the index without having to understand the statistics.