Home » RDBMS Server » Performance Tuning » PL SQL Merge of tables - efficiency
PL SQL Merge of tables - efficiency [message #293012] Thu, 10 January 2008 10:28 Go to next message
buechler66
Messages: 7
Registered: March 2007
Junior Member
It takes the query below over 25 minutes to return only 500k rows. Is the use of virtual tables as implemented below the reason this query would have such poor performance? Can you recommend an alternative approach for merging all these tables? I ultimately want to create/refresh a snapshot using this data. Any insights/suggestions for a novice developer would be greatly appreciated!


select mail.mailpiece,
rep.reporter,
bun.bundle,
bun.origin_zip3,
rep.destination_zip3,
mail.kit,
bun.actual_induction_dt,
mail.week,
mail.receipt_dt,
bun.indicia_cd,
mail.status,
bun.origin_pc,
pc.destination_pc,
bun.scheduled_induction_dt,
rep.active_ind,
mail.svc,
(bun.actual_induction_dt + mail.svc) expected_exit_date,
mail.cancel_cd,
mail.project_cd,
mail.call_dt,
mail.adjusted_call_dt,
mail.match_cd,
mail.cancel_dt,
mail.RETURN_ADDRESS_ID,
mail.report_type_ind
from (select mailpiece,
bundle,
reporter,
week,
kit,
receipt_dt receipt_dt,
status,
svc,
cancel_cd cancel_cd,
call_dt call_dt,
adjusted_call_dt,
project_cd,
match_cd,
cancel_dt cancel_dt,
RETURN_ADDRESS_ID,
report_type_ind
from mailpiece
where week = 0815) mail,
(select bundle,
week,
actual_zip3 origin_zip3,
indicia_cd,
actual_pc origin_pc,
actual_dt actual_induction_dt,
sched_dt scheduled_induction_dt
from bundle
where week = 0815) bun,
(select reporter,
project_cd,
substr(zip,1,3) destination_zip3,
active_ind
from reporter) rep,
(select project_code project_cd,
pc_code destination_pc,
zip3
from performance_clusters) pc
where mail.bundle = bun.bundle and
mail.week = to_number(bun.week) and
mail.reporter = rep.reporter and
mail.project_cd = rep.project_cd and
rep.destination_zip3 = pc.zip3 and
rep.project_cd = pc.project_cd and
mail.week = 0815
/

Report message to a moderator

Re: PL SQL Merge of tables - efficiency [message #293014 is a reply to message #293012] Thu, 10 January 2008 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ A query that is formatted is not readable
2/ Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
3/ Post performances question in Performances forum
4/ Read again forum guide and provide requested data for performances question
5/ Also read How to Identify Performance Problem and Bottleneck.

Regards
Michel

[Updated on: Thu, 10 January 2008 10:32]

Report message to a moderator

Re: PL SQL Merge of tables - efficiency [message #293016 is a reply to message #293014] Thu, 10 January 2008 10:46 Go to previous messageGo to next message
buechler66
Messages: 7
Registered: March 2007
Junior Member
Oh I'm sorry. I guess my question is really more about the approach then it is about technical performance. Basically are Virtual Tables a good use in the example below? If not, is there a better approach.

If my question is too generic and vague I understand. Thank you anyway.

Oracle version 9.2.0.8.0

Corrected posting of the code:
select mail.mailpiece,
 rep.reporter,
 bun.bundle,
 bun.origin_zip3,
 rep.destination_zip3,
 mail.kit,
 bun.actual_induction_dt,
 mail.week,
 mail.receipt_dt,
 bun.indicia_cd,
 mail.status,
 bun.origin_pc,
 pc.destination_pc,
 bun.scheduled_induction_dt,
 rep.active_ind,
 mail.svc,
 (bun.actual_induction_dt + mail.svc) expected_exit_date,
 mail.cancel_cd,
 mail.project_cd,
 mail.call_dt,
 mail.adjusted_call_dt,
 mail.match_cd,
 mail.cancel_dt,
 mail.RETURN_ADDRESS_ID,
 mail.report_type_ind
from (select mailpiece,
 bundle,
 reporter,
 week,
 kit,
 receipt_dt receipt_dt,
 status,
 svc,
 cancel_cd cancel_cd,
 call_dt call_dt,
 adjusted_call_dt,
 project_cd,
 match_cd,
 cancel_dt cancel_dt,
 RETURN_ADDRESS_ID,
 report_type_ind
 from mailpiece
 where week = 0815)     mail,
 (select bundle,
  week,
  actual_zip3 origin_zip3,
  indicia_cd,
  actual_pc origin_pc,
  actual_dt actual_induction_dt,
  sched_dt scheduled_induction_dt
 from bundle
 where week = 0815)     bun,
 (select reporter,
  project_cd,
  substr(zip,1,3) destination_zip3,
  active_ind
 from reporter)     rep,
 (select project_code project_cd,
  pc_code destination_pc,
  zip3
 from performance_clusters)   pc
where mail.bundle = bun.bundle and
 mail.week = to_number(bun.week) and
 mail.reporter = rep.reporter and
 mail.project_cd = rep.project_cd and
 rep.destination_zip3 = pc.zip3 and
 rep.project_cd = pc.project_cd and
 mail.week = 0815
/

Report message to a moderator

Re: PL SQL Merge of tables - efficiency [message #293027 is a reply to message #293016] Thu, 10 January 2008 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Formating does not only mean using code tags, it also means indenting.
As said in the link I posted, OraFAQ provide a SQL Formater, why didn't you use it?

Quote:

Basically are Virtual Tables a good use in the example below?

I think you mean inline view. It depends on many parameters that you should provide as requested in the links I posted.

Regards
Michel

Report message to a moderator

Re: PL SQL Merge of tables - efficiency [message #293029 is a reply to message #293012] Thu, 10 January 2008 11:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
Which of the suggestions in the above URL have you done & what were the results?

Report message to a moderator

Re: PL SQL Merge of tables - efficiency [message #293057 is a reply to message #293012] Thu, 10 January 2008 14:17 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
you do know ORAFaq has a built in sql formatter right?

http://www.orafaq.com/utilities/sqlformatter.htm

all you had to do was paste it into the formatter and you get this... OK, so the formatter could use a little work I admit, but it is better than nothing.

SELECT Mail.MailPiece,
       rep.Reporter,
       Bun.Bundle,
       Bun.Origin_Zip3,
       rep.Destination_Zip3,
       Mail.Kit,
       Bun.Actual_Induction_dt,
       Mail.Week,
       Mail.Receipt_dt,
       Bun.Indicia_cd,
       Mail.Status,
       Bun.Origin_pc,
       pc.Destination_pc,
       Bun.Scheduled_Induction_dt,
       rep.Active_Ind,
       Mail.svc,
       (Bun.Actual_Induction_dt + Mail.svc) Expected_Exit_Date,
       Mail.Cancel_cd,
       Mail.Project_cd,
       Mail.Call_dt,
       Mail.Adjusted_Call_dt,
       Mail.Match_cd,
       Mail.Cancel_dt,
       Mail.Return_Address_Id,
       Mail.Report_Type_Ind
FROM   (SELECT MailPiece,
               Bundle,
               Reporter,
               Week,
               Kit,
               Receipt_dt Receipt_dt,
               Status,
               svc,
               Cancel_cd Cancel_cd,
               Call_dt Call_dt,
               Adjusted_Call_dt,
               Project_cd,
               Match_cd,
               Cancel_dt Cancel_dt,
               Return_Address_Id,
               Report_Type_Ind
        FROM   MailPiece
        WHERE  Week = 0815) Mail,
       (SELECT Bundle,
               Week,
               Actual_Zip3 Origin_Zip3,
               Indicia_cd,
               Actual_pc Origin_pc,
               Actual_dt Actual_Induction_dt,
               scHed_dt Scheduled_Induction_dt
        FROM   Bundle
        WHERE  Week = 0815) Bun,
       (SELECT Reporter,
               Project_cd,
               Substr(Zip,1,3) Destination_Zip3,
               Active_Ind
        FROM   Reporter) rep,
       (SELECT Project_Code Project_cd,
               pc_Code Destination_pc,
               Zip3
        FROM   Performance_Clusters) pc
WHERE  Mail.Bundle = Bun.Bundle
       AND Mail.Week = To_number(Bun.Week)
       AND Mail.Reporter = rep.Reporter
       AND Mail.Project_cd = rep.Project_cd
       AND rep.Destination_Zip3 = pc.Zip3
       AND rep.Project_cd = pc.Project_cd
       AND Mail.Week = 0815
/


As a general rule, inline views as you have here make little difference to the overall performance question. Oracle will rewrite the query as it sees fit. There are of course exceptions to every rule and sometimes inline views can have a dramatic affect on performance either good or bad. But as a practice, you should use inline views not for performance reasons, but for readability and maintainability reasons.

Said another way, an inline view like this (select statement as table in a from clause) does mean "materialize these rows". There is of course a hint for this if you want to materialize an inline view, but most of the time you should leave Oracle to that decision, as figuring out if this would help is a pretty advanced tuning practice.

as proof of your understanding of inline views, you do understand that the last line of your query is unnecessary right?

and that the use of To_number(Bun.Week) suggests a small flaw in the query somewhere?

Not sure if I have helped but there you go. Kevin Meade

Report message to a moderator

Re: PL SQL Merge of tables - efficiency [message #293088 is a reply to message #293057] Thu, 10 January 2008 20:18 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why don't you paste in an Explain Plan - or better yet TKPROF output. Take a look at the links above for instructions.

Report message to a moderator

Re: PL SQL Merge of tables - efficiency [message #293338 is a reply to message #293012] Fri, 11 January 2008 13:30 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
posting a fattfinger correction. My previous post said

Quote:

Said another way, an inline view like this (select statement as table in a from clause) does mean "materialize these rows".

but should have said "does NOT mean materialize these rows".

sorry, Kevin

Report message to a moderator

Previous Topic: table access count
Next Topic: works in test env but very slow in production
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 05:17:04 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.