Re: A Guide to Constraint Exclusion (Partitioning)

Simon Riggs wrote:
> in those cases you are really just maintaining the indexes for partitioning
> purposes. On older data it may be desirable not to have lots of indexes,
> or at least use their resources on the indexes they really do want.
> 
> Also, if you have a List partitioned table where all rows in that table
> have a single value, then you maintain an index for no reason other than
> partitioning. Thats an expensive waste. 
> 
> Simply put, adding a constraint is faster and cheaper than adding an
> pointless index. CE gives people that option.


It seems with a partial index that whose partial index condition
specifies a range outside that of a partition would make the expense
much cheaper.

For example, if I created a couple partial indexes

    ON sales_2005(year) WHERE year<2005
    ON sales_2005(year) WHERE year>2005

I would think it would be a very cheap index to maintain
(they'd be very small because they're empty, I'd think)
and give many of the same benefits for excluding tables
as a non-partial index on year would have given.

    Ron

I like the other features Simon mentioned, though, that sound like
they're based on these constraints.