Is it a bad idea to have a large number of extents?
Let's say we have 1 billion rows in 125 extents with 125mio rows each. The values in the query column are inserted in order so that every extent has a different min-max-range. If we make a select with "where column=123" the extent elimination should make 125 range comparisons with the extents min-max-values and identify the one interesting extent. Now all the 125mio rows in there get checked for that value. Thats a total of +- 125 mio comparisons.
If we have 12500 extents with 1.25mio each, we get a total of a bit more than 1.25mio comparisons. So in theory that should be nearly 100x as fast.
Now the questions are:
Is there a major drawback if you raise the number of extents by lowering the config value ExtentRows?
What is still a good number of extents? 1k, 10k, 100k, 1m?
What if ExtentRows gets lowered to 8k from 8m?
I think for ordered columns the min-max-extent-elimination is a good way to boost performance, so everyone should find the sweet spot of ExtentRows for his data and system.