Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: partitioning
Partial Indexes–Take Care With Truncate
Partial indexes are a very cool feature that came along with Oracle 12c. The capability at partition level to control index existence allows for a couple of obvious use cases: 1) You index the data in recent partitions only, because small amounts of data…
Partial indexing – get the dictionary definitions right
Just a quick post to clear up some confusion that can be seen on the partial indexing capabilities in Oracle Database 12c and above. I was at an event in the Oracle Perth office last week, and an attendee mentioned that they thought the…
Hyper-partitioned index avoidance thingamajig
As you can tell, I have no idea on a name for what I am about to describe. So let me start from the beginning, and set the scene for an idea I have to utilize a cool new 18c feature. Often in a…
Cool stuff with partition elimination
Sometimes in the IT world, the term “surprise” is not a good one. “I woke up this morning and got a surprise…my database was down.” “I ran a SELECT COUNT(*) on my most important table, and got a surprise result of zero rows.” and…
18c merge partition online
One of the cool things in 18c is the ability to merge partitions without causing a service interruption. Here’s a video demonstration of that in action: This is just an accompanying blog post to let you grab the scripts for the demo so that…
iASH–my “infinite ASH” routine
I love Active Session History (ASH) data because a lot of the work I’ve done in my consulting life was “after the fact” diagnosis. By this I mean that many of us have been in a similar circumstance where the customer will contact you…
Partition-wise join
So just what is a “partition-wise” join ? We will use a metaphor to hopefully explain the benefit. Let’s say two people, Logan and Shannon, decide to move in together. If each of them already have an existing residence, they will both have a…
Updating indexes with partition maintenance
An index is basically a structure that maps keys (values) in columns to the physical location of their corresponding rows in a table. So if you move the rows (ie, change the physical location of a row) then the index entries for those rows…
Partitioning Guide updated
Just a quick note to say I’ve updated our Getting Started With Partitioning Guide! 9 easy to follow demonstrations to get developers up to speed with handling large databases. Check it out here
12c Statistics on load–special cases
One of the cool features in 12c is the automatic collection of optimizer statistics when a table is either created or loaded via direct path from empty. This makes a lot of sense because it saves us from what used to be the mandatory…
Interval partitioning just got better
Interval partitioning was a great feature when it arrived in version 11, because we no longer had to worry so much about ensuring partitions were available for new data when it arrived. Partitions would just be created on the fly as required. I’m not…
Partition count for interval partitioned tables
When dealing with a RANGE partitioned table, the defined partitions dictate all of the data that can be placed into the table. For example, if I have a SALES table as per below SQL> create table SALES 2 ( cal_year date, 3 txn_id int,…
