Data Warehousing Guide - Oracle
Oracle DatabaseData Warehousing Guide21cF31840-09August 2021
Oracle Database Data Warehousing Guide, 21cF31840-09Copyright 2001, 2021, Oracle and/or its affiliates.Primary Author: Padmaja PotineniContributors: Hermann Baer, Mark Bauer, Subhransu Basu, Nigel Bayliss, Donna Carver, Maria Colgan,Benoit Dageville, Luping Ding, Bud Endress, Bruce Golbus, John Haydu, Keith Laker, Paul Lane, Chun-ChiehLin, William Lee, George Lumpkin, David McDermid, Alex Melidis, Valarie Moore, Ananth Raghavan, JackRaitto, Andy Rivenes, Lei Sheng, Wayne Smith, Sankar Subramanian, Margaret Taft, Murali Thiyagarajan,Jean-Francois Verrier, Andreas Walter, Andy Witkowski, Min Xiao, Tsae-Feng Yu, Fred Zemke, MohamedZiauddin, Frederick KushThis software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it onbehalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software,any programs embedded, installed or activated on delivered hardware, and modifications of such programs)and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government endusers are "commercial computer software" or "commercial computer software documentation" pursuant to theapplicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use,reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/oradaptation of i) Oracle programs (including any operating system, integrated software, any programsembedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oraclecomputer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in thelicense contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloudservices are defined by the applicable contract for such services. No other rights are granted to the U.S.Government.This software or hardware is developed for general use in a variety of information management applications.It is not developed or intended for use in any inherently dangerous applications, including applications thatmay create a risk of personal injury. If you use this software or hardware in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure itssafe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of thissoftware or hardware in dangerous applications.Oracle, Java, and MySQL are registered trademarks of Oracle and/or its affiliates. Other names may betrademarks of their respective owners.Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks areused under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc,and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registeredtrademark of The Open Group.This software or hardware and documentation may provide access to or information about content, products,and services from third parties. Oracle Corporation and its affiliates are not responsible for and expresslydisclaim all warranties of any kind with respect to third-party content, products, and services unless otherwiseset forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not beresponsible for any loss, costs, or damages incurred due to your access to or use of third-party content,products, or services, except as set forth in an applicable agreement between you and Oracle.
ContentsPrefaceAudiencexxviDocumentation AccessibilityxxviRelated DocumentsxxviConventionsxxviiPart I1Data Warehouse - FundamentalsIntroduction to Data Warehousing Concepts1.1What Is a Data Warehouse?1.1.12Key Characteristics of a Data Warehouse1-11-31.2Contrasting OLTP and Data Warehousing Environments1-31.3Common Data Warehouse Tasks1-41.4Data Warehouse Architectures1-51.4.1Data Warehouse Architecture: Basic1-51.4.2Data Warehouse Architecture: with a Staging Area1-61.4.3Data Warehouse Architecture: with a Staging Area and Data Marts1-6Data Warehousing Logical Design2.1Logical Versus Physical Design in Data Warehouses2-12.2Creating a Logical Design2-12.2.12.32.4What is a Schema?About Third Normal Form Schemas2-22-22.3.1About Normalization2-32.3.2Design Concepts for 3NF Schemas2-42.3.2.1Identifying Candidate Primary Keys2-52.3.2.2Foreign Key Relationships and Referential Integrity Constraints2-52.3.2.3Denormalization2-5About Star Schemas2.4.1About Facts and Dimensions in Star Schemas2-52-6iii
2.52.632.4.1.1About Fact Tables in Data Warehouses2-72.4.1.2About Dimension Tables in Data Warehouses2-82.4.2Design Concepts in Star Schemas2.4.3About Snowflake Schemas2-10Improved Analytics Using the In-Memory Column Store2-112.5.1About Improving Query Performance Using In-Memory Expressions2-122.5.2About Using In-Memory Virtual Columns to Improve Query Performance2-132.5.3About In-Memory Column Store and Automatic Data Optimization2-13Automatic Big Table Caching to Improve the Performance of In-Memory ParallelQueries2-14Data Warehousing Physical Design3.1Moving from Logical to Physical Design3-13.2About Physical Design3-13.2.142-8Physical Design Structures3-23.2.1.1About Tablespaces in Data Warehouses3-23.2.1.2About Partitioning in Data Warehouses3-33.2.1.3Index Partitioning in Data Warehouses3-43.2.1.4About Partitioning for Manageability3-53.2.1.5About Partitioning for Performance3-53.2.1.6About Partitioning for Availability3-63.2.2About Views in Data Warehouses3-63.2.3About Integrity Constraints in Data Warehouses3-63.2.4About Indexes and Partitioned Indexes in Data Warehouses3-73.2.5About Materialized Views in Data Warehouses3-73.2.6About Dimensions in Data Warehouses3-73.2.6.1About Dimension Hierarchies3-83.2.6.2Typical Dimension Hierarchy3-9Data Warehousing Optimizations and Techniques4.1Using Indexes in Data Warehouses4.1.14-1About Using Bitmap Indexes in Data Warehouses4-14.1.1.1About Bitmap Indexes and Nulls4-24.1.1.2About Bitmap Indexes on Partitioned Tables4-24.1.2Benefits of Indexes for Data Warehousing Applications4-34.1.3About Cardinality and Bitmap Indexes4-34.1.4How to Determine Candidates for Using a Bitmap Index4-54.1.5Using Bitmap Join Indexes in Data Warehouses4-54.1.5.1Four Join Models for Bitmap Join Indexes in Data Warehouses4-64.1.5.2Bitmap Join Index Restrictions and Requirements4-8iv
4.24.34.1.6Using B-Tree Indexes in Data Warehouses4-84.1.7Using Index Compression4-94.1.8Choosing Between Local Indexes and Global Indexes4-9Using Integrity Constraints in a Data Warehouse4.2.1Overview of Constraint States4-104.2.2Typical Data Warehouse Integrity Constraints4-114.2.2.1UNIQUE Constraints in a Data Warehouse4-114.2.2.2FOREIGN KEY Constraints in a Data Warehouse4-124.2.2.3RELY Constraints in a Data Warehouse4-134.2.2.4NOT NULL Constraints in a Data Warehouse4-134.2.2.5Integrity Constraints and Parallelism in a Data Warehouse4-144.2.2.6Integrity Constraints and Partitioning in a Data Warehouse4-144.2.2.7View Constraints in a Data Warehouse4-14About Parallel Execution in Data Warehouses4.3.14.4Why Use Parallel Execution?4-164.3.1.2When Not to Implement Parallel Execution4-164.3.2Automatic Degree of Parallelism and Statement Queuing4-164.3.3About In-Memory Parallel Execution in Data Warehouses4-18About Optimizing Storage Requirements in Data WarehousesUsing Data Compression to Improve Storage in Data WarehousesOptimizing Star Queries and 3NF SchemasOptimizing Star Queries4.5.1.14.5.24-184-194-194-20Tuning Star Queries4-20Using Star Transformation4-204.5.2.1Star Transformation with a Bitmap Index4-214.5.2.2Execution Plan for a Star Transformation with a Bitmap Index4-224.5.2.3Star Transformation with a Bitmap Join Index4-234.5.2.4Execution Plan for a Star Transformation with a Bitmap Join Index4-234.5.2.5How Oracle Chooses to Use Star Transformation4-244.5.2.6Star Transformation Restrictions4-244.5.3Optimizing Third Normal Form Schemas4-254.5.3.13NF Schemas: Partitioning4-254.5.3.23NF Schemas: Parallel Query Execution4-284.5.4Optimizing Star Queries Using VECTOR GROUP BY AggregationAbout Approximate Query Processing4.6.14.74-15When to Implement Parallel Execution4.5.14.64-144.3.1.14.4.14.54-10Running Queries Containing Exact Functions Using SQL Functions that ReturnApproximate ValuesAbout Approximate Top-N Query Processing4-294-304-314-32v
Part II5Optimizing Data WarehousesBasic Materialized Views5.15.2Overview of Data Warehousing with Materialized Views5.1.1About Materialized Views for Data Warehouses5-25.1.2About Materialized Views for Distributed Computing5-25.1.3About Materialized Views for Mobile Computing5-25.1.4The Need for Materialized Views5-35.1.5Components of Summary Management5-45.1.6Data Warehousing Terminology5-55.1.7About Materialized View Schema Design5-65.1.7.1Schemas and Dimension Tables5-65.1.7.2Guidelines for Materialized View Schema Design5-75.1.8About Loading Data into Data Warehouses5-85.1.9Overview of Materialized View Management Tasks5-9Types of Materialized Views5.2.15.2.25-10Requirements for Using Materialized Views with Aggregates5-12About Materialized Views Containing Only Joins5.2.2.15.2.35-10About Materialized Views with Aggregates5.2.1.15.35-15-13Materialized Join Views FROM Clause Considerations5-14About Nested Materialized Views5-155.2.3.1Why Use Nested Materialized Views?5-155.2.3.2About Nesting Materialized Views with Joins and Aggregates5-165.2.3.3Nested Materialized View Usage Guidelines5-165.2.3.4Restrictions When Using Nested Materialized Views5-17Creating Materialized Views5-175.3.1Creating Materialized Views with Column Alias Lists5-185.3.2Creating Materialized Views Based on Hybird Partitioned Tables5-195.3.3About Materialized Views Names5-205.3.4About Storage And Table Compression for Materialized Views5-205.3.5About Build Methods for Materialized Views5-215.3.6About Enabling Query Rewrite for Materialized Views5-215.3.7About Query Rewrite Restrictions5-225.3.7.1About Materialized View Restrictions for Query Rewrite5-225.3.7.2General Query Rewrite Restrictions5-225.3.8About Refresh Options for Materialized Views5-235.3.8.1About Refresh Modes for Materialized Views5-235.3.8.2About Types of Materialized View Refresh5-245.3.8.3About Using Trusted Constraints and Materialized View Refresh5-245.3.8.4General Restrictions on Fast Refresh5-25vi
5.3.8.5Restrictions on Fast Refresh on Materialized Views with Joins Only5-265.3.8.6Restrictions on Fast Refresh on Materialized Views with Aggregates5-265.3.8.7Restrictions on Fast Refresh on Materialized Views with UNION ALL5-285.3.8.8About Achieving Refresh Goals5-295.3.8.9Refreshing Nested Materialized Views5-295.3.95-305.3.10Using Oracle Enterprise Manager to Create Materialized Views5-305.3.11Using Materialized Views with NLS Parameters5-305.3.12Adding Comments to Materialized Views5-305.4Creating Materialized View Logs5-315.4.1Using the FORCE Option With Materialized View Logs5-325.4.2Purging Materialized View Logs5-325.5Creating Materialized Views Based on Approximate Queries5-335.6Creating a Materialized View Containing Bitmap-based COUNT(DISTINCT)Functions5-345.7Registering Existing Materialized Views5-345.8Choosing Indexes for Materialized Views5-365.9Dropping Materialized Views5-375.10Analyzing Materialized View Capabilities5.10.16ORDER BY Clause in Materialized ViewsUsing the DBMS MVIEW.EXPLAIN MVIEW Procedure5-375-375.10.1.1DBMS MVIEW.EXPLAIN MVIEW Declarations5-385.10.1.2Using MV CAPABILITIES TABLE5-385.10.1.3MV CAPABILITIES TABLE.CAPABILITY NAME Details5-415.10.1.4MV CAPABILITIES TABLE Column Details5-42Advanced Materialized Views6.1About Partitioning and Materialized Views6.1.1About Partition Change Tracking6-16.1.1.1About Partition Key and Partition Change Tracking6-36.1.1.2About Join Dependent Expression and Partition Change Tracking6-36.1.1.3About Partition Markers and Partition Change Tracking6-46.1.1.4About Partial Rewrite in Partition Change Tracking6-56.1.2Partitioning a Materialized View6-56.1.3Partitioning a Prebuilt Table6-66.1.3.16.1.46.26-1Benefits of Partitioning a Materialized ViewRolling Materialized ViewsAbout Materialized Views in Analytic Processing Environments6-76-76-76.2.1About Materialized Views and Analytic Views6-86.2.2About Materialized Views and Hierarchical Cubes6-86.2.3Benefits of Partitioning Materialized Views6-96.2.4About Compressing Materialized Views6-9vii
6.2.5About Materialized Views with Set Operators6.2.5.1Examples of Materialized Views Using UNION ALL6-106.3About Materialized Views and Models6-116.4About Security Issues with Materialized Views6-116.4.1Querying Materialized Views with Virtual Private Database (VPD)6-126.4.1.1Using Query Rewrite with Virtual Private Database6-136.4.1.2Restrictions with Materialized Views and Virtual Private Database6-136.5Invalidating Materialized Views6-136.6Altering Materialized Views6-146.7Using Real-time Materialized Views6-156.7.176-9Overview of Real-time Materialized Views6-156.7.1.1Restrictions on Using Real-time Materialized Views6-166.7.1.2About Accessing Real-time Materialized Views6-166.7.2Creating Real-time Materialized Views6-176.7.3Converting an Existing Materialized View into a Real-time Materialized View6-186.7.4Enabling Query Rewrite to Use Real-time Materialized Views6-196.7.5Using Real-time Materialized Views During Query Rewrite6-196.7.6Using Real-time Materialized Views for Direct Query Access6-216.7.7Listing Real-time Materialized Views6-236.7.8Improving Real-time Materialized Views Performance6-23Refreshing Materialized Views7.1About Refreshing Materialized Views7-17.1.1About Complete Refresh for Materialized Views7-37.1.2About Fast Refresh for Materialized Views7-37.1.3About Partition Change Tracking (PCT) Refresh for Materialized Views7-47.1.4About the Out-of-Place Refresh Option7-47.1.4.1Types of Out-of-Place Refresh7-57.1.4.2Restrictions and Considerations with Out-of-Place Refresh7-57.1.5About ON COMMIT Refresh for Materialized Views7-67.1.6About ON STATEMENT Refresh for Materialized Views7-67.1.7About Manual Refresh Using the DBMS MVIEW Package7-77.1.8Refreshing Specific Materialized Views with REFRESH7-87.1.9Refreshing All Materialized Views with REFRESH ALL MVIEWS7-97.1.10Refreshing Dependent Materialized Views with REFRESH DEPENDENT7.1.11About Using Job Queues for Refresh7-107.1.12When Fast Refresh is Possible7-117.1.13Refreshing Materialized Views Based on Approximate Queries7-117.1.14About Refreshing Dependent Materialized Views During Online TableRedefinition7-12Recommended Initialization Parameters for Parallelism7-137.1.157-9viii
7.1.16Monitoring a Refresh7-137.1.17Checking the Status of a Materialized View7-137.1.17.17.1.187.27.3Scheduling Refresh of Materialized ViewsTips for Refreshing Materialized Views7-147-167-167.2.1Tips for Refreshing Materialized Views with Aggregates7-177.2.2Tips for Refreshing Materialized Views Without Aggregates7-197.2.3Tips for Refreshing Nested Materialized Views7-207.2.4Tips for Fast Refresh with UNION ALL7-207.2.5Tips for Fast Refresh with Commit SCN-Based Materialized View Logs7-217.2.6Tips After Refreshing Materialized Views7-21Using Materialized Views with Partitioned Tables7.3.1Materialized View Fast Refresh with Partition Change Tracking7-217-227.3.1.1PCT Fast Refresh for Materialized Views: Scenario 17-227.3.1.2PCT Fast Refresh for Materialized Views: Scenario 27-237.3.1.3PCT Fast Refresh for Materialized Views: Scenario 37-247.4Refreshing Materialized Views Based on Hybrid Partitioned Tables7-257.5Using Partitioning to Improve Data Warehouse Refresh7-267.68Viewing Partition Freshness7.5.1Data Warehouse Refresh Scenarios7-297.5.2Scenarios for Using Partitioning for Refreshing Data Warehouses7-307.5.2.1Partitioning for Refreshing Data Warehouses: Scenario 17-317.5.2.2Partitioning for Refreshing Data Warehouses: Scenario 27-31Optimizing DML Operations During Refresh7-317.6.1Implementing an Efficient MERGE Operation7-327.6.2Maintaining Referential Integrity in Data Warehouses7-347.6.3Purging Data from Data Warehouses7-35Synchronous Refresh8.1About Synchronous Refresh for Materialized Views8-18.1.1What Is Synchronous Refresh?8-18.1.2Why Use Synchronous Refresh?8-28.1.3Registering Tables and Materialized Views for Synchronous Refresh8-38.1.4Specifying Change Data for Refresh8-38.1.5Synchronous Refresh Preparation and Execution8-48.1.6Materialized View Eligibility Rules and Restrictions for Synchronous Refresh8-48.1.6.1Synchronous Refresh Restrictions: Partitioning8-58.1.6.2Synchronous Refresh Restrictions: Refresh Options8-58.1.6.3Synchronous Refresh Restrictions: Constraints8-58.1.6.4Synchronous Refresh Restrictions: Tables8-68.1.6.5Synchronous Refresh Restrictions: Materialized Views8-6ix
8.1.6.68.28.38.4Synchronous Refresh Step 1: Registration Phase8-78.2.2Synchronous Refresh Step 2: Synchronous Refresh Phase8-88.2.3Synchronous Refresh Step 3: The Unregistration Phase8-9Using Synchronous Refresh GroupsExamples of Common Actions with Synchronous Refresh Groups8-118.3.2Examples of Working with Multiple Synchronous Refresh Groups8-12Specifying and Preparing Change Data for Synchronous Refresh8-12Working with Partition Operations While Capturing Change Data forSynchronous Refresh8-13Working with Staging Logs While Capturing Change Data for SynchronousRefresh8-158.4.2.1About the Staging Log Key8-158.4.2.2About Staging Log Rules8-168.4.2.3About Columns Being Updated to NULL8-168.4.2.4Examples of Working with Staging Logs8-178.4.2.5Error Handling in Preparing Staging Logs8-19Troubleshooting Synchronous Refresh Operations8-198.5.1Overview of the Status of Refresh Operations8-208.5.2How PREPARE REFRESH Sets the STATUS Fields8-208.5.3Examples of Preparing for Synchronous Refresh Using PREPARE REFRESH8-218.5.4How EXECUTE REFRESH Sets the Status Fields During SynchronousRefresh8-228.5.5Examples of Executing Synchronous Refresh Using EXECUTE REFRESH8-248.5.6Example of EXECUTE REFRESH with Constraint Violations8-27Performing Synchronous Refresh Eligibility .68-6Using Synchronous Refresh for Materialized Views8.4.18.5Synchronous Refresh Restrictions: Materialized Views with Aggregates8-28Using SYNCREF TABLE to Store the Results of Synchronous RefreshEligibility Analysis8-29Using a VARRAY to Store the Results of Synchronous Refresh EligibilityAnalysis8-29Demo Scripts8-30Overview of Synchronous Refresh Security Considerations8-30Monitoring Materialized View Refresh Operations9.1About Materialized View Refresh Statistics9-19.2Overview of Managing Materialized View Refresh Statistics9-29.3About Data Dictionary Views that Store Materialized View Refresh Statistics9-29.4Collecting Materialized View Refresh Statistics9-49.4.1About Collecting Materialized View Refresh Statistics9-49.4.2Specifying Default Settings for Collecting Materialized View Refresh Statistics9-59.4.3Modifying the Collection Level for Materialized View Refresh Statistics9-5x
9.5Retaining Materialized View Refresh Statistics9.5.1About Retaining Materialized View Refresh Statistics9-79.5.2Specifying the Default Retention Period for Materialized View Refresh Statistics9-79.5.3Modifying the Retention Period for Materialized View Refresh Statistics9-89.6Viewing Materialized View Refresh Statistics Settings9-89.7Purging Materialized View Refresh Statistics9-99.8Viewing Materialized View Refresh Statistics9-109.8.1Viewing Basic Refresh Statistics for a Materialized View9-119.8.2Viewing Detailed Statistics for Each Materialized View Refresh Operation9-129.8.3Viewing Change Data Statistics During Materialized View Refresh Operations9-139.8.4Viewing the SQL Statements Associated with A Materialized View RefreshOperation9-149.910Analyzing Materialized View Refresh Performance Using Refresh Statistics9-15Dimensions10.1What are Dimensions?10.1.110.2Requirements for Dimensions in Data WarehousesCreating Dimensions10-110-310-410.2.1Dropping and Creating Attributes with Columns10-610.2.2Using Multiple Hierarchies While Creating Joins10-710.2.3Using Normalized Dimension Tables to Create Dimensions10-810.3119-6Viewing Dimensions10-910.3.1Viewing Dimensions With Oracle Enterprise Manager10-910.3.2Viewing Dimensions With the DESCRIBE DIMENSION Procedure10-910.4Using Dimensions with Constraints10-910.5Validating Dimensions10-1010.6Altering Dimensions10-1110.7Deleting Dimensions10-12Basic Query Rewrite for Materialized Views11.1Overview of Query Rewrite11-111.1.1About Query Rewrite and the Optimizer11-111.1.2When Does Oracle Rewrite a Query?11-211.2Ensuring that Query Rewrite Takes Effect11-211.2.1Enabling Query Rewrite for Materialized Views11-211.2.2About Initialization Parameters for Query Rewrite11-311.2.3Controlling Query Rewrite11-411.2.4About the Accuracy of Query Rewrite11-411.2.5About Privileges for Enabling Query Rewrite11-511.2.6Sample Schema and Materialized Views11-5xi
11.2.711.312How to Verify if Query Rewrite Occurred11-6Example of Query Rewrite11-7Advanced Query Rewrite for Materialized Views12.1How Oracle Rewrites Queries12-112.1.1About Cost-Based Optimization and Query Rewrite12-212.1.2General Query Rewrite Methods12-312.1.2.112.1.312-4About Checks Made by Query Rewrite12-412.1.3.1Join Compatibility Check for Query Rewrite12-412.1.3.2Data Sufficiency Check for Query Rewrite12-912.1.3.3Grouping Compatibility Check for Query Rewrite12-1012.1.3.4Aggregate Computability Check for Query Rewrite12-1012.1.412.2When are Constraints and Dimensions Needed for Query Rewrite?About Query Rewrite Using Dimensions12-1012.1.4.1Benefits of Using Dimensions in a Query Rewrite Environment12-1012.1.4.2How to Define Dimensions for Query Rewrite12-10Types of Query Rewrite12-1212.2.1Query Rewrite Method 1: Text Match Rewrite12-1212.2.2Query Rewrite Method 2: Join Back12-1412.2.3Query Rewrite Method 3: Aggregate Computability12-1512.2.4Query Rewrite Method 4: Aggregate Rollup12-1612.2.5Query Rewrite Method 5: Rollup Using a Dimension12-1712.2.6Query Rewrite Method 6: When Materialized Views Have Only a Subset ofData12-1712.2.6.1Query Rewrite Definitions When Materialized Views Have Only aSubset of Data12-18Selection Categories When Materialized Views Have Only a Subset ofData12-1812.2.6.3Examples of Query Rewrite Selection12-1912.2.6.4About Handling of the HAVING Clause in Query Rewrite12-2212.2.6.5About Query Rewrite When the Materialized View has an IN-List12-2212.2.6.212.2.712-2312.2.7.1PCT Rewrite Based on Range Partitioned Tables12-2312.2.7.2PCT Rewrite Based on Range-List Partitioned Tables12-2512.2.7.3PCT Rewrite Based on List Partitioned Tables12-2712.2.7.4PCT Rewrite and PMARKER12-2912.2.7.5PCT Rewrite Using Rowid as PMARKER12-3012.2.812.3Partition Change Tracking (PCT) RewriteAbout Query Rewrite Using Multiple Materialized Views12-31Other Query Rewrite Considerations12-3912.3.1About Query Rewrite Using Nested Materialized Views12-3912.3.2About Query Rewrite in the Presence of Inline Views12-40xii
12.3.3About Query Rewrite Using Remote Tables12-4112.3.4About Query Rewrite in the Presence of Duplicate Tables12-4212.3.5About Query Rewrite Using Date Folding12-4312.3.6About Query Rewrite Using View Constraints12-4512.3.6.1Abut View Constraints Restrictions12-4612.3.7About Query Rewrite in the Presence of Hybrid Partitioned Tables12-4712.3.8Query Rewrite Using Set Operator Materialized Views12-4812.3.8.112.3.9UNION ALL Marker and Query RewriteAbout Query Rewrite in the Presence of Grouping Sets12-4912-5112.3.9.1About Query Rewrite When Using GROUP BY Extensions12-5112.3.9.2Hint for Rewriting Queries with Extended GROUP BY12-5412.3.10Query Rewrite in the Presence of Window Functions12-5412.3.11Query Rewrite and Expression Matching12-5512.3.11.1Query Rewrite Using Partially Stale Materialized Views12-5512.3.12Cursor Sharing and Bind Variables During Query Rewrite12-5812.3.13Handling Expressions in Query Rewrite12-5912.4Advanced Query Rewrite Using Equivalences12-5912.5Creating Result Cache Materialized Views with Equivalences12-6212.6Query Rewrite and Materialized Views Based on Approximate Queries12-6412.7Query Rewrite and Materialized Views Based on Bitmap-based COUNT(DISTINCT)Functions12-67Verifying that Query Rewrite has Occurred12-6812.812.8.1Using EXPLAIN PLAN with Query Rewrite12-6912.8.2Using the EXPLAIN REWRITE Procedure with Query Rewrite12-6912.912.8.2.1DBMS MVIEW.EXPLAIN REWRITE Syntax12-7012.8.2.2Using REWRITE TABLE to View EXPLAIN REWRITE Output12-7012.8.2.3Using a Varray to View EXPLAIN REWRITE Output12-7212.8.2.4EXPLAIN REWRITE Benefit Statistics12-7312.8.2.5Support for Query Text Larger than 32KB in EXPLAIN REWRITE12-7312.8.2.6About EXPLAIN REWRITE and Multiple Materialized Views12-7412.8.2.7About EXPLAIN REWRITE Output12-74Design Considerations for Improving Query Rewrite Capabilities12-7512.9.1Query Rewrite Considerations: Constraints12-7612.9.2Query Rewrite Considerations: Dimensions12-7612.9.3Query Rewrite Considerations: Outer Joins12-7612.9.4Query Rewrite Considerations: Text Match12-7612.9.5Query Rewrite Considerations: Aggregates12-7712.9.6Query Rewrite Considerations: Grouping Conditions12-7712.9.7Query Rewrite Considerations: Expression Matching12-7712.9.8Query Rewrite Considerations: Date Folding12-7712.9.9Query Rewrite Considerations: Statistics12-77xiii
12.9.101314Query Rewrite Considerations: Hints12-7812.9.10.1Query Rewrite: REWRITE and NOREWRITE Hints12-7812.9.10.2Query Rewrite: REWRITE OR ERROR Hint12-7812.9.10.3Query Rewrite: Multiple Materialized View Rewrite Hints12-7912.9.10.4Query Rewrite: EXPAND GSET TO UNION Hint12-79Working With Automatic Materialized Views13.1Overview of Automatic Materialized Views13-113.2Workload Information Provided by the Object Activity Tracking System13-213.3Data Dictionary Views That Provide Information About Automatic MaterializedViews and OATS13-213.4The DBMS AUTO MV Package13-413.5The DBMS ACTIVITY Package13-13Attribute Clustering14.1About Attribute Clustering14-114.1.1Methods of Clustering Data14-114.1.2Types of Attribute Clustering14-214.1.2.1Attribute Clustering with Linear Ordering14-214.1.2.2Attribute Clustering with Interleaved Ordering14-314.1.3Example: Attribute Clustered Table14-314.1.4Guidelines for Using Attribute Clustering14-414.1.5Advantages of Attribute-Clustered Tables14-514.1.6About Defining Attribute Clustering for Tables14-514.1.7About Specifying When Attribute Clustering Must be Performed14-614.2Attribute Clustering Operations14-714.2.1Privileges for Attribute-Clustered Tables14-714.2.2Creating Attribute-Clustered Tables with Linear Ordering14-714.2.2.114.2.314-9Examples of Attribute Clustering with Interleaved Ordering14-9Maintaining Attribute Clustering14-1014.2.4.1Adding Attribute Clustering to an Existing Table14-1114.2.4.2Modifying Attribute Clustering Definitions14-1114.2.4.3Dropping Attribute Clustering for an Existing Table14-1214.2.4.4Using Hints to Control Attribute Clustering for DML Operations14-1214.2.4.5Overriding Table-level Settings for Attribute Clustering During DDLOperations14-12Clustering Table Data During Online Table Redefinition14-1214.2.4.614.314-7Creating Attribute-Clustered Tables with Interleaved Ordering14.2.3.114.2.4Examples of Attribute Clustering with Linear OrderingViewing Attribute Clustering Information14-14xiv
14.3.1Determining if Attribute Clustering is Defined for Tables14-1414.3.2Viewing Attribute-Clustering Information for Tables14-1414.3.3Viewing Information About the Columns on Which Attribute Clustering isPerformed14-15Viewing Information About Dimensions and Joins on Which AttributeClustering is Performed14-1514.3.415Using Zone Maps15.1About Zone Maps15-115.1.1Difference Between Zone Maps and Indexes15-215.1.2Zone Maps and Attribute Clustering15-215.1.3Types of Zone Maps15-215.1.4Benefits of Zone Maps15-315.1.5Scenarios Which Benefit from Zone Maps15-315.1.6About Maintaining Zone Maps15-315.215.1.6.1Operations that Require Zone Map Maintenance15-415.1.6.2Scenarios in Which Zone Maps are Automatically Refreshed15-4Zone Map Operations15-515.2.1Privileges Required for Zone Maps15-515.2.2Creating Zone Maps15-615.2.2.1Creating Zone Maps with Attribute Clustering15-615.2.2.2Creating Zone Maps Independent of Attribute Clustering15-815.2.3About Automatic Zone Maps15.2.4About the DBMS AUTO ZONEMAP Package15-915-1015.2.4.1CONFIGURE Procedure15-1015.2.4.2ACTIVITY REPORT Function15-1115.2.4.3Viewing Information About Automatic Zone Maps15-1415.2.5Modifying Zone Maps15-1415.2.6Dropping Zone Maps15-1515.2.7Compiling Zone Maps15-1515.2.8Controlling the Use of Zone Maps15-1615.2.8.1Controlling Zone Map Usage for Entire SQL Workloads15-1615.2.8.2Controlling Zone Map Usage for Specific SQL Statements15-1615.2.9Maintaining Zone Maps15.2.9.115.3Zone Map Maintenance ConsiderationsRefresh and Staleness of Zone Maps15-1715-1815-1915.3.1About Staleness of Zone Maps15-1915.3.2About Refreshing Zone Maps15-2115.3.3Refreshing Zone Maps15-2215.3.3.1Refreshing Zone Maps Using the ALTER MATERIALIZED ZONEMAPCommand15-22xv
15.3.3.215.4How Or
1.3 Common Data Warehouse Tasks 1-4 1.4 Data Warehouse Architectures 1-5 1.4.1 Data Warehouse Architecture: Basic 1-5 1.4.2 Data Warehouse Architecture: with a Staging Area 1-6 1.4.3 Data Warehouse Architecture: with a Staging Area and Data Marts 1-6 2 Data Warehousing Logical Design 2.1 Logical Versus Physical Design in Data Warehouses 2-1
Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,
Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,
Oracle Advanced Analytics (Oracle Data Mining and Oracle R Enterprise) Data Warehousing Oracle OLAP, Oracle Spatial, OBIEE Expert presenters at major Oracle conferences www.vlamis.com (blog, papers, newsletters, services) Co-author of book “Oracle Essbase & Oracle OLAP” Beta tester for OBIEE 11g, Oracle 12c (in-memory)
Data warehousing fundamentals for IT professionals / Paulraj Ponniah.—2nd ed. p. cm. Previous ed. published under title: Data warehousing fundamentals. Includes bibliographical references and index. ISBN 978-0-470-46207-2 (cloth) 1. Data warehousing. I. Ponniah, Paulraj. Data warehousing
Data Warehousing on AWS AWS Whitepaper Introduction Data Warehousing on AWS Publication date: January 15, 2021 (Document histor y and contributors (p. 23)) Enterprises across the globe want to migrate data warehousing to the cloud to improve performance and lower costs. This whitepaper discusses a modern approach to analytics and data warehousing
Introduction 1 Big Data: The evolution of data warehousing 2 Oracle Database 12c and Oracle Exadata: A Data Warehouse as a Foundation for Big Data 3 . However, data warehousing is undergoing a major transition. The benefits of data warehouses are currently being realized in most organizations, partially if not wholly. .
7 Messaging Server Oracle Oracle Communications suite Oracle 8 Mail Server Oracle Oracle Communications suite Oracle 9 IDAM Oracle Oracle Access Management Suite Plus / Oracle Identity Manager Connectors Pack / Oracle Identity Governance Suite Oracle 10 Business Intelligence
Advanced Replication Option, Database Server, Enabling the Information Age, Oracle Call Interface, Oracle EDI Gateway, Oracle Enterprise Manager, Oracle Expert, Oracle Expert Option, Oracle Forms, Oracle Parallel Server [or, Oracle7 Parallel Server], Oracle Procedural Gateway, Oracle Replication Services, Oracle Reports, Oracle
