SEARCH
Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.
BOOK EXPERT INDEXING IN ORACLE DATABASE 11G
SUMMARY
-
Items Found: 189
- Chapter 1: Oracle Indexes
- Improving Performance with Indexes
- Determining Which Type of Index to Use
- B-tree Indexes
- Specialized Index Types
- Determining Which Columns to Index
- Indexes on Primary Key and Unique Key Columns
- Indexes on Foreign Key Columns
- Other Suitable Columns
- Indexing Guidelines
- Chapter 2: B-tree Indexes
- Understanding How Oracle Uses B-tree Indexes
- Scenario 1: All Data Lies in the Index Blocks
- Scenario 2: All Information Is Not Contained in the Index
- Scenario 3: Only the Table Blocks Are Accessed
- Prepping for B-tree Indexes
- Estimating the Size of an Index Before Creation
- Creating Separate Tablespaces for Indexes
- Inheriting Storage Parameters from the Tablespace
- Naming Standards
- Implementing B-tree Indexes
- Creating a B-tree Index
- Reporting on Indexes
- Displaying Index Code
- Dropping a B-tree Index
- Managing B-tree Indexes with Constraints
- Creating B-tree Index on Primary Key Columns
- Creating a B-tree Index on Unique Key Columns
- Indexing Foreign Key Columns
- Chapter 3: Bitmap Indexes
- Understanding Bitmap Indexes
- Creating a Bitmap Index
- Creating a Partitioned Bitmap Index
- Creating a Bitmap Index on an Index-Organized Table
- Performance Implications Querying with Bitmap Indexes
- Performance Implications Loading Data with Bitmap Indexes
- Understanding Bitmap Join Indexes
- Creating a Bitmap Join Index
- Reporting on Bitmap Indexes
- Chapter 4: Index-Organized Tables
- Understanding the Structure
- Understanding the Advantages
- Creating an Index-Organized Table
- Adding an Overflow Segment
- Compressing an Index-Organized Table
- Building Secondary Indexes
- Rebuilding an Index-Organized Table
- Reporting on Index-Organized Tables
- Chapter 5: Specialized Indexes
- Invisible Indexes
- When to Create an Invisible Index
- Creating an Invisible Index
- Finding Invisible Indexes In Your Database
- Making an Invisible Index Available to the Optimizer
- Maintaining an Invisible Index
- Function-Based Indexes
- Creating a Function-Based Index
- Limitations of Function-Based Indexes
- Collecting Statistics for Function-Based Indexes
- Indexes on Virtual Columns
- Key-Compressed Indexes
- When Key Compression is Useful
- Creating a Compressed Index
- Key Compression and Storage
- Composite Indexes
- Understanding Index Skip Scans and Composite Indexes
- Ordering the Columns in a Composite Index
- Choosing Keys for Composite Indexes
- Creating Virtual Indexes
- Reverse Key Indexes
- Disadvantages of a Reverse Key Index
- When to Use a Reverse Key Index
- Creating a Reverse Key Index
- Application Domain Indexes
- Chapter 6: Partitioned Indexes
- Understanding Partitioned Indexes
- Creating a Locally Partitioned Index
- The Simplest Form
- Partition-Level Requirements
- Prefixed and Non-Prefixed Options
- Managing Primary Keys and Unique Indexes
- Creating a Globally Partitioned Index
- Choosing the Type of Index for Your Application
- Maintaining Indexes on Partitioned Tables
- Adding a Partition
- Truncating a Partition
- Moving a Partition
- Splitting a Partition
- Exchanging a Partition
- Dropping a Partition
- Merging a Partition
- Rebuilding Globally Partitioned and Non-Partitioned Indexes
- Setting Index Partitions as Unusable and Then Rebuilding
- Index Implications for Interval Partitioning
- Making Older Data Read-Only
- Reporting on Partitioned Indexes
- Chapter 7: Tuning Index Usage
- Optimizer Access Paths
- Index Scans
- Index Unique Scan
- Index Range Scan
- Index Skip Scan
- Index Full Scan
- Index Fast Full Scan
- Determining Whether a Query Uses an Index
- Avoiding an Index
- Avoiding All Use of an Index
- Avoiding Only the Fast Full Scan
- Forcing a Table Scan
- Choosing Between an Index and a Table Scan
- Why the Optimizer May Ignore Indexes
- Number of Distinct Rows
- Index Clustering Factor
- How Index Access Paths Can Change Without New Statistics
- Using the NOT EQUAL Condition
- Querying with Wild Characters
- Referencing Null Values in Predicates
- Writing Functions in a Query
- Skipping the Leading Portion of an Index
- Forcing the Optimizer to Use an Index
- Applying the INDEX Hint
- Applying Related Hints
- Troubleshooting a Failed INDEX Hint
- Adjusting the Optimizer_index_cost_adj Parameter
- Collecting Accurate Statistics for an Index
- Parallelizing Index Access
- Chapter 8: Maintaining Indexes
- Gathering Statistics for Indexes
- The DBMS_STATS Package
- The METHOD_OPT Parameter
- Working with Unusable Indexes
- Making an Index Unusable
- Specifying the SKIP_UNUSABLE_INDEXES Parameter
- Managing Index Space Usage
- Rebuilding to Reduce Fragmentation
- Rebuilding Reverse-Key Indexes
- Reclaiming Unused Space
- Rebuilding a Partitioned Index
- Rebuilding Indexes Frequently
- The Role of the INDEX_STATS View in Index Rebuilds
- Benefits from the INDEX_STATs view
- Problems with the INDEX_STATS view
- Index Rebuilding: The Debate
- Arguments for Rebuilding
- Arguments Against Rebuilding
- Our Advice
- Coalescing Indexes to Reduce Fragmentation
- Shrinking Indexes to Reduce Fragmentation
- Moving Tables and Indexes
- Improving Index Creation Efficiency
- Parallelizing Index Creation
- Avoiding Redo Generation During Index Creation
- Using Larger Block Sizes
- Compressing Indexes
- Using Multiple Options Together
- Generating the DDL for Creating an Index
- Using the DBMS_METADATA Package
- Using the SESSION_TRANSFORM Procedure
- Using the SET_FILTER Procedure
- Using Data Pump
- Dropping an Index
- The Hazards of Dropping an Index
- Chapter 9: SQL Tuning Advisor
- How the Tools Relate
- Automatic SQL Tuning Job
- Verifying Automatic Jobs Running
- Viewing Automatic SQL Tuning Job Advice
- Generating a SQL Script to Implement Automatic Tuning Advice
- Disabling and Enabling Automatic SQL Tuning
- Managing SQL Tuning Sets
- Viewing Resource-Intensive SQL in the AWR
- Viewing Resource-Intensive SQL in Memory
- Populating SQL Tuning Set from High-Resource SQL in AWR
- Populating a SQL Tuning Set from High-Resource SQL in Memory
- Populating SQL Tuning Set with All SQL in Memory
- Displaying the of a SQL Tuning Set
- Selectively Deleting Statements from a SQL Tuning Set
- Adding Statements to an Existing SQL Tuning Set
- Dropping a SQL Tuning Set
- Running the SQL Tuning Advisor
- Creating a Tuning Task
- Execute DBMS_SQLTUNE and View the Advice
- Viewing and Dropping Tuning Tasks
- Running SQL Tuning Advisor from SQL Developer
- Running SQL Tuning Advisor from Enterprise Manager
- Chapter 10: SQL Access Advisor
- Generating Advice for a Single SQL Statement
- Obtaining Advice for a Group of SQL Statements
- Querying Advisor Views
