Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > Oracle Development
Reload this Page

Query tuning

Discuss Query tuning in the Oracle Development forum on Dev Shed.
Query tuning Oracle Development forum discussing administration, Oracle queries, and other Oracle-related topics. Oracle is known as one of the most robust multi-platform relational databases available.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old April 15th, 2008, 01:56 AM
ganiraju ganiraju is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2008
Posts: 7 ganiraju User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 36 m 45 sec
Reputation Power: 0
Query tuning
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
[/pre]

Code:
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,
Last edited by pabloj : April 15th, 2008 at 12:49 PM.
Reply With Quote
  #2  
Old April 15th, 2008, 08:26 AM
vpire vpire is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2005
Posts: 167 vpire User rank is Corporal (100 - 500 Reputation Level)vpire User rank is Corporal (100 - 500 Reputation Level)vpire User rank is Corporal (100 - 500 Reputation Level)vpire User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 1 Day 9 h 35 m 50 sec
Reputation Power: 7
Quote:
Originally Posted by ganiraju
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
[/pre]

[pre]
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')
[/pre]
pls help me in this issue..
Thank you,


What did you try to achieve from the statement below
Code:
AND NVL(wh.CLOSE_DATE,'04-APR-9999')>=TO_DATE(&versDATE}, 'YYYYMMDD')

Also which fields did you index for 4 of the tables you mention above.
Reply With Quote
  #3  
Old April 15th, 2008, 11:06 AM
LKBrwn_DBA LKBrwn_DBA is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2006
Posts: 294 LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 2 Days 16 h 48 m 15 sec
Reputation Power: 10
Wink
In order to begin performance tunning your sql, I suggest you execute an explain plan and review if you are missing any indexes, ...etc...
__________________
Reply With Quote
  #4  
Old April 17th, 2008, 01:52 PM
mkhan mkhan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2008
Posts: 75 mkhan User rank is Sergeant (500 - 2000 Reputation Level)mkhan User rank is Sergeant (500 - 2000 Reputation Level)mkhan User rank is Sergeant (500 - 2000 Reputation Level)mkhan User rank is Sergeant (500 - 2000 Reputation Level)mkhan User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 2 Days 16 m 50 sec
Reputation Power: 7
Quote:
Originally Posted by ganiraju
AND NVL(wh.CLOSE_DATE,'04-APR-9999')>=TO_DATE(&versDATE}, 'YYYYMMDD')


I would use
AND (wh.CLOSE_DATE IS NULL OR wh.CLOSE_DATE>=TO_DATE(&versDATE}, 'YYYYMMDD')

Khan
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > Oracle Development > Query tuning

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway. 
Stay green...Green IT