
October 19th, 2004, 11:31 PM
|
|
Contributing User
|
|
Join Date: May 2003
Location: Brisbane, Australia
|
|
|
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
|