CODEWITHSUNDEEP
databaseperformanceoracle-databaseindexing
bdavies6086
bdavies6086

Reputation: 382

Oracle primary key affecting indexes

I had an index working really nicely last night, low I/O all that good stuff. Now this morning I added a primary key to the table and the performance has dropped and the optimizer ignores the index even with hints.. Any advice? Thankyou

Schema structure..

      Product
      ID       PK
      Name     
      Price   

      Order_Line
      Order_ID    FK
      Product_ID  FK
      Qty

      Orders
      ID             PK
      O_Date date
      CustID

Query...

      SELECT SUM(OL.QTY) FROM  PRODUCT P,ORDERS O, ORDER_LINE OL   
      WHERE
      P.NAME = 'APRICOT JAM'
      AND
      P.ID = OL.PRODUCT_ID
      AND
      O.O_DATE = '03-MAR-2014'
      AND
      OL.ORDER_ID= O.ID
      ;

The index which it isn't using is a composite index on product which is (name,ID), instead its using products primary key to do an index range scan Thanks!

Upvotes: 0

Views: 70

Answers (1)

OraNob
OraNob

Reputation: 694

Product Table ID PK Name INDEX01 Price

I would structure your table this way. Your query is going to range scan because it is going to scan the table for the P.NAME = 'APRICOT JAM' clause in your query.

Upvotes: 1

Related Questions