Home » SQL & PL/SQL » SQL & PL/SQL » RATIO_TO_REPORT
RATIO_TO_REPORT [message #446926] Thu, 11 March 2010 00:27 Go to next message
preeti_khurana
Messages: 15
Registered: May 2008
Location: Gurgaon
Junior Member
Hi Gurus


I have an existing query in my application which uses function ratio_to_report.
example:
select ename ,curr_salary,portion,sum (portion2)over (order by portion2 desc) as portion3
from
(SELECT ename, curr_salary,
curr_salary/SUM(curr_salary) OVER() Portion,
100*RATIO_TO_REPORT(curr_salary) OVER() Portion2
FROM employee) e
ORDER BY curr_salary;

Now by using ratio_to_report and analytical fuction SUM in the previous query makes the cost very high.
I want to tune this query so that the response time reduces.
This is just an example i am giving otherwise my query is very big.

I also explored the alternative of ratio_to_report but no success with that also.
response time is not reducing.

Please help.

Thanks
Preeti Khurana
Re: RATIO_TO_REPORT [message #446938 is a reply to message #446926] Thu, 11 March 2010 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you ask us is to optimize a query we don't see from another query that is an example we don't know what it tries to achieve and if it is representative if the actual one.
I can't.

For instance,
Quote:
sum (portion2)over (order by portion2 desc) as portion3

What does this intends to do?

Regards
Michel


Re: RATIO_TO_REPORT [message #446954 is a reply to message #446938] Thu, 11 March 2010 01:20 Go to previous message
preeti_khurana
Messages: 15
Registered: May 2008
Location: Gurgaon
Junior Member
Hi

i am sending u the actual query which i need to tune.
Please have a look. i think the problem area is ratio_to_report and the sum functions.

SELECT * FROM ( WITH user_selection AS (
SELECT * FROM ( SELECT
event_key, pf_include_flag, rank,
product_key, vat_decimal, ext_product_id, sku_number,
code, super_store_key, super_store_name,
zone_key, description, original_price,
original_units, new_price, current_price,
price_diff, new_units, current_units,
unit_diff, new_revenue, current_revenue,
revenue_change, new_margin, user_margin,
current_margin, margin_diff, margin_diff_bp,
comp_price, min_comp, max_comp, min_histsale_price,
max_histsale_price, cur_price_index,
opt_price_index, frcst_price_index,
cost, user_cost, new_profit, user_profit,
current_profit, profit_diff,
constraint_type, setby, price_family_name,
ap_price_family_key, dg_key, dg_description, begin_date,
end_date, node_name, node_key,
user_price, user_units, user_sales,
taxrate, price_chg_user_key,
price_accept_user_key, strategy_description,
eff_size, sell_uom_code, weight, row_key,
reason_key, elasticity, r_squared,
100*(raw_margin_pct/COUNT(*) OVER()) AS cume_margin_pct,
SUM(raw_sales_pct) OVER(ORDER BY raw_sales_pct DESC) AS cume_sales_pct,
SUM(raw_profit_pct) OVER(ORDER BY raw_profit_pct DESC) AS cume_profit_pct,
SUM(raw_units_pct) OVER(ORDER BY raw_units_pct DESC) AS cume_units_pct
,zone_name
,brand_name
,mfg_name
,units_per_case
FROM ( SELECT
optwb.event_key, optwb.pf_include_flag,
optwb.rank, optwb.product_key, optwb.vat_decimal,
mstprod.ext_product_id, optwb.sku_number,
optwb.code, optwb.super_store_key,
optwb.super_store_name, zssxref.zone_key,
optwb.description, optwb.original_price,
optwb.original_units, optwb.new_units,
ROUND(optwb.current_units, 1) AS current_units,
ROUND(optwb.unit_diff, 1) AS unit_diff,
ROUND(optwb.new_price, 2) AS new_price,
ROUND(optwb.current_price, 2) AS current_price,
ROUND(optwb.price_diff, 2) AS price_diff,
ROUND(optwb.new_revenue, 2) AS new_revenue,
ROUND(optwb.current_revenue, 2) AS current_revenue,
ROUND(optwb.revenue_diff, 2) AS revenue_change,
ROUND(optwb.new_margin, 4) AS new_margin,
ROUND(optwb.user_margin, 4) AS user_margin,
ROUND(optwb.current_margin, 4) AS current_margin,
ROUND(optwb.margin_diff_bp, 1) AS margin_diff_bp,
optwb.margin_diff,
ROUND(optwb.new_profit, 2) AS new_profit,
ROUND(optwb.user_profit, 2) AS user_profit,
ROUND(optwb.current_profit, 2) AS current_profit,
(ROUND(optwb.user_profit, 2) - ROUND(optwb.current_profit, 2)) AS profit_diff,
comp_price, optwb.min_comp,
optwb.max_comp, optwb.min_histsale_price,
optwb.max_histsale_price, cur_price_index,
opt_price_index, frcst_price_index,
ROUND(optwb.cost, 2) AS cost,
ROUND(optwb.user_cost, 2) AS user_cost,
optwb.constraint_type,
optwb.setby, optwb.price_family_name,
optwb.ap_price_family_key, optwb.dg_key, rdg.dg_description,
optwb.dpc_begin_date as begin_date,
optwb.dpc_end_date AS end_date,
snm.name AS node_name, optwb.node_key,
optwb.user_price, optwb.user_units,
optwb.user_sales, optwb.taxrate,
optwb.price_chg_user_key,
optwb.price_accept_user_key,
ms.strategy_description,
optwb.eff_size, optwb.sell_uom_code, mstprod.weight,
ROWIDTOCHAR(optwb.rowid) AS row_key,
DENSE_RANK() OVER(ORDER BY current_margin DESC) AS raw_margin_pct,
100*RATIO_TO_REPORT(current_revenue) OVER() AS raw_sales_pct,
100*RATIO_TO_REPORT(current_profit) OVER() AS raw_profit_pct,
100*RATIO_TO_REPORT(current_units) OVER() AS raw_units_pct,
reason_key, sed.e AS elasticity, sed.r_squared
,mz.zone_name
,mb.brand_name
,mfg.mfg_name
,mvpxref.units_per_case
FROM
OPT_WB_TABLE optwb, SYS_NODE_MASTER snm,
reg_zone_super_store_xref zssxref, reg_demand_group rdg,
MST_PRODUCT mstprod, sys_engine_data sed,
mst_strategy ms
,mst_zone mz
,mst_brand mb
,mst_mfg mfg
,mst_vendor_product_xref mvpxref
WHERE optwb.node_key = snm.node_key
AND optwb.node_key = sed.node_key
AND optwb.super_store_key = zssxref.super_store_key
AND optwb.super_store_key = sed.super_store_key
AND optwb.product_key = sed.product_key
AND sed.promotion_key = 1
AND ms.strategy_key = optwb.strategy_key
AND optwb.dg_key = rdg.dg_key
AND optwb.product_key = mvpxref.product_key(+)
AND mstprod.brand_key = mb.brand_key
AND mstprod.mfg_key = mfg.mfg_key
AND zssxref.zone_key = mz.zone_key
AND optwb.event_key = 86266
AND optwb.product_key = mstprod.product_key
ORDER BY rank DESC
)) ORDER BY rank DESC
)
(SELECT * FROM ( SELECT
us.*,
CEIL(rownum/50) AS page_num,
CEIL(COUNT(*) over()/(50)) AS tot_pages
FROM user_selection us
)
WHERE page_num = 1
) ORDER BY rank DESC
)


Thanks
Preeti Khurana
Previous Topic: Execute immediate
Next Topic: Partition By
Goto Forum:
  


Current Time: Wed Sep 28 05:45:44 CDT 2016

Total time taken to generate the page: 0.08965 seconds