Home arrow Oracle arrow 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).

  1. Large Database Features In Oracle, conclusion
  2. RAC Architecture
  3. Self-Managing Databases
  4. cube
  5. Analysis Functions
  6. Windowing Functions
  7. Project 9-2 Using Analytic SQL Functions and Models
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 6
December 15, 2005

print this article



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

  1. 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
    select p.prod_category, c.channel_desc, t.calendar_year year,
           sum(s.quantity_sold) quantity_sold,
           round((sum(s.quantity_sold) - 
                     over (partition by p.prod_category, c.channel_desc
                      order by t.calendar_year)) /
                      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;

  2. 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;
  3. 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;

  4. 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

  1. 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
  2. What data population methods can be used on a compressed table that result in the data being compressed?
  3. _____________ partitioned indexes are defined independently of the data partitions, and _____________ partitioned indexes have a one-to-one relationship with the data partitions.
  4. Explain the functions of the Parallel Execution Coordinator in parallel processing.
  5. For which of the following SQL commands is parallel processing not enabled by default?

    A.  select

    B.  insert

    C.  create

    D.  alter
  6. What is meant by “degree of parallelism”?
  7. What new analytic feature can be used to forecast values based on existing measures?
  8. What two methods can be used to run the SQLAccess Advisor utilities for materialized views?
  9. 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
  10. In a grid computing envrionment, the _____________ instance of a node manages the disk groups and maintains a map of data files and disks.
  11. _____________ partitioning allows you to control the placement of records in specified partitions based on sets of partition key values.
  12. What analytic function would you use to assign each row from a query to one of five buckets?
  13. What are the database components involved in grid computing?
  14. What are the two types of windows that can be used in analytic functions?
  15. The _____________ automatically collects workload and performance statistics used for database self-management activities.
  16. 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?


>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: