Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: partitioning
The phantom tablespace
(Cueing my deep baritone Morpheus voice…) What if I told you that you can reference non-existent tablespaces in your DDL? OK, it sounds like a gimmick but there is a real issue that I’ll get to shortly. But first the gimmick I’ve created a…
Interval versus Range partitions
One of the nice things about partitioning in the database is that partition pruning can quickly eliminate the requirement to read large amounts of data when the partitioning key(s) are appropriately provided in your queries (and DML). This also extends to queries where the…
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…
