HomeOracle Page 7 - Large Database Features In Oracle, conclusion
Project 9-2 Using Analytic SQL Functions and Models - Oracle
When you are working with large databases with Oracle Database 10g, there are certain topics with which you need to be familiar. These topics are covered in this article, which is the second of two parts. It 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).
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.
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?
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?