Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: partitioning
Partition loading in direct mode
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…
Raw partitions?
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…
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…
