tune the query..... between dates is making the query run too slow

Hai
       i have a query which is running fine but when i changed the query to use a range between the dates
the query is running too slow

SELECT
    pd.ProjectId ProjectId,
    pd.NSDOrderNo NSDOrderNo,
    pd.LocationId LocationId,
    ttto.Description Description_to,
    pdcsli.ConnFIC ConnFIC,
    pdcsli.ConnShelfUnit x,
    (case when pdcsli.ConnShelfUnit like '%A' then replace(pdcsli.ConnShelfUnit,'A','') when pdcsli.ConnShelfUnit like '%B' then replace(pdcsli.ConnShelfUnit,'B','') when pdcsli.ConnShelfUnit like '%R' then substr(pdcsli.ConnShelfUnit,1,1) when pdcsli.ConnShelfUnit like '%T' then substr(pdcsli.ConnShelfUnit,1,1) else pdcsli.ConnShelfUnit end) ConnShelfUnit,
    pdcsli.ConnTechJackstrt ConnTechJackstrt,
    pdcsli.ConnTechMod ConnTechMod,
    pdcsli.ConnTechUnit xx,
    trim(translate(pdcsli.ConnTechUnit, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',' ')) ConnTechUnit,
    ttfrom.Description Descriptionfrom,
    pdcsli.FIC FIC,
    pdcsli.ShelfUnit y,
    (case when pdcsli.ShelfUnit like '%A' then replace(pdcsli.ShelfUnit ,'A','') when pdcsli.ShelfUnit like '%B' then replace(pdcsli.ShelfUnit,'B','') when pdcsli.ShelfUnit like '%R' then substr(pdcsli.ShelfUnit,1,1) when pdcsli.ShelfUnit like '%T' then substr(pdcsli.ShelfUnit,1,1) else pdcsli.ShelfUnit end) ShelfUnit,
    pdcsli.TechJackStart TechJackStart,
    pdcsli.TechMod TechMod,
    pd.SupplierOrderNumber SupplierOrderNumber,
    pdcsli.TechUnit TechUnit,
    to_char(pd.SENTTOSUPPLIERDATETIME, 'mm/dd/yyyy') SENTTOSUPPLIERDATETIME
FROM
  LOGIC_V_PROJDMDREQ pd,
  LOGIC_LGTECHTYPE  ttto,
  LOGIC_LGPROJDMDREQCABLESUBLI pdcsli,
  LOGIC_LGTECHTYPE  ttfrom,
  LOGIC_LGPROJDMDREQCABLELI pdcli
WHERE pdcli.ProjDmdReq_ObjectId = pd.ObjectId
AND pdcli.ConnTechType_ObjectId = ttto.ObjectId
AND ttfrom.ObjectId = pdcli.TechType_ObjectId
AND pdcsli.LineItem_ObjectId = pdcli.ObjectId
AND pdcsli.ConnShelfUnit IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '11', '12', '21', '22', '2R', '2T', '3R', '3T', '4R', '4T', '6R', '6T', '7R', '7T', '8R', '8T', '1I', '1O', '2I', '2O', '3I', '3O', '1A', '1B', '2A', '2B', '11A', '11B', '12A', '12B', '21A', '22B', '22A', '22B', '21R', '21T', '61R', '61T')
AND ttto.Description IN ('LGX3 - LS ODF', 'NEC OADM', 'NGOL', 'NGOT', 'SPECTRALWAVE 160', 'WAVESTAR OLS')
AND ttfrom.Description IN ('LGX3 - LS ODF', 'NEC ADM', 'NEC OADM', 'NGFT', 'NGOT', 'SPECTRALWAVE 160', 'WAVESTAR OLS')
AND pd.CancelIndr = '0'
AND pdcsli.TechJackStart IS NOT NULL  
AND (pd.ProjectId LIKE 'HF%' OR pd.ProjectId LIKE 'IF%')
and pd.SENTTOSUPPLIERDATETIME between to_date('02/01/2008', 'mm/dd/yyyy')
and to_date('02/28/2008', 'mm/dd/yyyy')

ORDER BY 1, 2, 3
Start Free Trial