Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> how to use view on two queires joined by union all
alok
post Mar 20 2008, 06:03 AM
Post #1


Newbie
*

Group: Members
Posts: 7
Joined: 18-March 08
Member No.: 19,360



i have 2 queries which joined by union all..i want to use view and eleminate that union all...so kindly tell me how to do with codes...

2 queries having similar output but little differ..
Go to the top of the page
 
+Quote Post
wickedman84
post Mar 20 2008, 06:28 AM
Post #2


Newbie
*

Group: Members
Posts: 1
Joined: 20-March 08
Member No.: 19,499



CREATE OR REPLACE FORCE VIEW VIEW_NAME ("COL_1", "COL_2", "COL_3", "COL_4", "COL_5", "COL_6 ")
AS SELECT distinct A.COL1, A.COL_2, A.COL_3, B.COL_4, B.COL_5 C.COL_6
FROM
TABLE1 A,
TABLE2 B,
TABLE3 C
where
A.COL_1 = B.??? AND
A.COL_2= C.???
Go to the top of the page
 
+Quote Post
burleson
post Mar 20 2008, 06:48 AM
Post #3


Advanced Member
***

Group: Members
Posts: 11,634
Joined: 26-January 04
Member No.: 13



Hi Alok,

>> i want to use view and eleminate that union all...

Why? A view only simplifies the SQL, and has no bearing on performance.

Have you considered a materialized view?

http://www.dba-oracle.com/t_materialized_views_tuning.htm

create materialized view fred as
select stuff from tab1
unionall
select stuff from tab2;


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
alok
post Mar 20 2008, 07:04 AM
Post #4


Newbie
*

Group: Members
Posts: 7
Joined: 18-March 08
Member No.: 19,360



QUOTE (wickedman84 @ Mar 20 2008, 11:29 AM) *
CREATE OR REPLACE FORCE VIEW VIEW_NAME ("COL_1", "COL_2", "COL_3", "COL_4", "COL_5", "COL_6 ")
AS SELECT distinct A.COL1, A.COL_2, A.COL_3, B.COL_4, B.COL_5 C.COL_6
FROM
TABLE1 A,
TABLE2 B,
TABLE3 C
where
A.COL_1 = B.??? AND
A.COL_2= C.???


*****************************

the following two queries i have to optimize and eleminate union all..
please kindly go through and tell me how to do so....




select ua.ACCOUNTID accountGroupID,
1 accountOrGroup,
ua.ACCOUNTNAME groupName,
ua.MANAGERID portfolioManagerid,
ua.OWNERID portfolioOwnerid,
ua.ACCOUNTNAME portfolioAccountName,
ua.FIRSTNAME ownerFirstname,
ua.LASTNAME ownerLastname,
NVL(ua.STATE, '-') ownerState,
NVL(ua.ZIP, '-') ownerZip,
ua.COUNTRY ownerCountry,
ua.LANGUAGE,
p.PORTFOLIOID portfolioid,
p.PORTFOLIONAME portfolioname,
'na' sourcename, -- doesn't exist yet
sum(case h.HOLDING_TYPE
when 1 then decode (s.asset_class,12,
(h.QUANTITY*oi.CONTRACT_SIZE*s.LASTPRICE*s.PRICEFACTOR*pfx.RATIO/sfx.RATIO),
(h.QUANTITY*s.LASTPRICE*s.PRICEFACTOR*pfx.RATIO/sfx.RATIO)) --market value for an equity
else (h.QUANTITY/sfx.RATIO) --market value for cash
end
) marketValue, -- only uses last price (not delayed last price)
sum(case h.HOLDING_TYPE
when 1 then decode (s.asset_class,12,
(h.QUANTITY*oi.CONTRACT_SIZE*s.CLOSEDPRICE*s.PRICEFACTOR*pfx.RATIO/sfx.RATIO),
(h.QUANTITY*s.CLOSEDPRICE*s.PRICEFACTOR*pfx.RATIO/sfx.RATIO))
else h.QUANTITY/sfx.RATIO
end
) lastCloseValue,
0 ytdChange, --Don't know how to get this right now
p.CURRENCY currency,
NVL(ua.CITY, '-') ownerCity,
UPPER(ua.ACCOUNTNAME) uppername
from useraccount ua,
portfolio p,
fx pfx,
holding h,
stock s,
options_info oi,
fxday sfx
where ua.MANAGERID = 122866and ua.ACCOUNTID != ua.MANAGERID
and ua.ACCOUNTID = p.ACCOUNTID
and p.portfolioname not in ('Total Book','CurrentUnion','CompareCurrentUnion')
and pfx.CURRENCY = p.CURRENCY
and h.PORTFOLIOID = p.PORTFOLIOID
and h.STOCKID = s.STOCKID
and sfx.CURRENCY = s.CURRENCY
and sfx.timestamp = s.lastupdateday
and s.stockid=oi.optionsid (+)

group by ua.ACCOUNTID, ua.ACCOUNTNAME, ua.MANAGERID, ua.OWNERID,
ua.ACCOUNTNAME, ua.FIRSTNAME, ua.LASTNAME, ua.STATE, ua.ZIP,
ua.COUNTRY, ua.LANGUAGE, p.PORTFOLIOID, p.PORTFOLIONAME,
p.CURRENCY, ua.city
UNION ALL
select ua.ACCOUNTID accountGroupID,
1 accountOrGroup,
ua.ACCOUNTNAME groupName,
ua.MANAGERID portfolioManagerid,
ua.OWNERID portfolioOwnerid,
ua.ACCOUNTNAME portfolioAccountName,
ua.FIRSTNAME ownerFirstname,
ua.LASTNAME ownerLastname,
NVL(ua.STATE, '-') ownerState,
NVL(ua.ZIP, '-') ownerZip,
ua.COUNTRY ownerCountry,
ua.LANGUAGE,
p.PORTFOLIOID portfolioid,
p.PORTFOLIONAME portfolioname,
'na' sourcename, -- doesn't exist yet
sum((h.QUANTITY/pfx.RATIO)) --market value for cash
marketValue, -- only uses last price (not delayed last price)
sum(h.QUANTITY/pfx.RATIO) lastCloseValue,
0 ytdChange, --Don't know how to get this right now
p.CURRENCY currency,
NVL(ua.CITY, '-') ownerCity,
UPPER(ua.ACCOUNTNAME) uppername
from useraccount ua,
portfolio p,
fx pfx,
holding h
where ua.MANAGERID = 122866and ua.ACCOUNTID != ua.MANAGERID
and ua.ACCOUNTID = p.ACCOUNTID
and p.portfolioname not in ('Total Book','CurrentUnion','CompareCurrentUnion')
and h.PORTFOLIOID = p.PORTFOLIOID
and h.holding_type = 2
AND pfx.currency = H.stockID
group by ua.ACCOUNTID, ua.ACCOUNTNAME, ua.MANAGERID, ua.OWNERID,
ua.ACCOUNTNAME, ua.FIRSTNAME, ua.LASTNAME, ua.STATE, ua.ZIP,
ua.COUNTRY, ua.LANGUAGE, p.PORTFOLIOID, p.PORTFOLIONAME,
p.CURRENCY, ua.city,h.holding_type
Go to the top of the page
 
+Quote Post
HAL9000
post Mar 20 2008, 07:24 AM
Post #5


Advanced Member
***

Group: Members
Posts: 880
Joined: 25-September 07
Member No.: 12,336



"please kindly go through and tell me how to do so"

This forum is for asking questions!

WhY? A union all is a fast approach.

This is homework, from the sample EJB application:

http://www.oracle.com/technology/sample_co...bql/Readme.html

You can remove a union all with:

- A natural join
- global temporary tables
- The Materialize hint
- The WITH clause

http://www.dba-oracle.com/t_materialize_sql_hint.htm
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 31st October 2014 - 10:55 PM