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.
Dig deeper on Oracle database design and architecture
Related Q&A from Mike Ault, Senior Oracle Consultant, Burleson Consulting
I am trying to remove carriage returns at the end of clob fields in SQL*Plus. This just nulls out the field in the table. What do I need to change to...continue reading
How to find the definition or structure of a dropped table? I know the table's name but I don't know the columns and datatypes. It no longer exists.continue reading
I want to find the length of a numeric datatype field in my table. How can I find it?continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.