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.