CODEWITHSUNDEEP
sqloracle-databaseindexing
Gary O. Stenstrom
Gary O. Stenstrom

Reputation: 2294

oracle query is not using indexes

I am brand new to Oracle and although I have used SQL Server fairly extensively I have not had the need to delve deeply into the details of database design ... specifically INDEXES. So I have spent a good deal of time sitting through tutorials on Indexes ... both in concept as well as Oracle specific.

In an effort to put my understanding into practice I set up a VERY simple table with some basic indexes.

CREATE TABLE "SYSTEM"."TBL_PERSON" 
   (    
        "PERSON_ID" NUMBER(10,0) NOT NULL ENABLE, 
        "FIRST_NAME" NVARCHAR2(120) NOT NULL ENABLE, 
        "MIDDLE_NAME" NVARCHAR2(120), 
        "LAST_NAME" NVARCHAR2(120) NOT NULL ENABLE, 
        "DOB" DATE NOT NULL ENABLE, 
        "IS_MALE" NCHAR(1) DEFAULT 'T' NOT NULL ENABLE,
        CONSTRAINT "TBL_PERSON_PK" PRIMARY KEY ("PERSON_ID")
   )

As you can see the PERSON_ID field contains the unique ROWID for each record in the table and is an auto-incrementing Primary Key.

(please don't get hung up on missing SQL unless it pertains to the issue of INDEXES not working. I tried to select only the relevant SQL from the DDL and may have missed some items. There was a ton of stuff there that I didn't think was relevant to this issue so I tried to trim it out)

I have created a couple of additional non-clustered indexes on the table.

CREATE INDEX "SYSTEM"."IDX_LAST_NAME" ON "SYSTEM"."TBL_PERSON" ("LAST_NAME") 
CREATE INDEX "SYSTEM"."IDX_PERSON_NAME" ON "SYSTEM"."TBL_PERSON" ("FIRST_NAME", "LAST_NAME")

When I run an "Explain Plan" on the following SQL I get notified that the PK index was used as expected. 

select * from TBL_PERSON where PERSON_ID = 21

enter image description here

However when I run a query to select someone with a particular LAST_NAME the LAST_NAME index seems to go ignored.

select * from TBL_PERSON where LAST_NAME = 'Stenstrom'

enter image description here

Why would it not use IDX_LAST_NAME? For what it's worth I have the same issue with the composite index IDX_PERSON_NAME.

Upvotes: 4

Views: 2918

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The key to your question is the column "cardinality". You have only five rows estimated as being returned for the table.

Oracle has a choice between two execution plans:

  1. Load the data page. Scan the five records on the data page and choose the one(s) that match the condition.
  2. Load the index and scan it for the match. Then load the data page and lookup the matching record(s).

Oracle has concluded that for five records, the first approach is faster. If you load more data into the table, you should see the execution plan change. Alternatively, if you had select last_name instead of select *, then Oracle might very well choose the index.

Upvotes: 5

Related Questions