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 = 3But 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 IndexAs 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
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