Oracle Database 12cR2 new feature: Container Maps

Introduction

Oracle introduced a new cool concept called “Application Containers” in 12cR2 (12.2.0.1.0). I have already written about this topic in the article “Introduction to Application Containers in Oracle Database 12cR2”, where you can find an introduction to the topic and see a couple of examples. Since version 8.0, Oracle Database has had the partitioning feature, which helps you access data faster. Since 8.0 there have been several enhancements for partitioning, types of partitions, objects that supports partitioning, etc. In Oracle Database version 12.1.0.2 Oracle introduced the “CONTAINERS” clause, a very useful clause that can be used to execute queries across several Pluggable Databases. 

You can filter which PDB you want to get the data from by the CON_ID column. The downside of using the “CONTAINERS” clause is that you have to hard code the value of the CON_ID column. If the CON_ID changes because of a PDB unplug and a PDB plugin, you would be getting data from a wrong PDB; or if you remove the PDB, your queries will simply fail. There should be a way to use the “CONTAINERS” clause without hard coding the CON_ID, and, even better, why not to combine it with partitioning?  Basically, this was Oracle was thinking, and then the following insight occurred:

What if we use Pluggable Databases as partitions?

What if the PDB name is used instead of the CON_ID?

Thanks to this insight, “Container Maps” was introduced in Oracle 12.2.0.1.0. Unfortunately, at present, “Container Maps” are not available to use with normal Pluggable Databases. “Container Maps” can be used only with Application Containers (Application Root + Application PDBs).  

The illustration below shows how “Container Maps” works. In it, you see an end user executing a query and filtering the data by country=’GUATEMALA’. Internally, Oracle uses Application PDBs as partitions, where each Application PDB represents the data of a specific region (North, Central, South). After determining in which “partition” (Application PDB) all the files with the country=’GUATEMALA’ are located , Oracle then proceeds to query the table which is stored in that specific “Application PDB” –in this case, the Application PDB named “CENTRAL”. Of course, the table can also be partitioned as always, using all the enhancements in Oracle partitioning up to version 12.2.0.1.0.

In the following example we will explain step-by-step how to use “Container Maps”.

Create an Application Root:

First, I will create an “Application Container”, an Application Root named “Nuvola”, and three “Application PDBs” named “NORTH”, “CENTRAL” and “SOUTH”. If you want to read more about Application Containers you can read my article Introduction to Application Containers in Oracle Database 12cR2.

Creating the Application Root:

SQL> create pluggable database Nuvola as application container admin user pdbadmin identified by Nuvola1; 
Pluggable database created.
SQL> alter pluggable database Nuvola open;
Pluggable database altered.
 
In order to create “Application PDB” you must be connected to the “Application Root”:
SQL> alter session set container=Nuvola;
Session altered.
SQL> show con_name
CON_NAME
-----------------------------
NUVOLAAPPROOT

Creating the Application PDB named “North”:

SQL> create pluggable database north admin user app1admin identified by Nuvola1;
Pluggable database created.

Creating the Application PDB named “Central”:

SQL> create pluggable database central admin user app1admin identified by Nuvola1;
Pluggable database created.

Creating the Application PDB named “South”:

SQL> create pluggable database south admin user app1admin identified by Nuvola1;
Pluggable database created.

Opening all the Application PDBs:

SQL> alter pluggable database all open;
Pluggable database altered.

Creating the container map table:

 A container map is a simple table that has the information on which “partitions” (Application PDBs) are used and which column is used to address the data; in this case the column “country”. The type of partitioning used here is “BY LIST”. Note that the name of the “partitions” matches exactly with the name of the “Application PDBs”.

SQL> CREATE TABLE c##dgomez.containermap (
country VARCHAR2(30) NOT NULL)
PARTITION BY LIST (region) (
PARTITION north VALUES ('CANADA','USA'),
PARTITION central VALUES ('GUATEMALA','NICARAGUA'),
PARTITION south VALUES ('ARGENTINA','BRAZIL'));
Table created.

Now we set the “Application Root” to use the “Container Map”:

SQL> ALTER PLUGGABLE DATABASE SET CONTAINER_MAP='C##DGOMEZ.CONTAINERMAP'; 
Pluggable database altered.

Create an application with data

Now we will create an application and we will insert some data. This is just to show a couple of SELECT examples, so that you can see how the data is gotten transparently through the “partitions” (Application PDBs) based on the column “country”.

Start to install the application:

SQL> ALTER PLUGGABLE DATABASE APPLICATION Application_Nuvola BEGIN INSTALL '1.0'; 
Pluggable database altered.

It is not mandatory to use “SHARING=METADATA”. I am using this because all I want to share among the Application PDBs is the metadata (the objects, without data). The data will be physically stored into each Application PDB.

SQL> CREATE TABLE c##dgomez.revenue SHARING=METADATA (
country VARCHAR2(30),
revenue number);
Table created.

The following clauses are mandatory in order to use “Container Maps”:

SQL> ALTER TABLE c##dgomez.revenue ENABLE CONTAINER_MAP;
Table altered.
 
SQL>  ALTER TABLE c##dgomez.revenue ENABLE CONTAINERS_DEFAULT;
Table altered.

And finally, we will end the application installation:

SQL> ALTER PLUGGABLE DATABASE APPLICATION Application_Nuvola END INSTALL '1.0';
Pluggable database altered.

Verifying if the table is enabled to use Container Maps:

 We can double check whether the tables where the data will be stored are enabled to use Container Maps by querying the view DBA_TABLES and its new column “CONTAINER_MAP”:

SQL> select owner, table_name, CONTAINER_MAP from dba_tables where table_name='REVENUE';
OWNER      TABLE_NAME CONTAINER_MAP
---------- ---------- ---------------
C##DGOMEZ  REVENUE    YES

Inserting data to query using Container Map:

In order to complete our example, I will insert some data into each Application PDB. This is only to show how Container Maps work. After there is data inserted, I will proceed to perform a couple of SELECT statements that will automatically use the Container Map (in the next section of this article):

SQL> alter session set container=north;
Session altered.
SQL> ALTER PLUGGABLE DATABASE APPLICATION Application_Nuvola  SYNC;
Pluggable database altered.
SQL> insert into c##dgomez.revenue values ('CANADA',1000);
SQL> insert into c##dgomez.revenue values ('USA',2000);
SQL> commit; 
SQL>  alter session set container=central;
Session altered.
SQL> ALTER PLUGGABLE DATABASE APPLICATION Application_Nuvola SYNC;
Pluggable database altered.
SQL> insert into c##dgomez.revenue values ('GUATEMALA',3000);
SQL> insert into c##dgomez.revenue values ('NICARAGUA',4000);
SQL> commit;
SQL> alter session set container=south;
Session altered.
SQL> ALTER PLUGGABLE DATABASE APPLICATION Application_Nuvola SYNC;
Pluggable database altered. 
SQL> insert into c##dgomez.revenue values ('ARGENTINA',5000);
SQL> insert into c##dgomez.revenue values ('BRAZIL',6000);
SQL> commit;

Executing queries using PDBs as partitions:

Now, time for the magic. I will connect to the “Application Root” and from it I will execute two queries. You can see that the SELECT statements don’t have any filter with the column CON_ID nor the Application PDB name. We are just getting data from a simple table (C##DGOMEZ.REVENUE), but the SELECT statement understands that Container Map is enabled, it will ask in which “partition” (Application PDB) the value “GUATEMALA” is stored and then it will query the table “C##DGOMEZ.REVENUE” in that specific Application PDB.

SQL> alter session set container=nuvola;
Session altered.

SQL> select country, revenue from c##dgomez.revenue where country='GUATEMALA';

COUNTRY        REVENUE
-------------- ----------
GUATEMALA      3000

We can also use the country ‘CANADA” and Oracle will perform the same mechanism:

SQL> select country, revenue from c##dgomez.revenue where country='CANADA';

COUNTRY     REVENUE
----------- ----------
CANADA      1000 

Conclusion

We saw in this article a new, cool concept that combines the CONTAINERS clause, partitioning, and Application Containers. DBAs and developers will be able to take advantage of Container Maps, particularly s for reports that have to get data across several Application PDBs, without having to rewrite the code, and without having to add new clauses in the SELECT statement, taking advantage of Application PDBs as if they were partitions.