Tag: partitioning

When local partitions….aren’t

Let’s say I’ve got a partitioned table, and because New Year’s eve is coming around, I certainly don’t want to be called out at 12:01am because I forgot to add the required partition for the upcoming year . Since 11g, I can sleep easy…

A 12.2 treat for the festive season

We’re being asked to store more and more data, yet keep backup windows, query performance and the like unchanged, no matter how much we store. As a result, more and more database shops are needing to partition their data. The problem is – partitioning…

Exceeding 1 million partitions

In my previous post we saw that the partition number in the execution plan might not align with the partition position in the data dictionary when it comes to interval partitions.  As we saw, the partition numbers are preordained based on the low boundary…

Pending statistics and partition queries

This issue came through from an AskTom question, that turned out to be a known bug. In my talks on optimizer statistics, I’ve often mentioned the benefit of using pending statistics as a risk mitigation facility, and since this bug involved pending statistics, I…

The non-existent partition

Things get a little interesting in the data dictionary when it comes to interval partitions.  Consider the following example: SQL> create table t 2 partition by range(x) interval(1) 3 ( partition p0 values less than (1) ) 4 as 5 select rownum x from…

Partitioning an existing index

I had a question on AskTom recently, where due to concurrency issues, the poster of the question wanted to take their existing primary key index and hash partition it to reduce the “hotness” of the leading leaf block(s).  Their database was 11g, but I…

A cool thing with EXCHANGE PARTITION (part 2)

In the previous post, I showed that even though a partition was “removed” (ie, exchanged out) from a table, a query running against the table could still successfully run the completion. However, of course, if once that partition is exchanged out, it is now…