materialized view [message #276379] |
Thu, 25 October 2007 01:14 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 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
|
|
|