| Optimizing select from view [message #487181] |
Thu, 23 December 2010 09:31  |
 |
Dimidrol
Messages: 6
Registered: December 2010
Location: New York
|
Junior Member |
|
|
Hi , I have a view, below, which does few left outer joins to the same V_MARKET view to get data i need. When I run SQL by itself, ut runs pretty fast, 2-5 seconds. But when I do "select * from V_DEPT_DATA where busines_date = '01-APR-10'", it takes more than 10 minutes to run. I added all needed indexes and still have problems with it . Please help.
CREATE OR REPLACE VIEW V_DEPT_DATA
AS
SELECT
v1.business_date ,
v1.division ,
v1.department ,
v1.account ,
en.trader ,
en.desk_manager
FROM V_MARKET v1
LEFT OUTER JOIN V_MARKET v2
ON (v1.LAST_BUS_DATE = v2.BUSINESS_DATE
AND v1.KEY = v2.KEY
AND v1.LEVEL = v2.LEVEL )
LEFT OUTER JOIN V_MARKET V5
ON (V1.LAST5_BUS_DATE = V5.business_date
AND V1.key = V5.key
AND V1.level = V5.level)
LEFT OUTER JOIN entity en
ON (v1.account = en.ENTITY_NAME
AND en.ENTITY_TYPE = 'account')
WHERE V1.KEY IN (3,4)
* < code tags > added by BlackSwan. Please do so yourself in the future
[Updated on: Thu, 23 December 2010 10:32] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Optimizing select from view [message #487186 is a reply to message #487184] |
Thu, 23 December 2010 10:12  |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You need to give us the explain plan for the query, the view definition of v_market and the list of indexes on the relevant tables.
Why are you joining to v_market twice when you're not selecting anything from the outer-joined instances of that view (v2 and v5)?
|
|
|
|
| Re: Optimizing select from view [message #487189 is a reply to message #487186] |
Thu, 23 December 2010 10:30 |
|
Dimidrol
Messages: 6
Registered: December 2010
Location: New York
|
Junior Member |
|
|
My apologies. This is the actual SQL for the view. I attached the explain plan as well. As for your question, the reason I'm joining the same view multiple times is that, for a given business_date I need to find current_date (business_date)previous_date( prev_bus_date)and previous5_date (prev5_bus_date) values. All these columns are in V_MRK_RISK_VAR view. By passing business_date as a parameter, I can find all three values in one shot.
SELECT v1.business_date,
v1.aggregation_key,
v1.division,
v1.department,
v1.account,
v1.var_data_key,
en.trader,
en.desk_manager,
en.products_traded,
en.strategy,
en.risk_manager,
en.backup_risk_manager
FROM v_mkt_risk_var v1
left outer join v_mkt_risk_var v2
ON ( v1.prev_bus_date = v2.business_date
AND v1.aggregation_key = v2.aggregation_key
AND v1.aggregation_level = v2.aggregation_level
AND ROWNUM = 1 )
left outer join v_mkt_risk_var v5
ON ( v1.prev5_bus_date = v5.business_date
AND v1.aggregation_key = v5.aggregation_key
AND v1.aggregation_level = v5.aggregation_level
AND ROWNUM = 1 )
left outer join entity en
ON ( v1.account = en.entity_name
AND en.entity_type = 'account' )
inner join (SELECT long_date
FROM date_dim a
WHERE EXISTS (SELECT 'x'
FROM mkt_risk_var
WHERE business_date = a.long_date)) dim_dates
ON ( v1.business_date = dim_dates.long_date )
WHERE v1.aggregation_key IN ( 3, 4 )
AND v1.business_date BETWEEN v1.business_date - 1 AND
v1.business_date + 1
[Updated on: Thu, 23 December 2010 10:34] by Moderator Report message to a moderator |
|
|
|
| Re: Optimizing select from view [message #487191 is a reply to message #487189] |
Thu, 23 December 2010 10:57 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Dimidrol wrote on Thu, 23 December 2010 16:30for a given business_date I need to find current_date (business_date)previous_date( prev_bus_date)and previous5_date (prev5_bus_date) values.
So why aren't you selecting them?
Outer joining to a table or view without selecting anything from it is functionaly equivalent to not joining at all.
Dimidrol wrote on Thu, 23 December 2010 16:30
All these columns are in V_MRK_RISK_VAR view.
So?
|
|
|
|
| Re: Optimizing select from view [message #487193 is a reply to message #487191] |
Thu, 23 December 2010 11:18 |
|
Dimidrol
Messages: 6
Registered: December 2010
Location: New York
|
Junior Member |
|
|
These are the columns I'm adding to the select.
v1.VAR95_HIST_TOTAL AS VAR95_HIST_TOTA_cbd ,
v2.VAR95_HIST_TOTAL AS VAR95_HIST_TOTA_pbd ,
v5.VAR95_HIST_TOTAL AS VAR95_HIST_TOTA_p5bd ,
They are totals, based on passed business_date. In other words, when I pass a BUSINESS_DATE to the view, it evaluates PREV_BUS_DATE, PREV5_BUS_DATE colums, whether they contain the same date as BUSINESS_DATE. If any of them does, I will get "VAR95_HIST_TOTAL" for it, for that date. I ran this multiple times and this approach works, it's performance which kills it.
|
|
|
|
| Re: Optimizing select from view [message #487194 is a reply to message #487193] |
Thu, 23 December 2010 11:28 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Ok
1) Can you please read and follow the orafaq forum guide - especially the part on how to format your post.
2) Having done that can you post the following in the thread (not attachments), in [code] tags:
a) the revised definition of V_DEPT_DATA
b) the explain plan for it
c) the definition of v_mkt_risk_var
d) The list of indexes on the relevant tables.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
