1 2 3 Previous Next 117 Replies Latest reply: May 5, 2008 12:19 PM by 181444 RSS

    query tuning

    599921
      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 Previous Next