Home » SQL & PL/SQL » SQL & PL/SQL » Converting inline views to temp tables (10.2.0.3.0)
Converting inline views to temp tables [message #399361] Tue, 21 April 2009 16:14 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
My question is how would I convert inline views to temp tables?

I have a report that has 4 different inline views and each inline view is creating a column in the main query of the report. But this is causing too many problems for my report because of the various tables being referenced, so I want to just convert some of the inline views to temp tables and then join them to the main query.

How would I do this?



Anne


Re: Converting inline views to temp tables [message #399374 is a reply to message #399361] Tue, 21 April 2009 20:09 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>How would I do this?
CREATE TABLE INLINE1 AS SELECT ........
Re: Converting inline views to temp tables [message #399375 is a reply to message #399374] Tue, 21 April 2009 20:45 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Thanks and from the temp table that I create I can join it to the main query.

Thanks again BlackSwan.


Anne
Re: Converting inline views to temp tables [message #399376 is a reply to message #399361] Tue, 21 April 2009 20:53 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>Thanks and from the temp table that I create I can join it to the main query.
Realize that SQL parser does not know or care that the table being discussed whether or not is "temp" or not.

A table is a table is a table.

Is a new table really required?
Q: What is the difference between a "temp table" & a view?
A: A view does not duplicate data & does not require any "clean up".

Re: Converting inline views to temp tables [message #399393 is a reply to message #399361] Wed, 22 April 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle is NOT Sybase, you don't need temp tables for this, more it is inefficient.

Regards
Michel
Re: Converting inline views to temp tables [message #399444 is a reply to message #399361] Wed, 22 April 2009 04:22 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're doing this for that monsterous query you were asking about the other week materialized views might be more usefull.
Re: Converting inline views to temp tables [message #399451 is a reply to message #399444] Wed, 22 April 2009 04:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Materialized views would definitely be better - you can automatically refresh them to get up-to-date data in them
Re: Converting inline views to temp tables [message #399734 is a reply to message #399444] Thu, 23 April 2009 15:25 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Yes, I want to perform materialized views for the monsterous report. Smile

1. So would I create the materialized view for each inline view..I have 4 inline views?

2. Would the syntax for creating the materialized go like the following:

create materialized view b_inline_view
build immediate
refresh on commit
enable query rewrite
as
SELECT /*+ NO_CPU_COSTING */ ag.award_id, 
       gra.project_id, 
       gra.task_id, 
       gra.budget_version_id, 
       SUM(NVL(bl.burdened_cost, 0)) budget_total, 
       SUM(CASE SUBSTR(r.NAME, 1, 10) WHEN 'Award Reve' THEN NVL(bl.burdened_cost, 0) 
                                      WHEN 'F & A Cost' THEN NVL(bl.burdened_cost, 0) 
                                      ELSE 0 END) indirect_budget, 
       SUM(CASE SUBSTR(r.NAME, 1, 10) WHEN 'Award Reve' THEN 0 
                                      WHEN 'F & A Cost' THEN 0 
                                      WHEN 'Cost Share' THEN 0 
                                      ELSE NVL(bl.burdened_cost, 0) END) direct_budget, 
       SUM(CASE SUBSTR(r.NAME, 1, 10) WHEN 'Cost Share' THEN NVL(bl.burdened_cost, 0) 
                                      ELSE 0 END) cs_budget 
  FROM apps.gms_budget_versions BV1, 
       apps.pa_resource_list_members rlm, 
       apps.pa_resources r, 
       apps.gms_awards_all ag, 
       apps.gms_budget_lines bl, 
       apps.gms_resource_assignments gra 
 WHERE gra.budget_version_id IN (SELECT MAX(BV2.budget_version_id) 
                                   FROM gms.gms_budget_versions BV2 
                                  WHERE BV2.project_id = gra.project_id) 
   AND BV1.award_id = ag.award_id + 0 
   AND rlm.resource_id = r.resource_id 
   AND gra.resource_list_member_id = rlm.resource_list_member_id 
   AND gra.resource_assignment_id = bl.resource_assignment_id + 0 
   AND gra.project_id = BV1.project_id + 0 
   AND gra.budget_version_id = BV1.budget_version_id + 0 
 GROUP BY ag.award_id, gra.project_id, gra.task_id, gra.budget_version_id;







Re: Converting inline views to temp tables [message #399800 is a reply to message #399734] Fri, 24 April 2009 02:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why have you got all those '+ 0' terms on your join conditions?

I'd guess it was some half baked attempt to force the optimiser into a specific query path that your statistics don't justify.
Re: Converting inline views to temp tables [message #399805 is a reply to message #399800] Fri, 24 April 2009 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom wrote on Fri, 24 April 2009 09:57
Why have you got all those '+ 0' terms on your join conditions

Just someone trying to stupidely uses something she does not understand and that is more than 10 years old and is now totally irrelevant and inappropriate.

Regards
Michel

Re: Converting inline views to temp tables [message #399828 is a reply to message #399805] Fri, 24 April 2009 04:16 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It may be outdated, but it'll still stop the optimiser using the indexes, regardless of how wise that might be.
Previous Topic: Oracle 11g Pivot for Subquery
Next Topic: to_char(0.1) result is ,1 but must be 0,1
Goto Forum:
  


Current Time: Fri Dec 02 12:09:52 CST 2016

Total time taken to generate the page: 0.11388 seconds