S kip Headers

Oracle® Database Data Warehousing Guide
10g Release 1 (10.1)
Part Number B10736-01		 < td align="center" valign="top">Go to Index
< font size="-2">Index
Go to Docu mentation Home
Home		 Go to Book List
Book List		 Go to Master Index
Master Index		 Go to Feedback page Feedback
Go to next page
Next
 < font size="-2">View PDF

< font face="arial, helvetica, sans-serif">Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface< /a>

Audience
Organization
Related Documentation
Conventions
Documentation Accessibility

Wha t's New in Oracle Database?

Oracle Database 10g Release 1 (10.1) Ne w Features in Data Warehousing

Part I Concept s

1 Data War ehousing Concepts

What is a Data Warehouse?
Subject Oriented
Integrated
Nonvolatile
Time Variant
Contrasting OLTP and Data Warehousing Environments
Data Warehouse Architectures
< dl>
Data Warehouse Architecture (Basic)
Data Warehouse Architecture (with a Staging Area)
Data Warehouse Architecture (with a Staging Area and Data Marts)

Part II Logical Design< /font>

2 Logical De sign in Data Warehouses

Logical Versus Physical Design in Data Warehouses
Creating a Logical Design
Data Warehousing Schem as
Star Schemas
Other Schemas
Data Warehousing Objects
Fact Tables
Creating a New Fact Table
Dimension Tables
Hierarchies
Typical Dimension Hierarchy
Unique Identifiers
Relationships
Example of Data Warehousing Objects and Their Relationships

Part III Physical Design< /h3>

3 Physical Design in D ata Warehouses

Moving from Logical to Physical Design
Physical Design
Physical Design Structures
Tablespaces
Tables and Partitioned Tables
Table Compression
Views
< dd>Integrity Constraints
Indexes and Partitioned Index es
Materialized Views
Dimensions

4 Hardware and I/O Considerations in Data Warehouses

Overview of Hardware and I/O Cons iderations in Data Warehouses
Configure I/O for Bandwidth not Capacity
Stripe Far and Wide
Use Redundancy
Test the I/O System Before Building the Database
Plan for Growth
Storage Management

5 Parallelism and Partitioning in Data Warehouses

Overview of Parallel Execution
When to Implement Parallel Execution
Granules of Parallelism
Block Range Granules
Partition Granules
Partitioning Design Considerations
Types of Partitioning
Partitioning Methods
Index Partitioning
Performance Issues for Range, List, Hash, and Composite P artitioning
Partitioning and Table Compression
Table Compression and Bitmap Indexes
Example of Table Compressio n and Partitioning
Partition Pruning
Pruning Using DATE Columns
Avoiding I/O Bottlenecks
Partition-Wise Joins
Full Partition-Wise Join s
Partial Partition-wise Joins
Benefits of Partition-Wise Joins
Performance Considerations for Parallel Partition-Wise Joins
Partitioning and Subpartitioning Columns and Keys
Partition Bounds for Range Partitioning
Comparing Partitioning Keys with Partition Bounds
MAXVALUE
Nulls
DATE Datatypes
Multicolumn Partitioning Keys
Implicit Constraints Imposed by Partition Bounds
Index Partitioning
Local Partitioned Indexes
Global Partitioned Indexes
Summary of Partitioned Index Types
The Importance of Nonprefixed Indexes
Perfor mance Implications of Prefixed and Nonprefixed Indexes
Guidelines for Partitioning Index es
Physical Attributes of Index Partitions

6 Indexes

Using Bitmap Indexes in Data Warehouses
Benefits f or Data Warehousing Applications
Cardinality
Bitmap Indexes and Nulls
Bitmap Indexes on Partitioned Tables
Using Bitmap Join Indexes in Data Warehouses
Four Join Mo dels for Bitmap Join Indexes
Bitmap Join Index Restrictions and Requirements
Using B-Tree Indexes in Data Warehouses
Using Index Compression
Choosing Between Local Indexes and Global Indexes

7 Integrity Constraint s

Why Integrity Constraints are Useful in a Data Warehouse
Overview of Constraint States
Typical Data Warehouse Integri ty Constraints
UNIQUE Constraints in a Data Warehouse
FOREIGN KEY Constraints in a Data Warehouse
RELY Constraints
Integrity Constraints and Parallelism
Integrity C onstraints and Partitioning
View Constraints

8 Basic Materialized Views

Overview of Data Warehousing with Materialized Views
Materialized Views for Data Warehouses
Materialized Views for Distributed Co mputing
Materialized Views for Mobile Computing
The Need for Materialized Views
Components of Summary Management
Data Warehousing Terminology
Materialized View Schema Design< /a>
Schemas and Dimension Tables
Mate rialized View Schema Design Guidelines
Loading Data into Data Warehouses
Overview of Materialized View Management Tasks
Types of Materialized Views
Materialized Views with Aggregates
Requirements for Using Materialized Views with Aggregates
Materialized Views Containing Only Joins
Material ized Join Views FROM Clause Considerations
Nested Materialized Views
Why Use Nested Materialized Views?
Nesting Materialized Views with Joins and Aggregates
Nested Materialized View Usage Guidelines< /a>
Restrictions When Using Nested Materialized Views
Creating Materialized Views
Creating Materialized V iews with Column Alias Lists
Naming Materialized Views
Storage And Table Compression
Build Methods
Enabling Query Rewrite
Query Rewrite Restrictions
Materialized View Restrictions
General Query Rewrite Restr ictions
Refresh Options
General Restrictions on Fast Refresh
Restrictions on Fast Refresh on Materialized Views with Joins Only
Restrictions on Fast Refresh on Materialized Views with Aggregates< /dd>
Restrictions on Fast Refresh on Materialized Views with UNION ALL
Achieving Refresh Goals
Refreshing Nested Materialized Views
ORDER BY Clause
Materialized View Logs
Using the FORCE Option with Materialized View Logs
Using Oracle Enterprise Manager
Using Materialized Views with NLS Parameters
Adding Comments to Materialized Views
Registering Existing Materialized Views
Choosing Indexes for Materia lized Views
Dropping Materialized Views
Ana lyzing Materialized View Capabilities
Using the DBMS_MVIEW.EXPLAIN_MVIEW Proce dure
DBMS_MVIEW.EXPLAIN_MVIEW Declarations
Using MV_CAPABILITIES_TABLE
MV_CAPABILITIES_TABLE.CAPABILITY_NAME De tails
MV_CAPABILITIES_TABLE Column Details

9 Advanced Materialized Views

Partitioning and Materialized Views
Partition Change Tracking
Partition Key
Join De pendent Expression
Partition Marker
Partial Rew rite
Partitioning a Materialized View
Partitioning a Prebuilt Table
Benefits of Partitioning a Materialized View< /dd>
Rolling Materialized Views
Mate rialized Views in OLAP Environments
OLAP Cubes
Partitioning Materialized Views for OLAP
Compressing Materialized Views for OLAP
Materialized Views with Set Operators
Examples of Materialized Views Using UNION ALL
Materialized Views an d Models
Invalidating Materialized Views
Securi ty Issues with Materialized Views
Querying Materialized Views with Virtual Priva te Database
Using Query Rewrite with Virtual Private Database
Restrictions with Materialized Views and Virtual Private Database
Altering Materialized Views

10 Dimensions

What are Dimensions?
< dd>Creating Dimensions
Dropping and Creating Attri butes with Columns
Multiple Hierarchies
Using N ormalized Dimension Tables
Viewing Dimensions
Using Oracle Enterprise Manager
Using the DESCRIBE_DIMENSION Procedure< /a>
Using Dimensions with Constraints
Val idating Dimensions
Altering Dimensions
Deleting Dimensions

Part IV Managing the Data Warehou se Environment

11 Overview of Extraction, Transformation, and Loading

Overview of ETL in Data Warehouses
ETL Tools for Data Warehouses
Daily Operations in Data Warehouses
Evolution of the Data Warehouse

12 Extract ion in Data Warehouses

Overview of Extraction in Data Warehouses
Introduction to Extraction Methods in Data Warehouses
Logical Extraction Methods
Full Extraction
Incremental Extraction
Physical Extraction Methods
Online Extraction
Offline Extraction
Change Data Capture
Timest amps
Partitioning
Triggers
Data Warehousing Extraction Examples
Extraction Using Data Files
Extracting into Flat Files Using SQL*Plus< /a>
Extracting into Flat Files Using OCI or Pro*C Programs
Exporting into Export Files Using the Export Utility
Extracting into Export Files Using External Tables
Extraction Through Distributed Operations

13 Transpo rtation in Data Warehouses

Overview of Transportation in Data Warehouses
Introduction to Transportation Mechanisms in Data Warehouses
Transportation Using Flat Files
Transportation Through Distributed Operations
Transportation Using Transportable Tablespaces
Transportable Tablespaces Example
Other Uses of Transportable Tablespaces

14 Loading and Transformation

Overvie w of Loading and Transformation in Data Warehouses
Transformation Flow
Multistage Data Transformation
Pip elined Data Transformation
Loading Mechanisms
< dd>Loading a Data Warehouse with SQL*Loader
Loading a Data Warehouse with External Tables
Loading a Data Warehouse with OCI and Direct-Pat h APIs
Loading a Data Warehouse with Export/Import
Transformation Mechanisms
Transformation Using SQL
CREATE TABLE ... AS SELECT And INSERT /*+APPEND*/ AS SELECT
Transformation Using UPDATE
Transformation Using MERGE
Transformation Using Multitable INSERT
Transformation Using PL/SQL
Transformation Using Table Functions
What is a Table Function?
Loading and Transformation Scenarios
Key Lookup Scenario
Exception Handling Scenario
Pivoting Scenarios

15 Maintaini ng the Data Warehouse

Using Partitioning to Improve Data Warehouse Refresh
Refresh Scenarios
Scenarios for Us ing Partitioning for Refreshing Data Warehouses
Refresh Scenario 1
Refresh Scenario 2
Optimizing DML Operations During Refresh
Implementing an Efficient MERGE Operation
< dd>Maintaining Referential Integrity
Purging Data
Refreshing Materialized Views
Complete Refresh
Fast Refresh
Partition Ch ange Tracking (PCT) Refresh
ON COMMIT Refresh
Manual Refresh Using the DBMS_MVIEW Package
Refresh Specific Materialized Views with REFRESH
Refresh All Materialized Views with REFRESH_ALL_MVIEWS
Refresh Dependent Materialized Views with REFRESH_DEPENDENT
Using Jo b Queues for Refresh
When Fast Refresh is Possible
Recommended Initialization Parameters for Parallelism
Monitoring a Refresh
Checking the Status of a Materialized View
Sched uling Refresh
Tips for Refreshing Materialized Views with Aggregates
Tips for Refreshing Materialized Views Without Aggregates
Tips for Refreshing Nested Materialized Views
Tips for Fast Refresh with UNION ALL
Tips After Refreshing Materialized Views
U sing Materialized Views with Partitioned Tables
Fast Refresh with Partition Ch ange Tracking
PCT Fast Refresh Scenario 1
PCT Fast Refresh Scenario 2
PCT Fast Refresh Scenario 3
Fast Refresh with CONSIDER FRESH

16 Change Data Capture

Overview of Change Data Capture
Capturing Change Data Without Change Data Capture< /a>
Capturing Change Data with Change Data Capture
Publ ish and Subscribe Model
Publisher
Subscri bers
Change Sources and Modes of Data Capture
Synchronous
Asynchronous
HotLog
AutoLog
Change Sets
Valid Combinations of Change Sources and Change Sets
< a href="cdc.htm#sthref902">Change Tables
Getting Information About the Change Data Capture E nvironment
Preparing to Publish Change Data
Creating a User to Serve As a Publisher
Granting Privileges and Roles to the Pu blisher
Creating a Default Tablespace for the Publisher
Password Files and Setting the REMOTE_LOGIN_PASSWORDFILE Parameter
Determinin g the Mode in Which to Capture Data
Setting Initialization Parameters for Change Data Captur e Publishing
Initialization Parameters for Synchronous Publishing
Initialization Parameters for Asynchronous HotLog Publishing
Initia lization Parameters for Asynchronous AutoLog Publishing
Determining the Current Setting of a n Initialization Parameter
Retaining Initialization Parameter Values When a Database Is Rest arted
Adjusting Initialization Parameter Values When Oracle Streams Values Change
Publishing Change Data
Performi ng Synchronous Publishing
Performing Asynchronous HotLog Publishing
Performing Asynchronous AutoLog Publishing
Subscribing to Change Data
Considerations for Asynchronous Change Data Capture
Asynchronous Change Data Capture and Redo Log Files
Asynchronous Change Data Capture and Supplemental Logging
Datatypes and Table Structures Supported for Asynchronous Change Data Capture
Managing Published Data
Managing Asynchronous Change Sets
Creating Asynchronous Change Set s with Starting and Ending Dates
Enabling and Disabling Asynchronous Change Sets
< dd>Stopping Capture on DDL for Asynchronous Change Sets
Recove ring from Errors Returned on Asynchronous Change Sets
Managing Change Tables< /dd>
Creating Change Tables
Understanding Change Table Control Columns
Understanding TARGET_COLMAP$ and SOURCE_COLMAP$ Values
Controlling Subscriber Access to Change Tables
Purging Change Tables of Unneeded Data
Dropping Change Tables
Considerations for Exporting and Importing Change Data Capture Objects
Impact on Su bscriptions When the Publisher Makes Changes
Implementation and System Configurat ion
Synchronous Change Data Capture Restriction on Direct-Path INSERT

17 SQLAccess Advisor

Overview of the SQLAccess Advisor in the DBMS_ADVISOR Package
Overview of Using the SQLAccess Advisor
SQLAccess Advisor Repository
Using the SQLAccess Advi sor
SQLAccess Advisor Flowchart
SQLAccess Advisor Privileges
Creating Tasks
SQLAccess Advisor Templates
Creating Templates
Workload Objects
Managing Workloads
Linking a Task and a Workload
Defining the Contents of a Workload
SQL Tuning Set
Loading a User-Defined Workload< /a>
Loading a SQL Cache Workload
Using a Hypo thetical Workload
Using a Summary Advisor 9i Workload
SQLAccess Advisor Workload Parameters
SQL Workload Journal
Adding SQL Statements to a Workload
Deleting S QL Statements from a Workload
Changing SQL Statements in a Workload
Maintaining Workloads
Setting Workload Attributes< /a>
Resetting Workloads
Removing a Link Betwe en a Workload and a Task
Removing Workloads
Recommendation Options
Generating Recommendations
< a href="advisor.htm#sthref1223">EXECUTE_TASK Procedure
Viewing the Recommenda tions
Access Advisor Journal
Stopping the Recommendation Process
Canceling Tasks
Marking Recommendations
Modifying Recommendations
Generating SQL Scripts
When Recommendations are No Longer R equired
Performing a Quick Tune
Managing Tasks
Updating Task Attributes
Deleting Tasks
Setting DAYS_TO_EXPIRE
Using SQLAccess Advisor Constants
Examples of Using the SQLAccess Advisor
Recommendations From a User-Defined Workload
Generate Recommendations Using a Task Template
Filter a Workload from the SQL C ache
Evaluate Current Usage of Indexes and Materialized Views
Tuning Materialized Views for Fast Refresh and Query Rewrite
DBMS_ADVISOR.TUNE_MVIEW Procedure
TUNE_MVIEW Syntax and Operations
Accessing TUNE_MVIEW Output Results
USER_TUNE_MVIEW and DBA_TUNE_MVIEW Views
Script Generation DBMS_ADVISOR Funct ion and Procedure
Fast Refreshable with Optimized Sub-Materialized View

Part V Data Warehouse Performance

18 Query Rewrite

Overview of Query Rewrite
Cost-Base d Rewrite
When Does Oracle Rewrite a Query?
Enabling Query Rewrite
Initialization Parameters for Query Rewrite
Controlling Query Rewrite
Accuracy of Query Rewrite
Query Rewrite Hints
Privileges for Enablin g Query Rewrite
Sample Schema and Materialized Views
How Oracle Rewrites Queries
Text Match Rewrite Methods
< dl>
Text Match Capabilities
General Query Rewrite Methods
When are Constraints and Dimensions Needed?
Join Back
Rollup Using a Dimension
Compu te Aggregates
Filtering the Data
Dropping Selection s in the Rewritten Query
Handling of HAVING Clause in Query Rewrite
Handling Expressions in Query Rewrite
Handling IN-Lists in Query Rewrite
Checks Made by Query Rewrite
Join Compatibility Check
Data Sufficiency Check
Groupin g Compatibility Check
Aggregate Computability Check
Other Cases for Query Rewrite
Query Rewrite Using Partially Stale Mater ialized Views
Query Rewrite Using Nested Materialized Views
Query Rewrite When Using GROUP BY Extensions
Hint for Queries with Extended GROUP B Y
Query Rewrite with Inline Views
Query Rewrite wit h Selfjoins
Query Rewrite and View Constraints
Quer y Rewrite and Expression Matching
Date Folding Rewrite
Partition Change Tracking (PCT) Rewrite
PCT Rewrite Based on LIST Pa rtitioned Tables
PCT and PMARKER
PCT Rewrite with M aterialized Views Based on Range-List Partitioned Tables
PCT Rewrite Using Rowid as Pmarker< /a>
Query Rewrite and Bind Variables
Query Re write Using Set Operator Materialized Views
UNION ALL Marker
Did Query Rewrite Occur?
Explain Plan< /dd>
DBMS_MVIEW.EXPLAIN_REWRITE Procedure
DBMS_MVI EW.EXPLAIN_REWRITE Syntax
Using REWRITE_TABLE
Using a Varray
EXPLAIN_REWRITE Benefit Statistics
Suppor t for Query Text Larger than 32KB in EXPLAIN_REWRITE
Design Consideratio ns for Improving Query Rewrite Capabilities
Query Rewrite Considerations: Constrai nts
Query Rewrite Considerations: Dimensions
Query Rewrite Considerations: Outer Joins
Query Rewrite Considerations: Text Match
Query Rewrite Considerations: Aggregates
Query Rewrite Considerati ons: Grouping Conditions
Query Rewrite Considerations: Expression Matching
Query Rewrite Considerations: Date Folding
Query Rewrite Considerati ons: Statistics
Advanced Rewrite Using Equivalences

19 Schema Modeling Techniques
Schemas in Data Warehouses
Third Normal F orm
Optimizing Third Normal Form Queries
Star Schemas
Snowflake Schemas
Optimizing Star Queries
Tuning Star Queries
Using Star Transformation
Star Tra nsformation with a Bitmap Index
Execution Plan for a Star Transformation with a Bitmap Index
Star Transformation with a Bitmap Join Index
Execution Plan for a Star Transformation with a Bitmap Join Index
How Oracle Chooses to Use Star Transformation
Star Transformation Restrictions
< /dl>

20 SQL for Agg regation in Data Warehouses

Overview of SQL for Aggregation in Data Warehous es
Analyzing Across Multiple Dimensions
Optimized Performance
An Aggregate Scenario
Interpreting NULLs in Examples
ROLLUP Extension to GROUP BY
< dd>
When to Use ROLLUP
ROLLUP Syntax
< dd>Partial Rollup
CUBE Extension to GROUP BY
When to Use CUBE
CUBE Syntax
Partial CUBE
Calculating Subtotals Without CUBE< /a>
GROUPING Functions
GROU PING Function
When to Use GROUPING
GROUPING _ID Function
GROUP_ID Function
GR OUPING SETS Expression
GROUPING SETS Syntax
Composite Columns
Concatenated Groupings
Concatenated Groupings and Hierarchical Data Cubes
Considerations when Using Aggregation
Hierarchy Handling in ROLLUP and CUBE< /a>
Column Capacity in ROLLUP and CUBE
HAVING C lause Used with GROUP BY Extensions
ORDER BY Clause Used with GROUP BY Extensions
Using Other Aggregate Functions with ROLLUP and CUBE
Computation Using the WITH Clause
Working with Hierarchical Cubes in SQL
Specifying Hierarchical Cubes in SQL
Querying Hierarchical Cubes in SQL
SQL for Creating Materialized Views to S tore Hierarchical Cubes
Examples of Hierarchical Cube Materialized Views

21 SQL f or Analysis and Reporting

Overview of SQL for Analysis and Reporting
Ranking Functions
RANK and DENSE_R ANK Functions
Ranking Order
R anking on Multiple Expressions
RANK and DENSE_RANK Difference
Per Group Ranking
Per Cube and Rollup Group Ranking
Treatment of NULLs
Bottom N Ranking< /dd>
CUME_DIST Function
PERCENT_RANK Function
NTILE Function
ROW_NUMBER Function
Windowing Aggregate Functions
Treatment of NULLs as Input to Window Functions
Windowing Functions with Logical Offset
Centered Aggregate Function
Wind owing Aggregate Functions in the Presence of Duplicates
Varying Window Size for Each R ow
Windowing Aggregate Functions with Physical Offsets
FIRST_VALUE and LAST_VALUE Functions
Reporting Aggregate Fun ctions
RATIO_TO_REPORT Function
LAG/LEAD Functions
LAG/LEAD Syntax
FIRST/LAST Functions
FIRST/LAST Syntax
FIRST/LAST As Regular Aggregates
FIRST/LAST As Reporting Aggregates
Inverse Percentile Functions
Normal Aggregate Syntax
Inverse Percentile Ex ample Basis
As Reporting Aggregates
Inverse Percentile Restrictions
Hypothetical Rank and Distribution Fu nctions
Hypothetical Rank and Distribution Syntax
Linear Regression Functions
REGR_COUNT Functi on
REGR_AVGY and REGR_AVGX Functions
RE GR_SLOPE and REGR_INTERCEPT Functions
REGR_R2 Function
REGR_SXX, REGR_SYY, and REGR_SXY Functions
Linear Regression Statistic s Examples
Sample Linear Regression Calculation
Frequent Itemsets
Other Statistical Functions
Descriptive Statistics
Hypothesis Testing - Parame tric Tests
Crosstab Statistics
Hypothes is Testing - Non-Parametric Tests
Non-Parametric Correlation
WIDTH_BUCKET Function
WIDTH_BUCKET Syntax
User-Defined Aggregate Functions
CASE Expressions
Creating Histograms With User-Defined Buckets
Data Densification for Reporting
Partition Join Syntax
Sample of Sparse Data
Filling Gaps in Data
Filling Gaps in Two Dimensions
Filling Gaps in an Inventory Table
Computing Data Values to Fill Gaps
Time Series Calculations on Densified Data
Period-to-Period Comparison for One Time Level: Example
P eriod-to-Period Comparison for Multiple Time Levels: Example
Creating a Custom Member in a Dimension: Example

22 SQL for Modeling

Overview of SQL Modeling
How Data is Processed in a SQL Model
Why Use SQL Modeling?
SQL Modeling Capabilities
Basic Topics in SQL Modeling
Base Schema
< a href="sqlmodel.htm#sthref1774">MODEL Clause Syntax
Keywords in SQL Modeling
Assigning Values and Null Handling
Ca lculation Definition
Cell Referencing
Symbolic Dimension References
Positional Dimension References
Single Cell References on the Right Side
Mul ti-Cell References
Rules
Single Cell References
Multi-Cell References on the Right Side
Multi-Cell References on the Left Side
Use of the ANY Wi ldcard
Nested Cell References
Order of Evaluation of Rules
Differences Between Update and Upsert
Treatment of NULLs and Missing Cells
Distinguis hing Missing Cells from NULLs
Use Defaults for Missing Cells and NULLs
Qualifying NULLs for a Dimension
Reference M odels
Advanced Topics in SQL Modeling
FOR Loops
Iterative Models
Rule Dependency in AUTOMATIC ORDER Models
Ordered Rules
Unique Dimensions Versus Unique Single References
Rules and Re strictions when Using SQL for Modeling
Performance Considerations with SQL M odeling
Parallel Execution
Ag gregate Computation
Using EXPLAIN PLAN to Understand Model Queries
Using ORDERED FAST: Example
Using ORDERED: Examp le
Using ACYCLIC FAST: Example
Using AC YCLIC: Example
Using CYCLIC: Example
Examples of SQL Modeling

23 OLAP and Data Mining

OLAP Overview
Benefits of OLAP and RDBMS Integration
Scalabil ity
Availability
Manageability
Backup and Recovery
Security
Oracle Data Mining Overview
Enabling Data Mining Applications
Data Mining in the Database
Data Preparati on
Model Building
Model Evaluation
Model Apply (Scoring)
ODM Programmatic Interfaces
ODM Java API
ODM PL/SQL Packages
ODM Sequence Similarity Search (BLAST)

24 Using Parallel Execution

Introduction to Parallel Execution Tuning
When to Implement Parallel Exec ution
When Not to Implement Parallel Execution
Operations That Can Be Parallelized
How Parallel Execution Works
Degree of Parallelism
The Parallel Ex ecution Server Pool
Variations in the Number of Parallel Execution Servers
Processing Without Enough Parallel Execution Servers
How Parallel Execution Servers Communicate
Parallelizing SQL Statem ents
Dividing Work Among Parallel Execution Servers
Parallelism Between Operations
Producer Operations
Types of Parallelism
Paral lel Query
Parallel Queries on Index-Organized Tables
Nonpartitioned Index-Organized Tables
Partitioned Index-Organized Tables
Parallel Queries on Object Types
Parallel DDL
DDL Statements That Can Be Parallelized
< a href="usingpe.htm#sthref1950">CREATE TABLE ... AS SELECT in Parallel
Recoverability a nd Parallel DDL
Space Management for Parallel DDL
Storage Space When Using Dictionary-Managed Tablespaces
Free Space and Paralle l DDL
Parallel DML
Ad vantages of Parallel DML over Manual Parallelism
When to Use Parallel DML
Enabling Parallel DML
Transaction Restrictions for Par allel DML
Rollback Segments
Recovery for Parallel DML
Space Considerations for Parallel DML
Lock and Enqueue Resources for Parallel DML
Restrictions on Parallel DML< /dd>
Data Integrity Restrictions
Trigger Restricti ons
Distributed Transaction Restrictions
Examples of Distributed Transaction Parallelization
Parallel Execution of Fun ctions
Functions in Parallel Queries
Functions in Parallel DML and DDL Statements
Other Types of Parallelism
Initializing and Tuning Parameters for Parallel Execution
Using Default Parameter Settings
Setting the Degr ee of Parallelism for Parallel Execution
How Oracle Determines the Degree of Parallelis m for Operations
Hints and Degree of Parallelism
Table and Index Definitions
Default Degree of Parallelism
Adaptive Multiuser Algorithm
Minimum Number of Paral lel Execution Servers
Limiting the Number of Available Instances
Balancing the Workload
Parallelization Rules for SQL Statements
Rules for Parallelizing Queries
Rules for UPDATE, MERGE, and DELETE
Rules for INSERT ... SELECT
Rules for DDL Statements
Rules for [CREATE | REBUILD ] INDEX or [MOVE | SPLIT] PARTITION
Rules for CREATE TABLE AS SELECT
Summary of Parallelization Rules
Enabling Paralle lism for Tables and Queries
Degree of Parallelism and Adaptive Multiuser: How They Inte ract
How the Adaptive Multiuser Algorithm Works
Forcing Parallel Execution for a Session
Controlling Perform ance with the Degree of Parallelism
Tuning General Parameters for Parallel Ex ecution
Parameters Establishing Resource Limits for Parallel Operations
PARALLEL_MAX_SERVERS
Increasing the Number of Concurrent Users
Limiting the Number of Resources for a User
PARALLEL_MIN_SERVERS
SHARED_POOL_SIZE
Computing Additional Memory Requirements for Message Buffers
Adjusting Memory After Processing Begins
PARALLEL_MIN_PERCENT
< a href="usingpe.htm#sthref2216">Parameters Affecting Resource Consumption
PGA _AGGREGATE_TARGET
PARALLEL_EXECUTION_MESSAGE_SIZE
Parameters Affecting Resource Consumption for Parallel DML and Parallel DDL
Parameters Related to I/O
DB_CACHE_SIZE
DB_BLOCK_SIZE
DB_FILE_MULTIBLOCK_READ_COUNT
DISK_ASYNCH_IO and TAPE_ASYNCH_IO
Monitoring an d Diagnosing Parallel Execution Performance
Is There Regression?
Is There a Plan Change?
Is There a Parallel Plan?
Is There a Serial Plan?
Is There Parallel Ex ecution?
Is the Workload Evenly Distributed?
Monitoring Parallel Execution Performance with Dynamic Performance Views
V$PX_BUFFER_ADVICE
V$PX_SESSION
V$PX_SESS TAT
V$PX_PROCESS
V$PX_PROCESS_SYSSTAT
V$PQ_SESSTAT
V$FILESTAT
V$PARAMETER
V$PQ_TQSTAT
V$SESSTAT and V$SYSSTAT
Monitoring Session Statistics
< a href="usingpe.htm#sthref2332">Monitoring System Statistics
Monitoring Operating Syste m Statistics
Affinity and Parallel Operations
Affinity and Parallel Queries
Affinity and Parallel DML
Miscellaneous Parallel Execution Tuning Tips
Setting Buffer Cache Size for Parallel Operations
Overriding the Defa ult Degree of Parallelism
Rewriting SQL Statements
Creating and Populating Tables in Parallel
Creating Temporary Tablespaces for Parallel Sort and Hash Join
Size of Temporary Extents
Executing Parallel SQL Statements
Using EXPLAIN PLAN to Show Parallel Operations Plans
Additional Considerations for Parallel DML
< dd>
PDML and Direct-Path Restrictions
Limitat ion on the Degree of Parallelism
Using Local and Global Striping
Increasing INITRANS
Limitation on Available Number of Transacti on Free Lists for Segments
Using Multiple Archivers
Database Writer Process (DBWn) Workload
[NO]LOGGING Clause
Creating Indexes in Parallel
Parallel DML Tips
Parallel DML Tip 1: INSERT
Paral lel DML Tip 2: Direct-Path INSERT
Parallel DML Tip 3: Parallelizing INSERT, MERGE, UPDA TE, and DELETE
Incremental Data Loading in Parallel
Updating the Table in Parallel
Inserting the New Rows in to the Table in Parallel
Merging in Parallel
Using Hints with Query Optimization
FIRST_ROWS(n) Hint
Enabling Dynamic Sampling

Glossary

Index

Go to next page
Next
Oracle
Copyright © 2001, 2003 Oracle Corporation
All Rights Reserved.
Go to Documentation Home
Home		 Go to Book List
Book List 		Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback