| materialized view [message #276379] |
Thu, 25 October 2007 01:14  |
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  |
 |
Michel Cadot
Messages: 68704
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
