Large Database Features In Oracle, conclusion - Project 9-2 Using Analytic SQL Functions and Models (Page 7 of 7 ) Once all the database structures have been put in place and data has been loaded, the users will want to analyze it. Knowing what functions are available is important, and so is their use as well, at least to some extent. So, in this project we will walk through a more complex analytical example that includes using the lag function and creating a SQL model. Step by Step - Create a view of the SALES table using the following listing. (The SALES table should have been created during your Oracle installation process.) This view will calculate the percentage change (called percent_chng) of quantity_sold from one year to the next using the lag function, summarized by prod_category, channel_desc, and calendar_year.
create or replace view sales_trends
as
select p.prod_category, c.channel_desc, t.calendar_year year,
sum(s.quantity_sold) quantity_sold,
round((sum(s.quantity_sold) -
lag(sum(s.quantity_sold),1)
over (partition by p.prod_category, c.channel_desc
order by t.calendar_year)) /
lag(sum(s.quantity_sold),1)
over (partition by p.prod_category, c.channel_desc
order by t.calendar_year) *
100 ,2) as percent_chng
from sales s, products p, channels c, times t
where s.prod_id = p.prod_id
and s.channel_id = c.channel_id
and s.time_id = t.time_id
group by p.prod_category, c.channel_desc, t.calendar_year;
- Select from the sales_trends view using the following listing. Notice that quantity_sold and percent_chng reset after each channel_desc. This is a result of the lag function’s partition by clauses in the view definition.
select prod_category, channel_desc, year, quantity_sold, percent_chng
from sales_trends
where prod_category = 'Electronics' order by prod_category, channel_desc, year;
- Select from the sales_trends view using the following listing that contains a model clause. In this query, we are projecting quantity_sold and percent_chng according to the following rules:
a. Filter the prod_category to only select ‘Electronics’.
b. Project for years 2002 to 2006 inclusive.
c. The projected quantity_sold is calculated as the previous year’s value plus the average percent_chng over the previous three years.
d. The projected percent_chng is the average percent_chng over the previous three years.
select prod_category, channel_desc, year, quantity_sold, percent_chng
from sales_trends
where prod_category = 'Electronics' model
partition by (prod_category, channel_desc)
dimension by (year)
measures (quantity_sold, percent_chng)
rules (
percent_chng[for year from 2002 to 2006 increment 1] =
round(avg(percent_chng)[year between currentv()-3 and
currentv()-1], 2),
quantity_sold[for year from 2002 to 2006 increment 1] =
round(quantity_sold[currentv()-1] *
(1 + (round(avg(percent_chng)[year between currentv()-3 and
currentv()-1] ,2) / 100))))
order by prod_category, channel_desc, year;
- Notice the projected values for 2002 to 2006 for each channel_desc.
Project Summary The steps in this project build on the discussions we’ve had on Oracle Database 10g’s analytic capabilities. We used the lag function to calculate percentage change and used the model clause of the select statement to project sales five years into the future based on past trends. By going to the next level of examples, we can start to appreciate the significance of these functions and how they can be used. Oracle Database 10g’s analytic functions provide powerful and efficient analysis capabilities that would otherwise require complex SQL and/or third-party tools. All of these functions are part of the core database—ready and waiting to be exploited by your users. So, now we have come to the end of our discussions on large database features. A great deal of material has been presented in this chapter and we have really only seen the tip of the iceberg! However, you can feel confident that with this background information, you are primed to tackle almost any large database environment out there. Chapter 9 Mastery Check - Which of the following is not a valid combination for composite partitioning?
A. Range partitioning with hash partitioning
B. List partitioning with hash partitioning
C. Range partitioning with list partitioning
- What data population methods can be used on a compressed table that result in the data being compressed?
- _____________ partitioned indexes are defined independently of the data partitions, and _____________ partitioned indexes have a one-to-one relationship with the data partitions.
- Explain the functions of the Parallel Execution Coordinator in parallel processing.
- For which of the following SQL commands is parallel processing not enabled by default?
A. select
B. insert
C. create
D. alter
- What is meant by “degree of parallelism”?
- What new analytic feature can be used to forecast values based on existing measures?
- What two methods can be used to run the SQLAccess Advisor utilities for materialized views?
- Which of the following are components of an RAC system? (Select all that apply.)
A. Parallel Execution Server
B. Cluster Manager
C. Shared Storage
D. ASM Instance
E. Global Cache Service
- In a grid computing envrionment, the _____________ instance of a node manages the disk groups and maintains a map of data files and disks.
- _____________ partitioning allows you to control the placement of records in specified partitions based on sets of partition key values.
- What analytic function would you use to assign each row from a query to one of five buckets?
- What are the database components involved in grid computing?
- What are the two types of windows that can be used in analytic functions?
- The _____________ automatically collects workload and performance statistics used for database self-management activities.
- When creating partitioned tables, what option should you use to ensure that rows are redistributed to their correct partition if their partition key is updated?
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|  This article is excerpted from chapter nine of the book Oracle Database 10g A Beginner's Guide, written by Ian Abramson et al (Mcgraw-Hill/Osborne, 2004; ISBN: 0072230789). Check it out at your favorite bookstore. Buy this book now.
|
|
|
/ 5 
