Oracle 9i Training - Business Intelligence Enhancements Part 2

Submit Feedback/questions HERE or to subscribe just go to www.asktheoracle.net/oracle-tips-signup.html
-----------------------------------------------
This issue is a continuation of last month's Oracle 9i training course on the enhancements in Oracle 9i for business intelligence/decision support applications (i.e. using Oracle as a data warehouse). Smartsoft also offer instructor-led Oracle 9i training courses and SQL courses for developers and dbas. New subscribers can access the previous Oracle 9i tutorials here. The above pages also contains suggestions for further reading and links to other resources.

1. Introduction

Oracle databases have always been a good platform for decision support systems and data warehouses. Oracle 9i improves on the features in previous versions by enhancing partitioning and materialised views, adding external tables, view constraints, multi-table insert statement, merge statement and many other new features including extended support for on-line analytical processing (OLAP) and improvements to data mining features.

This session looks at a few of the enhancements made to Oracle 9i for data warehousing, OLAP and data mining. For more information about the new features see the resources section which reviews books for further reading and contains links to information on OTN and other places.

2. Oracle 9i Data Warehousing Enhancements

Oracle 9i has been designed to be a complete solution for data warehouses and thus for business intelligence and decision support systems. The major enhancements for data warehousing include:

List partitioning
View constraints
Partitioning extended to all data types (including index-organised and nested tables and objects)
Bitmap join indexes
Materialized view enhancements

Let's look at some of these in a little more detail. Refer to the Oracle 9i Data Warehousing Guide for a full description of these new features.

List Partitioning

This is an additional method of partitioning that allows you to specify a set (list) of discrete values for a column to determine into which partition the table rows should be placed.

For example, suppose you're running a sales team and want to divide your team up into groups depending on their name (i.e. one group contains Mike, Phil and Sue, another group contains Jane, Jackie and Steph and a third group contains Matt, Rob and Dave).

The way to do that is to partition the table using the list partitioning method as follows:

CREATE TABLE sales
           (invoice_id      NUMBER,
            invoice_total   NUMBER,
            cust_id         NUMBER,
           sales_person    VARCHAR2(20))
            PARTITION BY LIST (sales_person)
            TABLESPACE current_sales
              (PARTITION finance VALUES ('MIKE','PHIL','SUE')
             TABLESPACE fianance_sales,
             PARTITION manufacturing VALUES ('JANE','JACKIE','STEPH')
             TABLESPACE manufacturing_sales,
             PARTITION retail VALUES ('MATT','ROB','DAVE')
             TABLESPACE retail_sales));

In this example we have created a table with 3 partitions - one for each of the market sectors - and assigned the sales of each sales person to a specific partition by means of the partition value list.

There are a few restrictions applying to list-partitioned tables, as follows:

The values in each partition list must be unique across all partitions of the table (i.e. you can't have the same value applying to more than one partition).
The literal values in the list canot be greater than 4k in size.
Each partition must have at least one literal value.
Any values not in any of the partition value lists will be ignored - there's no concept of max value for list partitions.

View Constraints

For query rewrites to be able to work, Oracle needs to be able to detect the relationships between fact and dimension tables, so what happens when you create views on your base tables and use them in your queries? Prior to Oracle 9i these queries would not have been able to make use of any appropriate materialized views because the referational integrity constraints needed to define the relationships between the views could not be created.

Oracle 9i solves this problem by allowing referential integrity (foreign key), primary key and unique constraints to be created on views. However these constraints always have a status of DISABLE NOVALIDATE which means they are not enforced and cannot be used to validate data. The base-table constraints still have to be defined for this purpose. View constraints do support the use of the rely and norely states to allow the views to be used for query rewrites.

View constraints can be created at the same time as the view or by using the alter view <view_name> add constraint command.

Examples:

CREATE VIEW jan_sales (inv_id, inv_dt, inv_amt, cust_id, sales_person, CONSTRAINT pk_jan_sales PRIMARY KEY (inv_id) RELY DISABLE NOVALIDATE, CONSTRAINT fk_jan_sales_cust FOREIGN KEY (cust_id) REFERENCES cust(cust_id) RELY DISABLE NOVALIDATE) AS SELECT (inv_id, inv_dt, inv_amt, cust_id, sales_person) FROM SALES WHERE inv_dt BETWEEN '01-JAN-2003' AND '31-JAN-2003');

Materialized View Enhancemnts

Oracle have enhanced 9i to enable you to determine when a materialized view will be able to be fast-refreshed and what types of query rewrite can be done on it. This is achieved by using the new procedure explain_mview within the dbms_mview package. This procedure takes either the view name or the view text (sql statement) and a statement id as the input parameters and populates the mv_capabilities_table with the results.

Oracle 9i also provides the facility to explain query rewrite results by analyzing a query and advising on the view's availability for query rewrite operations. This is achieved by use of the new procedure explain_rewrite within the dbms_mview package. This procedure takes the query text (sql statement), the materialized view name and a statement id as the input parameters and populates the rewrite_table with the results.

Refer to the Oracle 9i Data Warehousing Guide and the resources page for further details

Summary

Oracle have made many changes with regards to data warehousing for Oracle 9i. This tutorial has only looked at a few of them.

For more information check out the references section or click here for information about our Oracle 9i training courses. Alternatively contact us directly with your requirements .

---------------------------------------

Learn how to develop your Oracle systems better, faster and smarter with high quality Oracle training from our top consultants . See the Oracle training page for more info or click here for details of our scheduled Oracle courses .

Looking for more Oracle tips and tricks ? If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information.

Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.