Contents
- Audience
- Organization
- Related Documentation
- Conventions
- Documentation Accessibility
- Oracle Database 10g Release 1 (10.1) New Features in Data Warehousing
- What is a Data Warehouse?
-
- Subject Oriented
- Integrated
- Nonvolatile
- Time Variant
- Contrasting OLTP and Data Warehousing Environments
- Data Warehouse Architectures
-
- Data Warehouse Architecture (Basic)
- Data Warehouse Architecture (with a Staging Area)
- Data Warehouse Architecture (with a Staging Area and Data Marts)
- Logical Versus Physical Design in Data Warehouses
- Creating a Logical Design
- Data Warehousing Schemas
-
- 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
- Moving from Logical to Physical Design
- Physical Design
-
- Physical Design Structures
- Tablespaces
- Tables and Partitioned Tables
-
- Table Compression
- Views
- Integrity Constraints
- Indexes and Partitioned Indexes
- Materialized Views
- Dimensions
- Overview of Hardware and I/O Considerations 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
- 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 Partitioning
- Partitioning and Table Compression
-
- Table Compression and Bitmap Indexes
- Example of Table Compression and Partitioning
- Partition Pruning
-
- Pruning Using DATE Columns
- Avoiding I/O Bottlenecks
- Partition-Wise Joins
-
- Full Partition-Wise Joins
- 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
- Performance Implications of Prefixed and Nonprefixed Indexes
- Guidelines for Partitioning Indexes
- Physical Attributes of Index Partitions
- Using Bitmap Indexes in Data Warehouses
-
- Benefits for Data Warehousing Applications
- Cardinality
- Bitmap Indexes and Nulls
- Bitmap Indexes on Partitioned Tables
- Using Bitmap Join Indexes in Data Warehouses
-
- Four Join Models 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
- Why Integrity Constraints are Useful in a Data Warehouse
- Overview of Constraint States
- Typical Data Warehouse Integrity Constraints
-
- UNIQUE Constraints in a Data Warehouse
- FOREIGN KEY Constraints in a Data Warehouse
- RELY Constraints
- Integrity Constraints and Parallelism
- Integrity Constraints and Partitioning
- View Constraints
- Overview of Data Warehousing with Materialized Views
-
- Materialized Views for Data Warehouses
- Materialized Views for Distributed Computing
- Materialized Views for Mobile Computing
- The Need for Materialized Views
- Components of Summary Management
- Data Warehousing Terminology
- Materialized View Schema Design
-
- Schemas and Dimension Tables
- Materialized 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
-
- Materialized 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
- Restrictions When Using Nested Materialized Views
- Creating Materialized Views
-
- Creating Materialized Views 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 Restrictions
- 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
- 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 Materialized Views
- Dropping Materialized Views
- Analyzing Materialized View Capabilities
-
- Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure
-
- DBMS_MVIEW.EXPLAIN_MVIEW Declarations
- Using MV_CAPABILITIES_TABLE
- MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details
- MV_CAPABILITIES_TABLE Column Details
- Partitioning and Materialized Views
-
- Partition Change Tracking
-
- Partition Key
- Join Dependent Expression
- Partition Marker
- Partial Rewrite
- Partitioning a Materialized View
- Partitioning a Prebuilt Table
-
- Benefits of Partitioning a Materialized View
- Rolling Materialized Views
- Materialized 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 and Models
- Invalidating Materialized Views
- Security Issues with Materialized Views
-
- Querying Materialized Views with Virtual Private Database
-
- Using Query Rewrite with Virtual Private Database
- Restrictions with Materialized Views and Virtual Private Database
- Altering Materialized Views
- What are Dimensions?
- Creating Dimensions
-
- Dropping and Creating Attributes with Columns
- Multiple Hierarchies
- Using Normalized Dimension Tables
- Viewing Dimensions
-
- Using Oracle Enterprise Manager
- Using the DESCRIBE_DIMENSION Procedure
- Using Dimensions with Constraints
- Validating Dimensions
- Altering Dimensions
- Deleting Dimensions
- Overview of ETL in Data Warehouses
- ETL Tools for Data Warehouses
-
- Daily Operations in Data Warehouses
- Evolution of the Data Warehouse
- 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
-
- Timestamps
- Partitioning
- Triggers
- Data Warehousing Extraction Examples
-
- Extraction Using Data Files
-
- Extracting into Flat Files Using SQL*Plus
- 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
- 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
- Overview of Loading and Transformation in Data Warehouses
-
- Transformation Flow
-
- Multistage Data Transformation
- Pipelined Data Transformation
- Loading Mechanisms
-
- Loading a Data Warehouse with SQL*Loader
- Loading a Data Warehouse with External Tables
- Loading a Data Warehouse with OCI and Direct-Path 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
- Using Partitioning to Improve Data Warehouse Refresh
-
- Refresh Scenarios
- Scenarios for Using Partitioning for Refreshing Data Warehouses
-
- Refresh Scenario 1
- Refresh Scenario 2
- Optimizing DML Operations During Refresh
-
- Implementing an Efficient MERGE Operation
- Maintaining Referential Integrity
- Purging Data
- Refreshing Materialized Views
-
- Complete Refresh
- Fast Refresh
- Partition Change 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 Job Queues for Refresh
- When Fast Refresh is Possible
- Recommended Initialization Parameters for Parallelism
- Monitoring a Refresh
- Checking the Status of a Materialized View
- Scheduling 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
- Using Materialized Views with Partitioned Tables
-
- Fast Refresh with Partition Change Tracking
-
- PCT Fast Refresh Scenario 1
- PCT Fast Refresh Scenario 2
- PCT Fast Refresh Scenario 3
- Fast Refresh with CONSIDER FRESH
- Overview of Change Data Capture
-
- Capturing Change Data Without Change Data Capture
- Capturing Change Data with Change Data Capture
- Publish and Subscribe Model
-
- Publisher
- Subscribers
- Change Sources and Modes of Data Capture
-
- Synchronous
- Asynchronous
-
- HotLog
- AutoLog
- Change Sets
-
- Valid Combinations of Change Sources and Change Sets
- Change Tables
- Getting Information About the Change Data Capture Environment
- Preparing to Publish Change Data
-
- Creating a User to Serve As a Publisher
-
- Granting Privileges and Roles to the Publisher
- Creating a Default Tablespace for the Publisher
- Password Files and Setting the REMOTE_LOGIN_PASSWORDFILE Parameter
- Determining the Mode in Which to Capture Data
- Setting Initialization Parameters for Change Data Capture Publishing
-
- Initialization Parameters for Synchronous Publishing
- Initialization Parameters for Asynchronous HotLog Publishing
- Initialization Parameters for Asynchronous AutoLog Publishing
- Determining the Current Setting of an Initialization Parameter
- Retaining Initialization Parameter Values When a Database Is Restarted
- Adjusting Initialization Parameter Values When Oracle Streams Values Change
- Publishing Change Data
-
- Performing 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 Sets with Starting and Ending Dates
- Enabling and Disabling Asynchronous Change Sets
- Stopping Capture on DDL for Asynchronous Change Sets
- Recovering from Errors Returned on Asynchronous Change Sets
- Managing Change Tables
-
- 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 Subscriptions When the Publisher Makes Changes
- Implementation and System Configuration
-
- Synchronous Change Data Capture Restriction on Direct-Path INSERT
- Overview of the SQLAccess Advisor in the DBMS_ADVISOR Package
-
- Overview of Using the SQLAccess Advisor
-
- SQLAccess Advisor Repository
- Using the SQLAccess Advisor
-
- 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
- Loading a SQL Cache Workload
- Using a Hypothetical Workload
- Using a Summary Advisor 9i Workload
- SQLAccess Advisor Workload Parameters
- SQL Workload Journal
- Adding SQL Statements to a Workload
- Deleting SQL Statements from a Workload
- Changing SQL Statements in a Workload
- Maintaining Workloads
-
- Setting Workload Attributes
- Resetting Workloads
- Removing a Link Between a Workload and a Task
- Removing Workloads
- Recommendation Options
- Generating Recommendations
-
- EXECUTE_TASK Procedure
- Viewing the Recommendations
- Access Advisor Journal
- Stopping the Recommendation Process
-
- Canceling Tasks
- Marking Recommendations
- Modifying Recommendations
- Generating SQL Scripts
- When Recommendations are No Longer Required
- 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 Cache
- 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 Function and Procedure
- Fast Refreshable with Optimized Sub-Materialized View
- Overview of Query Rewrite
-
- Cost-Based 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 Enabling Query Rewrite
- Sample Schema and Materialized Views
- How Oracle Rewrites Queries
-
- Text Match Rewrite Methods
-
- Text Match Capabilities
- General Query Rewrite Methods
-
- When are Constraints and Dimensions Needed?
- Join Back
- Rollup Using a Dimension
- Compute Aggregates
- Filtering the Data
- Dropping Selections 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
- Grouping Compatibility Check
- Aggregate Computability Check
- Other Cases for Query Rewrite
-
- Query Rewrite Using Partially Stale Materialized Views
- Query Rewrite Using Nested Materialized Views
- Query Rewrite When Using GROUP BY Extensions
- Hint for Queries with Extended GROUP BY
- Query Rewrite with Inline Views
- Query Rewrite with Selfjoins
- Query Rewrite and View Constraints
- Query Rewrite and Expression Matching
- Date Folding Rewrite
- Partition Change Tracking (PCT) Rewrite
-
- PCT Rewrite Based on LIST Partitioned Tables
- PCT and PMARKER
- PCT Rewrite with Materialized Views Based on Range-List Partitioned Tables
- PCT Rewrite Using Rowid as Pmarker
- Query Rewrite and Bind Variables
- Query Rewrite Using Set Operator Materialized Views
-
- UNION ALL Marker
- Did Query Rewrite Occur?
-
- Explain Plan
- DBMS_MVIEW.EXPLAIN_REWRITE Procedure
-
- DBMS_MVIEW.EXPLAIN_REWRITE Syntax
- Using REWRITE_TABLE
- Using a Varray
- EXPLAIN_REWRITE Benefit Statistics
- Support for Query Text Larger than 32KB in EXPLAIN_REWRITE
- Design Considerations for Improving Query Rewrite Capabilities
-
- Query Rewrite Considerations: Constraints
- Query Rewrite Considerations: Dimensions
- Query Rewrite Considerations: Outer Joins
- Query Rewrite Considerations: Text Match
- Query Rewrite Considerations: Aggregates
- Query Rewrite Considerations: Grouping Conditions
- Query Rewrite Considerations: Expression Matching
- Query Rewrite Considerations: Date Folding
- Query Rewrite Considerations: Statistics
- Advanced Rewrite Using Equivalences
- Schemas in Data Warehouses
- Third Normal Form
-
- Optimizing Third Normal Form Queries
- Star Schemas
-
- Snowflake Schemas
- Optimizing Star Queries
-
- Tuning Star Queries
- Using Star Transformation
-
- Star Transformation 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
- Overview of SQL for Aggregation in Data Warehouses
-
- Analyzing Across Multiple Dimensions
- Optimized Performance
- An Aggregate Scenario
- Interpreting NULLs in Examples
- ROLLUP Extension to GROUP BY
-
- When to Use ROLLUP
- ROLLUP Syntax
- Partial Rollup
- CUBE Extension to GROUP BY
-
- When to Use CUBE
- CUBE Syntax
- Partial CUBE
- Calculating Subtotals Without CUBE
- GROUPING Functions
-
- GROUPING Function
- When to Use GROUPING
- GROUPING_ID Function
- GROUP_ID Function
- GROUPING 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
- Column Capacity in ROLLUP and CUBE
- HAVING Clause 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 Store Hierarchical Cubes
- Examples of Hierarchical Cube Materialized Views
- Overview of SQL for Analysis and Reporting
- Ranking Functions
-
- RANK and DENSE_RANK Functions
-
- Ranking Order
- Ranking on Multiple Expressions
- RANK and DENSE_RANK Difference
- Per Group Ranking
- Per Cube and Rollup Group Ranking
- Treatment of NULLs
- Bottom N Ranking
- 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
- Windowing Aggregate Functions in the Presence of Duplicates
- Varying Window Size for Each Row
- Windowing Aggregate Functions with Physical Offsets
- FIRST_VALUE and LAST_VALUE Functions
- Reporting Aggregate Functions
-
- 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 Example Basis
- As Reporting Aggregates
- Inverse Percentile Restrictions
- Hypothetical Rank and Distribution Functions
-
- Hypothetical Rank and Distribution Syntax
- Linear Regression Functions
-
- REGR_COUNT Function
- REGR_AVGY and REGR_AVGX Functions
- REGR_SLOPE and REGR_INTERCEPT Functions
- REGR_R2 Function
- REGR_SXX, REGR_SYY, and REGR_SXY Functions
- Linear Regression Statistics Examples
- Sample Linear Regression Calculation
- Frequent Itemsets
- Other Statistical Functions
-
- Descriptive Statistics
- Hypothesis Testing - Parametric Tests
- Crosstab Statistics
- Hypothesis 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
- Period-to-Period Comparison for Multiple Time Levels: Example
- Creating a Custom Member in a Dimension: Example
- 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
- MODEL Clause Syntax
- Keywords in SQL Modeling
-
- Assigning Values and Null Handling
- Calculation Definition
- Cell Referencing
-
- Symbolic Dimension References
- Positional Dimension References
- Single Cell References on the Right Side
- Multi-Cell References
- Rules
-
- Single Cell References
- Multi-Cell References on the Right Side
- Multi-Cell References on the Left Side
- Use of the ANY Wildcard
- Nested Cell References
- Order of Evaluation of Rules
- Differences Between Update and Upsert
- Treatment of NULLs and Missing Cells
-
- Distinguishing Missing Cells from NULLs
- Use Defaults for Missing Cells and NULLs
- Qualifying NULLs for a Dimension
- Reference Models
- 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 Restrictions when Using SQL for Modeling
- Performance Considerations with SQL Modeling
-
- Parallel Execution
- Aggregate Computation
- Using EXPLAIN PLAN to Understand Model Queries
-
- Using ORDERED FAST: Example
- Using ORDERED: Example
- Using ACYCLIC FAST: Example
- Using ACYCLIC: Example
- Using CYCLIC: Example
- Examples of SQL Modeling
- OLAP Overview
-
- Benefits of OLAP and RDBMS Integration
-
- Scalability
- Availability
- Manageability
- Backup and Recovery
- Security
- Oracle Data Mining Overview
-
- Enabling Data Mining Applications
- Data Mining in the Database
-
- Data Preparation
- Model Building
- Model Evaluation
- Model Apply (Scoring)
- ODM Programmatic Interfaces
- ODM Java API
- ODM PL/SQL Packages
- ODM Sequence Similarity Search (BLAST)
- Introduction to Parallel Execution Tuning
-
- When to Implement Parallel Execution
- When Not to Implement Parallel Execution
- Operations That Can Be Parallelized
- How Parallel Execution Works
-
- Degree of Parallelism
- The Parallel Execution Server Pool
-
- Variations in the Number of Parallel Execution Servers
- Processing Without Enough Parallel Execution Servers
- How Parallel Execution Servers Communicate
- Parallelizing SQL Statements
-
- Dividing Work Among Parallel Execution Servers
- Parallelism Between Operations
- Producer Operations
- Types of Parallelism
-
- Parallel 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
- CREATE TABLE ... AS SELECT in Parallel
- Recoverability and Parallel DDL
- Space Management for Parallel DDL
- Storage Space When Using Dictionary-Managed Tablespaces
- Free Space and Parallel DDL
- Parallel DML
-
- Advantages of Parallel DML over Manual Parallelism
- When to Use Parallel DML
- Enabling Parallel DML
- Transaction Restrictions for Parallel DML
- Rollback Segments
- Recovery for Parallel DML
- Space Considerations for Parallel DML
- Lock and Enqueue Resources for Parallel DML
- Restrictions on Parallel DML
- Data Integrity Restrictions
- Trigger Restrictions
- Distributed Transaction Restrictions
- Examples of Distributed Transaction Parallelization
- Parallel Execution of Functions
-
- 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 Degree of Parallelism for Parallel Execution
- How Oracle Determines the Degree of Parallelism for Operations
-
- Hints and Degree of Parallelism
- Table and Index Definitions
- Default Degree of Parallelism
- Adaptive Multiuser Algorithm
- Minimum Number of Parallel 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 Parallelism for Tables and Queries
- Degree of Parallelism and Adaptive Multiuser: How They Interact
-
- How the Adaptive Multiuser Algorithm Works
- Forcing Parallel Execution for a Session
- Controlling Performance with the Degree of Parallelism
- Tuning General Parameters for Parallel Execution
-
- 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
- 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 and 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 Execution?
- Is the Workload Evenly Distributed?
- Monitoring Parallel Execution Performance with Dynamic Performance Views
-
- V$PX_BUFFER_ADVICE
- V$PX_SESSION
- V$PX_SESSTAT
- 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
- Monitoring System Statistics
- Monitoring Operating System 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 Default 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
-
- PDML and Direct-Path Restrictions
- Limitation on the Degree of Parallelism
- Using Local and Global Striping
- Increasing INITRANS
- Limitation on Available Number of Transaction 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
- Parallel DML Tip 2: Direct-Path INSERT
- Parallel DML Tip 3: Parallelizing INSERT, MERGE, UPDATE, and DELETE
- Incremental Data Loading in Parallel
-
- Updating the Table in Parallel
- Inserting the New Rows into the Table in Parallel
- Merging in Parallel
- Using Hints with Query Optimization
- FIRST_ROWS(n) Hint
- Enabling Dynamic Sampling