CODEWITHSUNDEEP
databaseoracle-databaseindexing
bdavies6086
bdavies6086

Reputation: 382

Oracle applying indexes to improve this query

Hi I am having issues applying indexes to improve the speed of the following query..

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

Any suggestions would be most appreciated! Currently I have tried applying a concatenated index to the orders table as (O_DATE,ID) which hasn't helped much!

The tables designs are below! Thankyou!

  ORDERS
  ID         NOT NULL     NUMBER(38)
  CUSTID     NOT NULL     NUMBER(38)
  O_DATE     NOT NULL     DATE

  ORDER_LINE
  ORDER_ID   NOT NULL     NUMBER(38)
  PRODUCT_ID NOT NULL     NUMBER(38)
  QTY        NOT NULL     NUMBER(4)

  PRODUCT
  ID         NOT NULL     NUMBER(38)
  NAME       NOT NULL     VARCHAR(30)
  PRICE      NOT NULL     NUMBER(3,2)

Upvotes: 0

Views: 36

Answers (1)

hkutluay
hkutluay

Reputation: 6944

Create index for this fields

PRODUCT.NAME

ORDER_LINE.PRODUCT_ID
ORDER_LINE.ORDER_ID

ORDERS.O_DATE

I assume that you have already primary key fields. If not, create unique index for primary keys first

ORDER.ID
PRODUCT.ID

Upvotes: 1

Related Questions