partitioning

Just a quick post to ensure people don’t get caught out by a recent “trick” I saw on an AskTOM question for extracting the HIGH_VALUE out of it’s LONG storage in the dictionary to a more appropriate datatype. A reviewer (I’m sure in good faith) posted the following mechanism to extract the HIGH_VALUE

I’m sure there will be a slew of post-Kscope wrap up posts coming out into the blogosphere, so in lieu of that, and the the fact that I’m just stuck in an airport waiting for a flight, I’ll offer something slightly more technical. I did a post a while back about a curious error “unable to get a stable set of rows” when using MERGE. Here is another variant which can occur when you allow rows to physically move during a MERGE.

“How is that possible?” I hear you ask. Easy. All we need is partitioned table with ENABLE ROW MOVEMENT.

Last week I wrote a note about turning a range-partitioned table into a range/list composite partitioned table using features included in 12.2 of Oracle. But my example was really just an outline of the method and bypassed a number of the little extra problems you’re likely to see in a real-world system, so in this note I’m going to bring in an issue that you might run into – and which I’ve seen appearing a number of times: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION.

In yesterday’s note on the options for converting a range-partioned table into a composite range/list parititioned table I mentioned that you could do this online with a single command in 18c, so here’s some demonstration code to demonstrate that claim:

I wrote a short note a little while ago demonstrating how flexible Oracle 12.2 can be about physically rebuilding a table online to introduce or change the partitioning while discarding data, and so on.  But what do you do (as a recent question on ODC asked) if you want to upgrade a customer’s database to meet the requirements of a new release of your application by changing a partitioned table into a composite partitioned table and don’t have enough room to do an online rebuild. Which could require two copies of the data to exist at the same time.)

If you’ve got the down time (and not necessarily a lot is needed) you can fall back on “traditional methods” with some 12c enhancements. Let’s start with a range partitioned table:

Direct mode insert using the APPEND hint is a cool piece of technology that lets you load bulk data into a table very quickly and efficiently. Obviously there are a number of implications of doing so which you can read about here, but the one that catches most people out is the that you are locking the table during the load and once the load is completed, the table is “disabled” until the transaction ends. Here’s a quick example of that in action:

Here’s a quirky one for you. It can happen when you are dealing with a partitioned table where the partition key is defined as RAW. To be honest, I really can’t think of a reason why you ever want a table with a raw partition key. (If you have one, please let me know in the comments). Anyway, here’s the demo. I’ll start with a table using the cool automatic list partition facility and load a single row into it.

Here’s an important thought if you’ve got any large tables which are purely hash partitioned. As a general guideline you should not need partition level stats on those tables. The principle of hash partitioned tables is that the rows are distributed uniformly and randomly based on the hash key so, with the assumption that the number of different hash keys is “large” compared to the number of partitions, any one partition should look the same as any other partition.

I published a note a few years ago about using the 12c “with function” mechanism for writing simple SQL statements to takes deltas of dynamic performance views. The example I supplied was for v$event_histogram but I’ve just been prompted by a question on ODC to supply a couple more – v$session_event and v$sesstat (joined to v$statname) so that you can use one session to get an idea of the work done and time spent by another session – the first script reports wait time:

Global statistics are complex to gather. Gathering on the whole table can be very long and doesn’t ‘scale’ because the duration will increase with the volume. Incremental gathering can be a solution but has its side effects (such as the size of the synopsis). But having stale global statistics can be dangerous. Do you know when the optimizer bases its estimation on global or on partition level statistics? The partition level statistics are used only:

• when partition pruning iterates on only one single partition
• when this partition is known at optimization time during the parse phase

This is clearly explained in Jonathan Lewis ‘Cost-Based Oracle Fundamentals’: