Oracle Database 12cR2 new feature: Container Maps

    May 24, 2017 8:31:00 AM by Deiby Gomez

    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. 

    Tags: Oracle

    Deiby Gomez

    Written by Deiby Gomez

    Deiby Gómez is the youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator