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.

• Index Usage Tracking (March 2024): the feature, and the view dba_index_usage. Part 1: a description
  • Index Usage Tracking (March 2024): Part 2 – technical bits and an outline of my test strategy.
  • Index Usage Tracking (April 2024): Part 3 – first tests of what’s tracked and what isn’t.
• Descending bug (December 2023): Results appearing in the wrong order despite an order by clause when in-lists meet “descending” indexes (fixed by 19.11).
  • Descending bug (August 2018): A query pattern against “descending” indexes that crashes in 12c (fixed by 19.11).
• sys_op_descend() (November 2023): Expanded note on the effect of descending columns on index size
• Descending max() (November 2023): “Descending” indexes don’t always do the right arithmetic and can produce odd execution plans, even for simple queries.
• 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 “fetch first n” 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.
• Index rebuild bug (Nov 2018): a performance threat rebuilding partitioned indexes.
• 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
• Index rebuild fail (July 2018): Rebuild and Create do not honour the table_cached_blocks preference.
• Advanced index compression (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)
• Over-Indexing (Jan 2013): Thoughts on minimising the costs of index maintenance.
• 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.
• B-tree Sizing (Feb 2010): report the size a (non-compressed) B-tree index would be if rebuilt at a given pctfree.
• Index Explosion (Sept 2009): An index to a series on how even low concurrency can have a big impact on ITL “wastage” in an index.