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

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old October 19th, 2004, 11:31 PM
ProggerPete ProggerPete is offline
Contributing User
Dev Shed Beginner (1000 - 1499 posts)
 
Join Date: May 2003
Location: Brisbane, Australia
Posts: 1,438 ProggerPete User rank is Sergeant (500 - 2000 Reputation Level)ProggerPete User rank is Sergeant (500 - 2000 Reputation Level)ProggerPete User rank is Sergeant (500 - 2000 Reputation Level)ProggerPete User rank is Sergeant (500 - 2000 Reputation Level)ProggerPete User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Week 1 Day 1 h 16 m 25 sec
Reputation Power: 23
Force oracle to evaluate one of my predicates 1st

Hi, I've got a reasonably big query that looks like this.

Code:
INSERT INTO call_analysis
SELECT folder_fk, 
	   decode(to_char(date_call, 'HH24:MI:SS'), '00:00:00', 'Unknown', to_char(date_call, 'HH24')) As Hour,
	   servnum,
	   serv_dialled As Dest_Servnum,
	   cat_desc,
	   sum(pct / 100) AS Qty,
	   sum(charge * pct / 100.0) as cost,
	   avg(decode(duration, '', 0,duration)) AS AVG_Duration,
	   a.customer_fk
FROM (
	 SELECT NVL(serv_split.customer_fk, service.customer_fk) as customer_fk,
	 service.servnum,
	 NVL(pct, 100) AS pct,
	 serv_type_desc,
	 NVL(description, 'Unallocated') AS description
	 FROM service
	 LEFT JOIN serv_split ON service.servnum = serv_split.servnum
	 JOIN serv_type ON serv_type_fk = serv_type_pk
	 LEFT JOIN service_allocations ON service.servnum = service_allocations.servnum
	 LEFT JOIN allocation_categories ON allocation_categories.allocation_category_id = service_allocations.allocation_category_id
	 ) a
JOIN customer ON a.customer_fk = customer.customer_pk
JOIN ui ON a.servnum = ui.servnum AND NOT EXISTS (SELECT 0 FROM analysed_mids WHERE mid = ui.mid)
JOIN dt ON ui.mid = dt.mid
join cat on ui.cat_ref = cat.cat_ref
GROUP BY folder_fk,
	  	 decode(to_char(date_call, 'HH24:MI:SS'), '00:00:00', 'Unknown', to_char(date_call, 'HH24')),
		 servnum,
		 serv_dialled,
		 cat_desc,
		 a.customer_fk

Don't get to caught up in trying to follow exactly what's happening. The line I'm concerned with is
Code:
JOIN ui ON a.servnum = ui.servnum AND NOT EXISTS (SELECT 0 FROM analysed_mids WHERE mid = ui.mid)

UI is a BIG table so I want the cull on whether or not the mid value exists to be pretty much the 1st thing that happens. At the moment it takes about 9 minutes to run regardless of whether the analysed_mids table contains all or none of the mids in ui.

I figure if I could modify my query so it checks out the mid value of ui 1st then It would run a lot faster.

Any ideas?
__________________
Like the answers I give? Why not ask me directly at my forum. I'm always glad to help.

Javascript scripts and tips can be found at Dynamic Tools.
Check out DynamicTable, the best javascript table sorter around.
Get reliable and affordable hosting at www.thinksmarthosting.com

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesOracle Development > Force oracle to evaluate one of my predicates 1st


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
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 4 hosted by Hostway
Stay green...Green IT