I have a few doubts about index creations. Can you please help me with this? I have a table called PLANTEST as below: 
CREATE TABLE PLANTEST(A NUMBER, B VARCHAR2(10))
Now I have created two non-unique indexes, for both the columns separately. 
CREATE INDEX IND ON PLANTEST(A)

CREATE INDEX IND_B ON PLANTEST(B)
Now when I execute the below query, Oracle is hitting the index and fetching the values. 
SELECT A FROM PLANTEST WHERE A = 3
But when I execute the below query, the system is not hitting the index. Can you please tell me how I should change my index/query so that the index is hit? 
SELECT A, B FROM PLANTEST WHERE A = 3 (nor) SELECT B FROM PLANTEST WHERE
A = 3 --> Not Hitting Index
As with prior queries, this is for character queries. 
SELECT B FROM PLANTEST WHERE B LIKE 'A%'

SELECT A FROM PLANTEST WHERE B LIKE 'A%' --> Full Table Scan

    Requires Free Membership to View

    Login

The issue is that Oracle doesn't know how to equate the two indexes. Therefore if you select from the table where either index can be used, it will, but if you require a column that is not in the index then it must also look at the table to read the value.

What makes you believe it is not hitting the index? It is probably using the index to find the table ROWIDS then hitting the table to find the values not in the index. Try a concatenated index.

This was first published in November 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top