title: oracle query is not using indexes
body: <p>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.</p>
  
  <p>In an effort to put my understanding into practice I set up a VERY simple table with some basic indexes.</p>
  
  <pre><code>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")
     )
  </code></pre>
  
  <p>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.</p>
  
  <p>(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)</p>
  
  <p>I have created a couple of additional non-clustered indexes on the table.</p>
  
  <pre><code>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")
  </code></pre>
  
  <p>When I run an "Explain Plan" on the following SQL I get notified that the PK index was used as expected. </p>
  
  <pre><code>select * from TBL_PERSON where PERSON_ID = 21
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/6DShv.jpg" alt="enter image description here"></p>
  
  <p>However when I run a query to select someone with a particular LAST_NAME the LAST_NAME index seems to go ignored.</p>
  
  <pre><code>select * from TBL_PERSON where LAST_NAME = 'Stenstrom'
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/Sj1Im.jpg" alt="enter image description here"> </p>
  
  <p>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.</p>