This discussion is archived
1 2 3 8 Previous Next 117 Replies Latest reply: May 5, 2008 10:19 AM by 181444 RSS

query tuning

599921 Newbie
Currently Being Moderated
Hi All,
I have 4 tables. I wrote one query as follows. But it's taking 45 mins for executing.
Can you please write a query for this to improve performance.the tables are as follows.
1. Case 2. Item 3. Master 4. Warehouse
columns are
Case:  Item, Supply_indicator ,  Country_Indicator , item_size

Item : Item , location ,location_type, ondate, offdate,status,create_date

Master :item , status,  pack_indicator, Item_level(either 1 or 0),Trns_level(either 1 or 0),create_date,Foreind (either Y or N)

Warehouse  :  Warehose_no, Name , Address
SELECT      im.location                        ,
                   im.item                             ,
                   cs.case_size                     ,
                   im.ondate                         ,
                   im.offdate               ,
                   mas.status        
              FROM  case_size       cs,
                          item               im,
                          master            mas,
                          warehouse       wh
             WHERE mas.pack_ind                   = 'N'
               AND     mas.item_level                  = mas.trns_level
               AND     mas.status                       = 'A'
               AND    mas.foreind                        = 'Y'
               AND     mas.item                          = im.item
               AND     im.location_type                   = 'S'
               AND    mas.item                            = cs.item
               AND    cs.supply_indicator             = 'Y'
               AND    cs.country_indicator            = 'Y'
               AND    im.location =wh.warehose_no
               AND NVL(wh.CLOSE_DATE,'04-APR-9999')>=TO_DATE(&versDATE}, 'YYYYMMDD')
pls help me in this issue..
Thank you,
1 2 3 8 Previous Next