Presentations & Demos
For easy reference, a complete list of all presentations and demos as they get included and discussed in the Blog:
Do ROWID Index Row Entry Columns Impact Index Block Splits: Does the ROWID that constitutes an additional column in a Non-Unique index determine whether a particular row entry is the maximum or equivalent entry or not. Because by implication, this can determine and influence whether Oracle performs the generally preferred 90-10 splits rather than 50-50 block splits for indexed column values that at least equal the maximum value.
Issues With Local Indexes and PK/Unique Constraints: An old demo that just highlights some of the issues regarding the use of a Local Index to police a PK or Unique Key constraint
Oracle Indexing New Features In 11g R1 and R2 Presentation: My Oracle OpenWorld 2010 presentation on various new indexing related features introduced in Oracle11g.
Yet Another Presentation On Extended SQL Trace: An older presentation of mine on the benefits of Extended SQL Trace
Larger Block Tablespace and Small Index Scans: Shows how rebuilding an index in a larger block tablespace can actually decrease performance of OLTP transactions, even in the event of the index height being reduced
Larger Block Tablespace and Index Height: Shows how the index height may not necessarily decrease as a result of rebuilding in a larger block tablespace and how in many scenarios is actually quite a rare event
Larger Block Tablespace and Improved Performance Illusion: Highlights how improved multiblock read performance after rebuilding an index in a larger block tablespace is likely to have nothing whatsoever to do with the larger block tablespace and is a result of a secondary change (such as improved index structure, faster disks, etc.)
Larger Block Tablespace and Multiblock Reads: Highlights how the read size of a multiblock read is actually identical, regardless of the block size of the tablespace
Index Skip Scan Demo: Highlights how an Index Skip Scan access path can be useful and how the cardinality of the leading column in an index is crucial to the viability of an Index Skip Scan
Index Compression Part IV: Shows how you can successfully compress a one byte column if the cardinality is low enough
Index Compression Part III: Shows how to compress Unique Indexes
Index Compression Part II: Shows how index compression can actually increase, not decrease the size of an index if implemented inappropriately
Index Compression Part I: Shows basically how index compression works and is implemented by Oracle
Clustering Factor In Concatenated Index Leading Column Decision: Shows how the Clustering Factor and ordering of the specific columns in a concatenated index impacts on the Clustering Factor of the resultant concatenated index.
High Cardinality Leading Columns Don’t Improve Index Efficiencies: Dispels the myth that having a high cardinality column as the leading column in a concatenated index is better and improves index performance compared to a low cardinality leading column
Index Create and Index Rebuild 11g Locking Improvements: Shows how 11g has improved the associated locking implication of creating or rebuilding an index online.
When to use Rebuild vs Coalesce vs Shrink Space: Highlights when it’s best and more efficient to use a Rebuild vs Coalesce vs a Shrink Space
Comparision Between Coalesce and Shrink Space: Shows the similarities and differences between Index Coalesce and Shrink Space
Alter Index Coalesce 10g Improvements: Shows how Coalesce can be applied to indexes with less than 50% of free space since 10g
Bitmap Index Many Distinct Values: Breaks the myth that Bitmap Indexes should only be used to index “low” cardinality columns but potentially useful for any number of distinct column values
How Are NULLs Actually Stored: This demo shows how index block dumps can be particularly useful, here to illustrate how NULLs are physically indexed and sorted within an index B-Tree
Index Only Values Of Interest: Details how a function-based index can be used to index only specific column values of interest, potentially substantially reducing the size of the index
Indexing NULLs: Shows how one can actually index NULL values
Reverse Key Indexes Part IV: Shows how the Clustering Factor can change from excellent to appalling by rebuilding an index to be a Reverse Key Index
Reverse Key Indexes Part III: Shows how block splitting and the reuse of deleted space differs between a Reverse and a Non-Reverse Key Index and how it impacts overall index space utilisation.
Reverse Key Indexes: Part II: Shows how Reverse Key Indexes can’t be used to get around the problem of having LIKE wildcards in the front of a search criteria but you could use the unsupported Reverse Function-Based Index with single-byte character sets.
Reverse Key Indexes: Part I: Shows how Reverse Key Indexes can’t be used with Range Predicates, but can perform Index Range Scans in some scenarios.
Intro to Fake/Virtual/Nosegment Indexes: Highlights how to create a “Fake” index that doesn’t really exist to test what might happen if it were to be really created
Dangers of METHOD_OPT SIZE AUTO: Shows how the AUTO option with METHOD_OPT, changed to be the default in 10g, can be very problematic as it generates many unnecessary histograms while missing out on generating those that may be required.
Linguistic Indexes Sizes: Shows how Linguistic Indexes can take up considerably more storage than normal binary indexes.
Linguistic Indexes with LIKE and MIN/MAX Predicates: Restrictions of Linguistic Indexes with some predicate types
Mixing Linguistic Indexes with Non-Linguistic Indexes: Dangers of trying to mix/use Linguistic and Non-Linguistic indexes at the same time.
Intro to Linguistic Indexes: Highlights how Linguistic Indexes can be useful in performing case-insensitive searches without having to necessarily modify application code
Redo/Undo Differences Between Unique and Non-Unique Indexes: Highlights the fact Unique indexes have less redo/undo overheads if a PK or UK constraint is violated
Latch Differences Between Unique and Non-Unique Indexes: Highlights the fact Non-Unique indexes have in the order of double the associated latch overheads of Unique indexes
Compare Internal Index Storage Between Unique and Non-Unique Indexes: Shows how Non-Unique indexes require an additional byte of storage per index row entry compared to Unique Indexes and how this can lead to reasonably significant additional overheads for small sized index entries
Local Index Issue With Partitioned Primary Key Index: Why Local Indexes must include the partitioning column if used to police Primary Key or Unique Key constraints.
CBO Check Constraint Trick in 10g Demo: Shows how the CBO can immediately return an empty row set, with no LIOs, for searches of column values that violate a validated and nondeferrable check constraint
CBO NOT NULL Trick in 10g Demo: Shows how the CBO can immediate return an empty row set, with no LIOs, for searches of nulls on columns with a validated and nondeferrable NOT NULL constraint
CBO NOT NULL Trick in 9i Demo: Shows how the CBO uses an index to return an empty row set for searches of nulls on columns with a validated and nondeferrable NOT NULL constraint
Outlier Index Space Utilisation Demo: Shows how an outlier value can prevent 90-10 index leaf block splits, resulting in a fragmented index structure
Outlier Selectivity Demo: Shows how an outlier value can ruin the selectivity calculations of the CBO and result in an inefficient execution plan
11g Invisible Indexes Demo: Shows how indexes can be made invisible and ignored by the CBO
Oracle Index Internals - Rebuilding The Truth : An article originally presented for the ACT Oracle User Group way back in 2002 and which I still get asked about to this day. Somewhat revised edition.