At present I have 2,130 SQL scripts on my laptop, of which about 130 are tools that I use for trouble-shooting. The rest are models of problems I have come across at client sites, demonstrations of bugs that I’ve sent in to Oracle, or test cases that I’ve used to investigate the way some feature of Oracle works.
Of the 2,000 scripts that aren’t aids to trouble-shooting, there are 123 scripts with the word “index” in their name – in second place are the scripts with “pt” (for partitioning) in their name with 72 scripts.
The numbers may not give you the same shock that they gave me, so you may have to take my word for it that if 6% of my scripts are about indexing that’s a pretty stark indication of the degree to which indexes mess up people and people mess up indexes.
It doesn’t quite make it to the “Philosophy” list, but it’s almost a truism for any badly performing system: “You need to review your indexing strategy.”
Update Jan 2016
I’ve just rediscovered this note and thought I’d check the numbers. It’s currently: 3,958 scripts of which 322 are about indexing and 240 are about partitioning. (There’s a little overlap, with about 20 scripts that are explicitly named for partitioned indexes.)