I have few doubts concerning Index
creations. Can you please help me?
I have a table called PLANTEST as shown below:
CREATE TABLE PLANTEST (A NUMBER, B
VARCHAR2(10))
I have also created two Non-Unique Indexes,
for both the columns separately, as shown below:
CREATE INDEX IND ON PLANTEST(A)
CREATE INDEX IND_B ON PLANTEST(B)
When I execute the query below, Oracle is
hitting Index and fetching the values:
SELECT A FROM PLANTEST WHERE A = 3
However, when I execute the query below,
system is not hitting the Index:
SELECT A, B FROM PLANTEST WHERE A = 3 (nor)
SELECT B FROM PLANTEST WHERE
A = 3 --> Not Hitting Index
Can you please tell me how should I change my
index/query so that Index is hit?
Like the 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
Thank you.
This question posed on 25 November 2005
Answer:
