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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: