Oracle Creating Indexes

Oracle Database Tips by Donald Burleson

Question:

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: