Home » RDBMS Server » Performance Tuning » materialized view
materialized view [message #276379] Thu, 25 October 2007 01:14 Go to next message
send.shakthi
Messages: 2
Registered: October 2007
Location: Australia
Junior Member
i need to write one materialized view so that it improves r either make all the query use the materialized view i create?which sql query should be the best one to be used in the materialized view?.but all these queries must be altered to one..


Query 1 – sales and profitability by quarter by product.

select t.quarter, p.product_id, p.product_name,
sum(f.purchase_price + f.shipping_charge) as Gross_Sales,
sum((f.purchase_price + f.shipping_charge)-(p.cost_price + p.shipping_charge)) as Profit, count(purchase_price) as total_sales, count(*) as purchase_count
from time t, product p, purchases f
where t.time_key = f.time_key and f.product_id = p.product_id
group by t.quarter, p.product_id, p.product_name;



Query 2 – sales analysis by month by supplier by product.

select p.supplier, t.month, sum(p.cost_price + p.shipping_charge) as Cost_Price, count(purchase_price) as total_sales, count(*) as Unit_Sales
from time t, product p, purchases f
where t.time_key = f.time_key and f.product_id = p.product_id
group by p.supplier, t.month
order by p.supplier, t.month ;

Query 3 – sales analysis by quarter by customer city by product.

select c.city, t.quarter, p.product_id, p.product_name,
sum(f.purchase_price + f.shipping_charge) as Gross_Sales,
sum((f.purchase_price + f.shipping_charge)-(p.cost_price + p.shipping_charge)) as Profit, count(purchase_price) as total_sales, count(*) as purchase_count
from time t, product p, purchases f, customer c
where t.time_key = f.time_key and f.product_id = p.product_id and f.customer_id = c.customer_id
group by c.city, t.quarter, p.product_id, p.product_name
order by c.city, t.quarter, p.product_id, p.product_name;

Re: materialized view [message #276411 is a reply to message #276379] Thu, 25 October 2007 02:03 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: How to creating index? for best performance
Next Topic: Slow SQL with dedicated server?
Goto Forum:
  


Current Time: Mon Dec 05 08:48:52 CST 2016

Total time taken to generate the page: 0.08494 seconds