online

One of the very cool features from 12.2 onwards is that the vast majority of segment operations the require rewriting the existing data can be done online without interruption to service. But whether an operation is being done online or not, since DDL operations need to be atomic, the general rule is that when an object is being reorganised this work cannot be done in place. The reorganised segment must be written elsewhere in order for the original segment to be left untouched in the event of the operation failing midway. (No-one wants their table to vanish just because someone pulled out the wrong power cable!)

General consensus from the “blog-sphere” is that you thus need enough space to hold the existing segment and space for the new segment to be created, but I thought I’d explore that a little further, and there’s a few surprises.

Today’s video gives a demonstration of Online Statistics Gathering for Bulk Loads.

This is based on this article.

By Franck Pachot

.
We have an incredible number of possibilities with Oracle. Yes, an index can be global (indexing many partitions without having to be partitioned itself on the same key) and partial (skipping some of the table partitions where we don’t need indexing). In the previous post of this series of small examples on recent features I partitioned a table, with covid-19 cases per day and per country, partitioned on range of date by interval. The index on the country code (GEOID) was not very efficient for data ingested per day, because countries are scattered through all the table. And then I have reorganized the old partitions to cluster them on countries.

My global index on country code is defined as:

By Franck Pachot

.
In this series of small examples on recent features, I have imported in a previous post, the statistics of covid-19 per day and per countries. This is typical of data that comes as a time-series ordered by date, because this is how it is generated day after day, but where you probably want to query from another dimension, like per countries.

By Franck Pachot

.
On Twitter, Ludovico Caldara mentioned the #licensing #pitfall when using the Online Partition Move with Basic Compression. Those two features are available in Enterprise Edition without additional option, but when used together (moving online a compressed partition) they enable the usage of Advance Compression Option:

Will it be reflected in dba_feature_usage_statistics?

— Niels Jespersen (@njesp) April 19, 2020

In today’s video we demonstrate how to move, or rebuild, a table as an online operation.

This video was done as a response to some questions about the previous video on shrink operations. As usual, the video is based on some stuff I’ve written previously.

Today’s video is a quick look at online datafile moves in container databases (CDBs) and pluggable databases (PDBs).

If you’ve used this functionality in a non-CDB database, it’s going to look familiar, with a PDB-specific gotcha.

These articles discus moving and renaming files.

If you have performed some sort of data cleanup or similar on a table, then the deleted space will be reused by future insertions. But if

• that cleanup was the last task you were performing on that table, ie, you were not expecting a lot of new data to ever come in again, or
• you are performing a lot of full scan queries on that table and you want to make sure they are as efficient as possible

then there may be benefits to performing a shrink on that table to reclaim that space. One of the cool things about the segment advisor is that it will detect if there are some benefits to be gained by shrinking a segment. Here’s an example of that. I create a large table and then delete every 2nd row.

Just a quick post here by request of an attendee of the September Office Hours. I had a demo converting the data type of a primary key without losing data and with minimal impact to the availability of the database using DBMS_REDEFINITION.  You can see that video here

but here is the script used to run that demo. 

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 transactional-style system the busiest table (let us call it SALES for the sake of this discussion) is also

• the biggest table, after all, it has all of our sales in it,
• the most demanded for table, in that, almost every query in our application wants to access it in some way shape or form.

This is in effect the database version of the Pareto Principle. Everyone wants a slice of that SALES “pie”, and the piece of that pie that is in most demand is typically the most recent data. Your application may have pages that will be showing: