IOTs

Updated 2021 with a catalogue of articles that I’ve written about IOTs since I first posted this note.

That’s Index Organized Tables, of course. Searching back through my blog I find that I’ve only written a couple of articles about IOTs although I’m very keen on taking advantage of them and have made a few references to them in other articles. Rather than addressing this oversight myself, I thought I’d direct you to a series on IOTs by Martin Widlake.

• Part 1: The basics
• Part 2: Examples and Proofs
• Part 3: Greatly reduced I/O
• Part 4: Boosting buffer cache efficiency
• Part 5: Primary Key issues
• Part 6a: Inserts and updates slowed down
• Part 6b: OLTP inserts into an IOT

Updated Feb 2014 with another worthwhile catalogue of articles

Richard Foote’s array of articles on IOTs

• An introduction
• IOTs – pctthreshold
• Overflow segments – pt. 1
• Overflow segments – pt. 2
• Secondary Indexes – primary considerations
• Secondary Indexes – an introduction
• Secondary indexes (logical rowid) – pt.1
• Secondary indexes (logical rowid) – pt.2 

Update Feb 2021 – a catalogue of my own IOT articles in date order:

• A case study of IOTs and an optimizer error (Feb 2008)
• IOTs and Block Size (Oct 2008)
• Online index rebuilds have an IOT issue (June 2009)
• IOTs, FIFOs, Queues and rebuilds (Mar 2011)
• A little quiz on IOT execution plans and the answer (Nov 2011)
• A trap when using the “including” clause (Dec 2011)
• Thoughts on loading an empty IOT as quickly as possible (Nov 2012)
• auto_sample_size and IOT overflow segments don’t mix (Mar 2014)
• The problem of gathering stats on an IOT (Sept 2016)
• IOTs and direct path loads – /*+ append */ (July 2018)
• How sys_op_lbid() is used with IOTs (Mar 2019)
• Online doesn’t cover everything (Jul 2019)
• Hidden costs of hash joins with IOTs (Oct 2019)
• Space threat from bug in secondary indexes (Dec 2019)