Home » SQL & PL/SQL » SQL & PL/SQL » Aggregate Functions Within Inline Views
Aggregate Functions Within Inline Views [message #390242] Thu, 05 March 2009 10:23 Go to next message
fearthegrimp
Messages: 6
Registered: March 2009
Junior Member
Hi folks, new user here so please be gentle!!

I am running a query in TOAD combining several large(ish) indexed tables (records in the hundreds of thousands) with an incorporated inline view and an assortment of filters. All fine and dandy - 1100 records returned in less than a minute.

I'm asked to add a couple of aggregated totals (one counting associated records and one checking for a latest date). Both these are selected as unfiltered recordsets and are incorporated into my original query as outer joined inline views.

I run the query - it works, bringing back the same 1100 records but now takes almost 3 hours to complete!!!

Is this to be expected?

I can include my (rather messy) code if required...

Thanks!
Re: Aggregate Functions Within Inline Views [message #390243 is a reply to message #390242] Thu, 05 March 2009 10:25 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Can you provide DDL And DML Scripts for your requirement
Re: Aggregate Functions Within Inline Views [message #390244 is a reply to message #390242] Thu, 05 March 2009 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Aggregate Functions Within Inline Views [message #390245 is a reply to message #390242] Thu, 05 March 2009 10:30 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also post explain plans for both old and new queries
Re: Aggregate Functions Within Inline Views [message #390267 is a reply to message #390245] Thu, 05 March 2009 12:18 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Oracle tries to push predicates as deep as possible to eliminate unnecessary data retrieval as soon as possible. Predicate pushing is blocked by aggregate function amounst other constructs. Search for "predicate pushing".
http://www.orafaq.com/forum/mv/msg/100064/311137/0/#msg_311278

There are a few tricks though. Search for "paramaterized views". Using this, you may be able to force a predicate deeper into your query.
http://www.orafaq.com/forum/t/121085/0/
Re: Aggregate Functions Within Inline Views [message #390313 is a reply to message #390267] Thu, 05 March 2009 19:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Aggregation blocks view-merging, but not necessarily predicate-pushing. It will only block if the predicate is on an aggregated column. If you are joining on a GROUP BY column it can be OK.

Ross Leishman
Re: Aggregate Functions Within Inline Views [message #390382 is a reply to message #390313] Fri, 06 March 2009 03:23 Go to previous messageGo to next message
fearthegrimp
Messages: 6
Registered: March 2009
Junior Member
Thanks folks, as I said before I'm quite new to all this. I've formatted my query and it's shown below and I attach the Explain Plan (which baffles me!). Does this help?
SELECT   iv1."Territory", 
         geog_unit.geog_unit_name "Area", 
         iv1."ELR",
         iv1."Mileage", 
         iv1."Railway ID", 
         iv1."Exam Type", 
         iv1."Exam ID",
         iv1."Due Date", 
         iv1."Interval - Years", 
         iv1."Interval - Months",
         iv1."Interval - Days", 
         iv1."Exam Status",
         iv1."Specific Exam Requirement", 
         rv.rv_desc "Asset Group", 
         et.element_type_desc "Asset Type",
         iv2."Number of Summaries", 
         iv3."Signed-off Summaries",
         iv4."Latest Exam Date"
FROM     geog_unit,
         element_type et,
         reference_value rv,
         (SELECT geog_unit.geog_unit_name "Territory", 
                 ELEMENT.area_id,
                 element_elr.elr_code "ELR",
                 (element_elr.start_miles + element_elr.start_yards / 10000) 
                                                             "Mileage",
                 business_ref "Railway ID", 
                 exam_type_desc "Exam Type",
                 examination.exam_id "Exam ID", 
                 exam_due_date "Due Date",
                 interval_years "Interval - Years",
                 interval_months "Interval - Months",
                 interval_days "Interval - Days",
                 rv_desc "Exam Status",
                 exam_group_exam_type_cycle.specific_exam_requirements
                                               "Specific Exam Requirement",
                 ELEMENT.element_type_id
         FROM    examination,
                 element_elr,
                 exam_group_element,
                 exam_status_hist,
                 ELEMENT,
                 exam_group_exam_type_cycle,
                 exam_type,
                 reference_value,
                 geog_unit
        WHERE    exam_group_element.element_id = ELEMENT.element_id
          AND    exam_type.exam_type_id = examination.exam_type_id
          AND    exam_group_exam_type_cycle.exam_group_id =
                                           exam_group_element.exam_group_id
          AND    ELEMENT.element_id = element_elr.element_id
          AND    exam_group_element.exam_group_id = examination.exam_group_id
          AND    exam_status_hist.exam_id = examination.exam_id
          AND    exam_status_id NOT IN (2376, 2378)
          AND    (exam_status_hist.exam_id, exam_status_seq) IN 
                         (SELECT   exam_id,
                                   MAX (exam_status_seq)
                          FROM exam_status_hist
                          GROUP BY exam_id)
          AND examination.exam_type_id IN (1, 9)
          AND exam_group_exam_type_cycle.exam_type_id IN (1, 9)
          AND examination.deleted_flg = 'N'
          AND examination.exam_type_id =
                                    exam_group_exam_type_cycle.exam_type_id
          AND examination.exam_group_exam_type_cycle_id =
                   exam_group_exam_type_cycle.exam_group_exam_type_cycle_id
          AND ELEMENT.territory_id = geog_unit.geog_unit_id
          AND rv_id = exam_status_id) iv1,
         (SELECT exam_id, 
                  COUNT (exam_id) "Number of Summaries"
           FROM exam_summary
           WHERE    deleted_flg = 'N'
           GROUP BY exam_id) iv2,
         (SELECT exam_id, 
                 COUNT (exam_id) "Signed-off Summaries"
           FROM exam_summary
           WHERE    exam_status_id = 2308 AND deleted_flg = 'N'
           GROUP BY exam_id) iv3,
         (SELECT exam_id, 
                 MAX (exam_date) "Latest Exam Date"
           FROM exam_summary
           WHERE deleted_flg = 'N'
           GROUP BY exam_id) iv4
WHERE iv1.area_id = geog_unit.geog_unit_id
AND   iv1.element_type_id = et.element_type_id
AND   iv1."Exam ID" = iv2.exam_id(+)
AND   iv1."Exam ID" = iv3.exam_id(+)
AND   iv1."Exam ID" = iv4.exam_id(+)
AND   et.asset_group_id = rv.rv_id
ORDER BY "Territory", "ELR", "Mileage", "Railway ID", "Due Date";


[Mod-edit: Frank added code-tags]
  • Attachment: PLAN.JPG
    (Size: 126.07KB, Downloaded 92 times)

[Updated on: Fri, 06 March 2009 03:30] by Moderator

Report message to a moderator

Re: Aggregate Functions Within Inline Views [message #390383 is a reply to message #390242] Fri, 06 March 2009 03:25 Go to previous messageGo to next message
fearthegrimp
Messages: 6
Registered: March 2009
Junior Member
Sorry, it was indented when I pasted it, but my formatting seems to have been lost when I pressed Submit!!
Re: Aggregate Functions Within Inline Views [message #390386 is a reply to message #390383] Fri, 06 March 2009 03:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Surround your code with [code] and [/code] tags to preserve formatting when posting.
I'll modify your post
Re: Aggregate Functions Within Inline Views [message #390389 is a reply to message #390382] Fri, 06 March 2009 03:43 Go to previous message
fearthegrimp
Messages: 6
Registered: March 2009
Junior Member
Thanks Frank!
Previous Topic: which one is faster
Next Topic: Foreign Key and Primary Key Details...
Goto Forum:
  


Current Time: Tue Dec 06 10:31:56 CST 2016

Total time taken to generate the page: 0.05324 seconds