Indexing Catalogue

This is a list of all articles I’ve written that talk about indexes and indexing strategy. Each entry has a date stamp and a short note of the contents. The articles are (will be) grouped in a couple of different categories, but otherwise are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

• Empty indexes (Mar 2023): completely null entries do not get stored in B-tree indexes, but does Oraclel include them in the big index sort?
• Global Rowids (February 2023): a note describing how rowids are stored in global indexes
• Foundation thoughts (Jan 2023): before you commit to creating specific indexes on a table take a moment to go back to general principles and review your strategy.
• Shrinking Indexes (Sept 2022): A comparison of coalese and shrink space (with or without compact).
• Drop Partition (Aug 2022): the mechanics and cost of deferred global index maintenance
• Index Wildcard (July 2022): Leading wildcards and cursor_sharing can have an unexpected side effect on indexed acccess paths.
• 19c – better stats (April 2022): Oracle now uses approx_count_distinct() on the whole index in dbms_stats.gather_index_stats().
• Generated predicates (March 2022): virtual columns from “function-based” indexes may allow the optimizer to generate predicates that you weren’t expecting to see.
• Index ITL Limits (Feb 2022): stressing an index to the point of raising an error
• OER_06051 (Jan 2022): How large can the blevel of an index get
• Names matter (Nov 2021): renaming an index can change an execution plan
• Function based indexes (July 2021): excellent use case, plus risk warning
• Index hints – pt. 1 (Jan 2021) – a voyage of discovery as different index hints (range scan, skip scan, fast full scan) don’t do exactly what you expect.
  • Index hints – pt. 2 (Jan 2021) – continuing the voyage of discovery with index skip scans and range scans.
• Index descending First N (Dec 2020): until 19c an first rows in descending order had to be hinted with index_desc()
• Engineering indexes (Jan 2020): A case study about designing indexes to get maximum benefit for minimum cost.
• num_index_keys() hint (Nov 2018): how to use it
• Index leaf block splits (Oct 2018): The mechanics of index leaf block splits
  • Leaf block splits part 2 (Oct 2018): Examining the row directory
  • Leaf block splits part 3 (Oct 2018): Redo and block dumps for leaf block splits
• Advanced index comprion (Dec 2016): a first examination
• Dropping an index that’s not visible in an execution plan CAN cause the plan to change (June 2016)
• Oracle will use an index if the number of rows to fetch from the table is WHAT percent? (Mar 2016)
• Using segment statistics to check for unused indexes (Sept 2015)
• Using constraints to eliminate indexes (Aug 2015)
  • Another example of using constraints to eliminate indexes (Sept 2015)
• How do I identify the indexes I could safely drop? (Aug 2015)
• Index FFS Deletes (June 2014): From 12.2 Oracle can use an index fast full scan to drive a delete (or, in limited cases) an update. Still not able to drive a select.
• New Index hints (June 2013): Analysis of how Oracle handles “near misses” on hinting by index description.
• Basic (free) index compression – written for Simpletalk (Feb 2013)
  • The overheads of (basic) index compression – written for Simpletalk (Feb 2013)
• Minimum Stats (Sept 2012): auto_sample_size on an index tends to mean 1,140 blocks
• Bitmap sizing (May 2012): Why it’s hard to predict the size of a bitmap index
• The optimizer uses index distinct_keys the same way it uses column group (extended) stats (Mar 2012)
• New Index hints (Nov 2011): Oracle 10g introduced an index hinting by description. Some examples and comments.
• Index Explosion (Sept 2009): An index to a series on how low concurrency can have a big impact on ITL “wastage” in an index.