What's New in Data Warehousing?

This section describes new features of Oracle9i release 2 (9.2) and provides pointers to additional information. New features information from previous releases is also retained to help those users migrating to the current release.

The following sections describe the new features in Oracle Data Warehousing:

• Oracle9i Release 2 (9.2) New Features in Data Warehousing
• Oracle9i Release 1 (9.0.1) New Features in Data Warehousing

Oracle9i Release 2 (9.2) New Features in Data Warehousing

• Data Segment Compression

  You can compress data segments in heap-organized tables, and a typical example of a heap-organized table you should consider for data segment compression is partitioned tables. Data segment compression is also useful for highly redundant data, such as tables with many foreign keys and materialized views created with the ROLLUP clause. You should avoid compression on tables with many updates or DML.

  See Also: Chapter 8, "Materialized Views"

• Materialized View Enhancements

  You can now nest materialized views when the materialized view contains joins and aggregates. Fast refresh is now possible on a materialized views containing the UNION ALL operator. Various restrictions were removed in addition to expanding the situations where materialized views could be effectively used. In particular, using materialized views in an OLAP environment has been improved.

  See Also: "Overview of Data Warehousing with Materialized Views" and "Materialized Views in OLAP Environments", and Chapter 14, "Maintaining the Data Warehouse"

• Parallel DML on Non-Partitioned Tables

  You can now use parallel DML on non-partitioned tables.

  See Also: Chapter 21, "Using Parallel Execution"

• Partitioning Enhancements

  You can now simplify SQL syntax by using a DEFAULT partition or a subpartition template. You can implement SPLIT operations more easily.

  See Also: "Partitioning Methods", Chapter 5, "Parallelism and Partitioning in Data Warehouses", and Oracle9i Database Administrator's Guide

• Query Rewrite Enhancements

  Text match processing and join equivalence recognition have been improved. Materialized views containing the UNION ALL operator can now use query rewrite.

  See Also: Chapter 22, "Query Rewrite"

• Range-List Partitioning

  You can now subpartition by list range-partitioned tables.

  See Also: "Types of Partitioning"

• Summary Advisor Enhancements

  The Summary Advisor tool and its related DBMS_OLAP package were improved so you can restrict workloads to a specific schema.

  See Also: Chapter 16, "Summary Advisor"

Oracle9i Release 1 (9.0.1) New Features in Data Warehousing

• Analytic Functions

  Oracle's analytic capabilities have been improved through the addition of Inverse percentile, hypothetical distribution, and first/last analytic functions.

  See Also: Chapter 19, "SQL for Analysis in Data Warehouses"

• Bitmap Join Index

  A bitmap join index spans multiple tables and improves the performance of joins of those tables.

  See Also: "Bitmap Indexes"

• ETL Enhancements

  Oracle's extraction, transformation, and loading capabilities have been improved with a MERGE statement, multi-table inserts, and table functions.

  See Also: Chapter 10, "Overview of Extraction, Transformation, and Loading"

• Full Outer Joins

  Oracle added full support for full outer joins so that you can more easily express certain complex queries.

  See Also: Oracle9i Database Performance Tuning Guide and Reference

• Grouping Sets

  You can now selectively specify the set of groups that you want to create using a GROUPING SETS expression within a GROUP BY clause. This allows precise specification across multiple dimensions without computing the whole CUBE.

  See Also: Chapter 18, "SQL for Aggregation in Data Warehouses"

• List Partitioning

  List partitioning offers you precise control over which data belongs in a particular partition.

  See Also: "Partitioning Design Considerations" and Oracle9i Database Concepts, and Oracle9i Database Administrator's Guide

• Materialized View Enhancements

  Various restrictions were removed in addition to expanding the situations where materialized views could be effectively used.

  See Also: "Overview of Data Warehousing with Materialized Views"

• Query Rewrite Enhancements

  The query rewrite feature, which allows many SQL statements to use materialized views, thereby improving performance significantly, was improved significantly. Text match processing and join equivalence recognition have been improved.

  See Also: Chapter 22, "Query Rewrite"

• Summary Advisor Enhancements

  The Summary Advisor tool and its related DBMS_OLAP package were improved so you can specify workloads. In addition, a broader class of schemas is now supported.

  See Also: Chapter 16, "Summary Advisor"

• WITH Clause

  The WITH clause enables you to reuse a query block in a SELECT statement when it occurs more than once within a complex query.

  See Also: "Computation Using the WITH Clause"