SEARCH
Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.
BOOK EXPERT ORACLE SQL
SUMMARY
-
Items Found: 706
- Part 1: Basic Concepts
- Chapter 1: SQL Features
- SQL and Declarative Programming Languages
- Statements and SQL_IDs
- Cross-Referencing Statement and SQL_ID
- Array Interface
- Subquery Factoring
- The Concept of Subquery Factoring
- Joins
- Inner Joins and Traditional Join Syntax
- Outer Joins and ANSI Join Syntax
- Chapter 2: The Cost-Based Optimizer
- The Optimal Execution Plan
- The Definition of Cost
- The CBO’s Cost-Estimating Algorithm
- Calculating Cost
- The Quality of the CBO’s Plan Selection
- The Optimization Process
- Parallelism
- Query Transformation
- Final State Query Optimization
- Chapter 3: Basic Execution Plan Concepts
- Displaying Execution Plans
- Displaying the Results of EXPLAIN PLAN
- EXPLAIN PLAN May Be Misleading
- Displaying Output from the Cursor Cache
- Displaying Execution Plans from the AWR
- Understanding Operations
- What an Operation Does
- How Operations Interact
- How Long Do Operations Take?
- Chapter 4: The Runtime Engine
- Collecting Operation Level Runtime Data
- The GATHER_PLAN_STATISTICS Hint
- Setting STATISTICS_LEVEL=ALL
- Enabling SQL Tracing
- Displaying Operational Level Data
- Displaying Runtime Engine Statistics with DBMS_XPLAN.DISPLAY_CURSOR
- Displaying Runtime Engine Statistics with V$SQL_PLAN_STATISTICS_ALL
- Displaying Session Level Statistics with Snapper
- The SQL Performance Monitor
- Workareas
- Operations Needing a Workarea
- Allocating Memory to a Workarea
- Optimal,One-Pass,and Multipass Operations
- Shortcuts
- Scalar Subquery Caching
- Join Shortcuts
- Result and OCI Caches
- Function Result Cache
- Chapter 5: Introduction to Tuning
- Understanding the Problem
- Understanding the Business Problem
- Understanding the Technical Problem
- Understanding the SQL Statement
- Understanding the Data
- Understanding the Problem Wrap Up
- Analysis
- Running the Statement to Completion
- Analyzing Elapsed Time
- When the Elapsed Times Doesn’t Add Up
- When the Time Does Add Up
- Fixing the Problem
- Check the Statistics
- Changing the Code
- Adding Hints
- Making Physical Changes to the Database
- Making Changes to the Environment
- Running the SQL Tuning Advisor
- Rethink the Requirement
- Chapter 6: Object Statistics and Deployment
- The Principle of Performance Management
- The Royal Mail Example
- The Airport Example
- Service Level Agreements in IT
- Non-database Deployment Strategies
- The Strategic Direction for the CBO
- The History of Strategic Features
- Implications of the CBO Strategy
- Why We Need to Gather Statistics
- How Often Do We Need to Change Execution Plans?
- Wolfgang Breitling’s Tuning by Cardinality Feedback
- The TCF Corollary
- Concurrent Execution Plans
- Skewed Data and Histograms
- Workload Variations
- Concurrent Execution Plans Wrap Up
- Oracle’s Plan Stability Features
- Stored Outlines
- SQL Profiles
- SQL Plan Baselines
- Introducing TSTATS
- Acknowledgements
- Adjusting Column Statistics
- TSTATS in a Nutshell
- An Alternative to TSTATS
- Deployment Options for Tuned SQL
- When Just One SQL Statement Needs to Change
- When Multiple SQL Statements Need to Change
- Part 2: Advanced Concepts
- Chapter 7: Advanced SQL Concepts
- Query Blocks and Subqueries
- Terminology
- How Query Blocks are Processed
- Functions
- Aggregate Functions
- Analytic Functions
- Combining Aggregate and Analytic Functions
- Single-row Functions
- The MODEL Clause
- Spreadsheet Concepts
- A Moving Median with the MODEL Clause
- Why Not Use PL/SQL?
- Chapter 8: Advanced Execution Plan Concepts
- Displaying Additional Execution Plan Sections
- DBMS_XPLAN Formatting Options
- Running EXPLAIN PLAN for Analysis
- Query Blocks and Object Alias
- Outline Data
- Peeked Binds
- Predicate Information
- Column Projection
- Remote SQL
- Adaptive Plans
- Result Cache Information
- Notes
- Understanding Parallel Execution Plans
- Operations That Can Be Run in Parallel
- Controlling Parallel Execution
- Granules of Parallelism
- Data Flow Operators
- Parallel Query Server Sets and DFO Trees
- Table Queues and DFO Ordering
- Multiple DFO Trees
- Parallel Query Distribution Mechanisms
- Why Forcing Parallel Query Doesn’t Force Parallel Query
- Further Reading
- Understanding Global Hints
- Hinting Data Dictionary Views
- Applying Hints to Transformed Queries
- The NO_MERGE Hint
- Chapter 9: Object Statistics
- The Purpose of Object Statistics
- Creating Object Statistics
- Gathering Object Statistics
- Exporting and Importing Statistics
- Transferring Statistics
- Setting Object Statistics
- Creating or Rebuilding Indexes and Tables
- Creating Object Statistics Wrap Up
- Examining Object Statistics
- Examining Object Statistics in the Data Dictionary
- Examining Exported Object Statistics
- Statistic Descriptions
- Table Statistics
- Index Statistics
- Column Statistics
- Statistics Descriptions Wrap-up
- Statistics and Partitions
- Gathering Statistics on Partitioned Tables
- How the CBO Uses Partition-level Statistics
- Why We Need Partition-level Statistics
- Statistics and Partitions Wrap-up
- Restoring Statistics
- Locking Statistics
- Pending Statistics
- A Footnote on Other Inputs to the CBO
- Initialization Parameters
- System Statistics
- Other Data Dictionary Information
- Part 3: The Cost-Based Optimizer
- Chapter 10: Access Methods
- Access by ROWID
- ROWID Concepts
- Access by ROWID
- B-tree Index Access
- INDEX FULL SCAN
- INDEX RANGE SCAN
- INDEX SKIP SCAN
- INDEX UNIQUE SCAN
- INDEX FAST FULL SCAN
- INDEX SAMPLE FAST FULL SCAN
- INDEX JOIN
- AND_EQUAL
- Bitmap Index Access
- Full Table Scans
- TABLE and XMLTABLE
- Cluster Access
- Chapter 11: Joins
- Join Methods
- Nested loops
- Hash joins
- Merge joins
- Cartesian joins
- Join Orders
- Join orders without hash join input swapping
- Join orders with hash join input swapping
- Semi-joins
- Standard semi-joins
- Null-accepting semi-joins
- Anti-joins
- Standard anti-joins
- Null-aware anti-joins
- Distribution Mechanisms for Parallel Joins
- The PQ_DISTRIBUTE hint and parallel joins
- Full partition-wise joins
- Partial partition-wise joins
- Broadcast distribution
- Row source replication
- Hash distribution
- Adaptive parallel joins
- Data buffering
- Bloom filtering
- Chapter 12: Final State Optimization
- Join Order
- Join Method
- Access Method
- IN List Iteration
- Chapter 13: Optimizer Transformations
- No-brainer Transformations
- Count Transformation
- Predicate Move-around
- Set and Join Transformations
- Join Elimination
- Outer Join to Inner Join
- Full Outer Join to Outer Join
- Semi-Join to Inner Join
- Subquery Unnesting
- Partial Joins
- Join Factorization
- Set to Join
- Aggregation Transformations
- Distinct Aggregation
- Distinct Placement
- Group by Placement
- Group by Pushdown
- Subquery Transformations
- Simple View Merging
- Complex View Merging
- Factored Subquery Materialization
- Subquery Pushdown
- Join Predicate Pushdown
- Subquery Decorrelation
- Subquery Coalescing
- Miscellaneous Transformations
- Or Expansion
- Materialized View Rewrite
- Grouping Sets to Union Expansion
- Order by Elimination
- Table Expansion
- Star Transformation
- The Distributed Join Filter Problem
- Solving the Distributed Join Filter Problem
- In the Future
- Part 4: Optimization
- Chapter 14: Why Do Things Go Wrong?
- Cardinality Errors
- Correlation of Columns
- Statistics Feedback and DBMS_STATS.SEED_COL_USAGE Features
- Functions
- Stale Statistics
- Daft Data Types
- Caching Effects
- Transitive Closure
- Unsupported Transformations
- Missing Information
- Bad Physical Design
- Contention
- Chapter 15: Physical Database Design
- Adding and Removing Indexes
- Removing Indexes
- Identifying Required Indexes
- Managing Contention
- Sequence Contention
- The Hot-block Problem
- Partitioning
- Full Table Scans on Partitions or Subpartitions
- Partition-wise Joins
- Parallelization and Partitioning
- Denormalization
- Materialized Views
- Manual Aggregation and Join Tables
- Bitmap Join Indexes
- Compression
- Index Compression
- Table Compression
- LOBs
- Chapter 16: Rewriting Queries
- Use of Expressions in Predicates
- Equality Versus Inequality Predicates
- Implicit Data-Type Conversions
- Bind Variables
- UNION,UNION ALL,and OR
- Issues with General Purpose Views
- How to Use Temporary Tables
- Avoiding Multiple Similar Subqueries
- Chapter 17: Optimizing Sorts
- The Mechanics of Sorting
- Memory Limits for Sorts
- Disk-based Sorts
- Avoiding Sorts
- Non-sorting Aggregate Functions
- Index Range Scans and Index Full Scans
- Avoiding Duplicate Sorts
- Sorting Fewer Columns
- Taking Advantage of ROWIDs
- Solving the Pagination Problem
- Sorting Fewer Rows
- Additional Predicates with Analytic Functions
- Views with Lateral Joins
- Avoiding Data Densification
- Parallel Sorts
- Chapter 18: Using Hints
- Are Hints Supportable?
- The PUSH_SUBQ story
- The DML error logging story
- Documented versus undocumented hints
- The MODEL clause corollary
- Supportability conclusion
- Types of Hints
- Edition -based redefinition hints
- Hints that cause errors
- Runtime engine hints
- Optimizer hints that are hints
- Production-hinting case studies
- The bushy join
- Materialization of factored subqueries
- Suppressing order by elimination and subquery unnesting
- The v$database_block_corruption view
- Chapter 19: Advanced Tuning Techniques
- Leveraging an INDEX FAST FULL SCAN
- Simulating a Star Transformation
- Simulating an INDEX JOIN
- Joining Multi-Column Indexes
- Using ROWID Ranges for Application-Coded Parallel Execution
- Converting an Inner Join to an Outer Join
- Part 5: Managing Statistics with TSTATS
- Chapter 20: Managing Statistics with TSTATS
- Managing Column Statistics
- Time-based columns
- Columns with NUM_DISTINCT=1
- Skewed column values and range predicates
- Correlated columns and expressions
- Use of sample data for complex statistical issues
- Managing column statistics wrap up
- Statistics and Partitions
- The DBMS_STATS.COPY_TABLE_STATS myth
- Cardinality estimates with global statistics
- Costing full table scans of table partitions
- Temporary Tables
- The pros and cons of dynamic sampling
- Fabricating statistics for temporary tables
- How to Deploy TSTATS
- Part 1: Basic Concepts
- Chapter 1: SQL Features
- SQL and Declarative Programming Languages
- Statements and SQL_IDs
- Cross-Referencing Statement and SQL_ID
- Array Interface
- Subquery Factoring
- The Concept of Subquery Factoring
- Joins
- Inner Joins and Traditional Join Syntax
- Outer Joins and ANSI Join Syntax
- Chapter 2: The Cost-Based Optimizer
- The Optimal Execution Plan
- The Definition of Cost
- The CBO’s Cost-Estimating Algorithm
- Calculating Cost
- The Quality of the CBO’s Plan Selection
- The Optimization Process
- Parallelism
- Query Transformation
- Final State Query Optimization
- Chapter 3: Basic Execution Plan Concepts
- Displaying Execution Plans
- Displaying the Results of EXPLAIN PLAN
- EXPLAIN PLAN May Be Misleading
- Displaying Output from the Cursor Cache
- Displaying Execution Plans from the AWR
- Understanding Operations
- What an Operation Does
- How Operations Interact
- How Long Do Operations Take?
- Chapter 4: The Runtime Engine
- Collecting Operation Level Runtime Data
- The GATHER_PLAN_STATISTICS Hint
- Setting STATISTICS_LEVEL=ALL
- Enabling SQL Tracing
- Displaying Operational Level Data
- Displaying Runtime Engine Statistics with DBMS_XPLAN.DISPLAY_CURSOR
- Displaying Runtime Engine Statistics with V$SQL_PLAN_STATISTICS_ALL
- Displaying Session Level Statistics with Snapper
- The SQL Performance Monitor
- Workareas
- Operations Needing a Workarea
- Allocating Memory to a Workarea
- Optimal,One-Pass,and Multipass Operations
- Shortcuts
- Scalar Subquery Caching
- Join Shortcuts
- Result and OCI Caches
- Function Result Cache
- Chapter 5: Introduction to Tuning
- Understanding the Problem
- Understanding the Business Problem
- Understanding the Technical Problem
- Understanding the SQL Statement
- Understanding the Data
- Understanding the Problem Wrap Up
- Analysis
- Running the Statement to Completion
- Analyzing Elapsed Time
- When the Elapsed Times Doesn’t Add Up
- When the Time Does Add Up
- Fixing the Problem
- Check the Statistics
- Changing the Code
- Adding Hints
- Making Physical Changes to the Database
- Making Changes to the Environment
- Running the SQL Tuning Advisor
- Rethink the Requirement
- Chapter 6: Object Statistics and Deployment
- The Principle of Performance Management
- The Royal Mail Example
- The Airport Example
- Service Level Agreements in IT
- Non-database Deployment Strategies
- The Strategic Direction for the CBO
- The History of Strategic Features
- Implications of the CBO Strategy
- Why We Need to Gather Statistics
- How Often Do We Need to Change Execution Plans?
- Wolfgang Breitling’s Tuning by Cardinality Feedback
- The TCF Corollary
- Concurrent Execution Plans
- Skewed Data and Histograms
- Workload Variations
- Concurrent Execution Plans Wrap Up
- Oracle’s Plan Stability Features
- Stored Outlines
- SQL Profiles
- SQL Plan Baselines
- Introducing TSTATS
- Acknowledgements
- Adjusting Column Statistics
- TSTATS in a Nutshell
- An Alternative to TSTATS
- Deployment Options for Tuned SQL
- When Just One SQL Statement Needs to Change
- When Multiple SQL Statements Need to Change
- Part 2: Advanced Concepts
- Chapter 7: Advanced SQL Concepts
- Query Blocks and Subqueries
- Terminology
- How Query Blocks are Processed
- Functions
- Aggregate Functions
- Analytic Functions
- Combining Aggregate and Analytic Functions
- Single-row Functions
- The MODEL Clause
- Spreadsheet Concepts
- A Moving Median with the MODEL Clause
- Why Not Use PL/SQL?
- Chapter 8: Advanced Execution Plan Concepts
- Displaying Additional Execution Plan Sections
- DBMS_XPLAN Formatting Options
- Running EXPLAIN PLAN for Analysis
- Query Blocks and Object Alias
- Outline Data
- Peeked Binds
- Predicate Information
- Column Projection
- Remote SQL
- Adaptive Plans
- Result Cache Information
- Notes
- Understanding Parallel Execution Plans
- Operations That Can Be Run in Parallel
- Controlling Parallel Execution
- Granules of Parallelism
- Data Flow Operators
- Parallel Query Server Sets and DFO Trees
- Table Queues and DFO Ordering
- Multiple DFO Trees
- Parallel Query Distribution Mechanisms
- Why Forcing Parallel Query Doesn’t Force Parallel Query
- Further Reading
- Understanding Global Hints
- Hinting Data Dictionary Views
- Applying Hints to Transformed Queries
- The NO_MERGE Hint
- Chapter 9: Object Statistics
- The Purpose of Object Statistics
- Creating Object Statistics
- Gathering Object Statistics
- Exporting and Importing Statistics
- Transferring Statistics
- Setting Object Statistics
- Creating or Rebuilding Indexes and Tables
- Creating Object Statistics Wrap Up
- Examining Object Statistics
- Examining Object Statistics in the Data Dictionary
- Examining Exported Object Statistics
- Statistic Descriptions
- Table Statistics
- Index Statistics
- Column Statistics
- Statistics Descriptions Wrap-up
- Statistics and Partitions
- Gathering Statistics on Partitioned Tables
- How the CBO Uses Partition-level Statistics
- Why We Need Partition-level Statistics
- Statistics and Partitions Wrap-up
- Restoring Statistics
- Locking Statistics
- Pending Statistics
- A Footnote on Other Inputs to the CBO
- Initialization Parameters
- System Statistics
- Other Data Dictionary Information
- Part 3: The Cost-Based Optimizer
- Chapter 10: Access Methods
- Access by ROWID
- ROWID Concepts
- Access by ROWID
- B-tree Index Access
- INDEX FULL SCAN
- INDEX RANGE SCAN
- INDEX SKIP SCAN
- INDEX UNIQUE SCAN
- INDEX FAST FULL SCAN
- INDEX SAMPLE FAST FULL SCAN
- INDEX JOIN
- AND_EQUAL
- Bitmap Index Access
- Full Table Scans
- TABLE and XMLTABLE
- Cluster Access
- Chapter 11: Joins
- Join Methods
- Nested loops
- Hash joins
- Merge joins
- Cartesian joins
- Join Orders
- Join orders without hash join input swapping
- Join orders with hash join input swapping
- Semi-joins
- Standard semi-joins
- Null-accepting semi-joins
- Anti-joins
- Standard anti-joins
- Null-aware anti-joins
- Distribution Mechanisms for Parallel Joins
- The PQ_DISTRIBUTE hint and parallel joins
- Full partition-wise joins
- Partial partition-wise joins
- Broadcast distribution
- Row source replication
- Hash distribution
- Adaptive parallel joins
- Data buffering
- Bloom filtering
- Chapter 12: Final State Optimization
- Join Order
- Join Method
- Access Method
- IN List Iteration
- Chapter 13: Optimizer Transformations
- No-brainer Transformations
- Count Transformation
- Predicate Move-around
- Set and Join Transformations
- Join Elimination
- Outer Join to Inner Join
- Full Outer Join to Outer Join
- Semi-Join to Inner Join
- Subquery Unnesting
- Partial Joins
- Join Factorization
- Set to Join
- Aggregation Transformations
- Distinct Aggregation
- Distinct Placement
- Group by Placement
- Group by Pushdown
- Subquery Transformations
- Simple View Merging
- Complex View Merging
- Factored Subquery Materialization
- Subquery Pushdown
- Join Predicate Pushdown
- Subquery Decorrelation
- Subquery Coalescing
- Miscellaneous Transformations
- Or Expansion
- Materialized View Rewrite
- Grouping Sets to Union Expansion
- Order by Elimination
- Table Expansion
- Star Transformation
- The Distributed Join Filter Problem
- Solving the Distributed Join Filter Problem
- In the Future
- Part 4: Optimization
- Chapter 14: Why Do Things Go Wrong?
- Cardinality Errors
- Correlation of Columns
- Statistics Feedback and DBMS_STATS.SEED_COL_USAGE Features
- Functions
- Stale Statistics
- Daft Data Types
- Caching Effects
- Transitive Closure
- Unsupported Transformations
- Missing Information
- Bad Physical Design
- Contention
- Chapter 15: Physical Database Design
- Adding and Removing Indexes
- Removing Indexes
- Identifying Required Indexes
- Managing Contention
- Sequence Contention
- The Hot-block Problem
- Partitioning
- Full Table Scans on Partitions or Subpartitions
- Partition-wise Joins
- Parallelization and Partitioning
- Denormalization
- Materialized Views
- Manual Aggregation and Join Tables
- Bitmap Join Indexes
- Compression
- Index Compression
- Table Compression
- LOBs
- Chapter 16: Rewriting Queries
- Use of Expressions in Predicates
- Equality Versus Inequality Predicates
- Implicit Data-Type Conversions
- Bind Variables
- UNION,UNION ALL,and OR
- Issues with General Purpose Views
- How to Use Temporary Tables
- Avoiding Multiple Similar Subqueries
- Chapter 17: Optimizing Sorts
- The Mechanics of Sorting
- Memory Limits for Sorts
- Disk-based Sorts
- Avoiding Sorts
- Non-sorting Aggregate Functions
- Index Range Scans and Index Full Scans
- Avoiding Duplicate Sorts
- Sorting Fewer Columns
- Taking Advantage of ROWIDs
- Solving the Pagination Problem
- Sorting Fewer Rows
- Additional Predicates with Analytic Functions
- Views with Lateral Joins
- Avoiding Data Densification
- Parallel Sorts
- Chapter 18: Using Hints
- Are Hints Supportable?
- The PUSH_SUBQ story
- The DML error logging story
- Documented versus undocumented hints
- The MODEL clause corollary
- Supportability conclusion
- Types of Hints
- Edition -based redefinition hints
- Hints that cause errors
- Runtime engine hints
- Optimizer hints that are hints
- Production-hinting case studies
- The bushy join
- Materialization of factored subqueries
- Suppressing order by elimination and subquery unnesting
- The v$database_block_corruption view
- Chapter 19: Advanced Tuning Techniques
- Leveraging an INDEX FAST FULL SCAN
- Simulating a Star Transformation
- Simulating an INDEX JOIN
- Joining Multi-Column Indexes
- Using ROWID Ranges for Application-Coded Parallel Execution
- Converting an Inner Join to an Outer Join
- Part 5: Managing Statistics with TSTATS
- Chapter 20: Managing Statistics with TSTATS
- Managing Column Statistics
- Time-based columns
- Columns with NUM_DISTINCT=1
- Skewed column values and range predicates
- Correlated columns and expressions
- Use of sample data for complex statistical issues
- Managing column statistics wrap up
- Statistics and Partitions
- The DBMS_STATS.COPY_TABLE_STATS myth
- Cardinality estimates with global statistics
- Costing full table scans of table partitions
- Temporary Tables
- The pros and cons of dynamic sampling
- Fabricating statistics for temporary tables
- How to Deploy TSTATS
