Hemant K Chitale wrote:Indeed. I suppose that my other hypothetical method would really be what you might call "unique list" partitioning. I also suppose that theoretically list partitioning could be enhanced for automatic creation of new partitions for each new unique value of the partition key column, similar to interval partitioning. Maybe not a common enough requirement, like you say.
Such optimization would be possible only with LIST Partitioning.
Jonathan Lewis wrote:I think it's worth linking this thread forward to an example from C Joshi where a specific execution plan is not available to the optimizer because the index doesn't contain the partitioning column - even though the optimizer could determine that the relevant predicate was redundant after partition pruning: Non-prefixed local indexes and fast full scansUwe Hesse wrote:Uwe,
Generally speaking, a local prefixed index enables partition pruning on the index level for statements that list the index key in ther where clause. A nonprefixed local index doesn't.
Can you supply a demonstration to show exactly what you mean. I believe that that argument ceased to be true in 8.1.6 or 8.1.7 when the optimizer code caught up with the fact that you could do partition pruning at the index level even if the partitioning column wasn't part of the (local) index. For example: