Q

Creating a query to hit an index

I have a few doubts about index creations. I have a table called PLANTEST. Now I have created two non-unique indexes, for both the columns separately. 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?

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
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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close