IMPLEMENTING A LOGICALLY PARTITIONED DATA WAREHOUSE USING A CONTAINER MAP

• US 20170116278A1
• Filed: 10/21/2016
• Published: 04/27/2017
• Est. Priority Date: 10/23/2015
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Baby, Thomas
• Granted Patent: US 10,628,422 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

In an approach, a database management system logically partitions a database object (such as a table) across multiple pluggable databases of a container database. When a database server receives a query at an application root, the database server consults a container map which provides a mapping between a set of partitioning criteria and a set of member pluggable databases of the application root. Using the container map, the database server identifies one or more pluggable databases of the set of member pluggable databases of the application root that contain records which potentially have the ability to match the predicates of the query. The database server then limits the execution of the query to the identified pluggable databases, effectively pruning away records contained by the other pluggable databases to increase the efficiency of executing the query.

Claims

1. A method comprising:

receiving at a database server a query from a database client during a session with an application root specifying to retrieve one or more records of a database object that match one or more predicates from a plurality of member pluggable databases of the application root, wherein the plurality of member pluggable databases are contained within a container database to which the database server provides access;

the database server identifying one or more particular pluggable databases of the plurality of member pluggable databases that are implicated by the query based on a container map stored within the application root, wherein the container map specifies a mapping between each partitioning criteria of a set of partitioning criteria and a corresponding pluggable database of the plurality of member pluggable databases;

the database server executing the query only on each pluggable database of the one or more particular pluggable databases to produce a respective result set of records from the database object;

the database server aggregating the respective result set of results from each pluggable database of the one or more particular pluggable databases into an aggregated result set for the query;

the database server sending the aggregated result set to the database client.

2. The method of claim 1, wherein the partitioning criteria of the container map is based on one of:

a list partitioning scheme, a range partitioning scheme, a hash partitioning scheme, or a composite partitioning scheme.

3. The method of claim 1, wherein the partitioning criteria is based on a partitioning key representing a value of an attribute within the database object.

4. The method of claim 1, wherein identifying the one or more particular pluggable databases is performed by comparing the one or more predicates of the query to the set of partitioning criteria and determining which of the plurality of member pluggable databases stores records which could potentially match the one or more predicates.

5. The method of claim 1, wherein the query is received by a query coordinator process of the database server which spawns one or more slave processes to execute the query on each pluggable database of the one or more particular pluggable databases in parallel.

6. The method of claim 5, further comprising rewriting the query before providing the query to the one or more slave processes, wherein rewriting the query causes the one or more predicates to be applied by the one or more slave processes in parallel.

7. The method of claim 5, wherein the query requires a join to be performed between the database object and a second database object and further comprising:

in response to a determination that the join is to be performed locally within each pluggable database of the one or more particular pluggable databases, rewriting the query before providing the query to the one or more slave processes, wherein rewriting the query causes the join to be performed by the one or more slave processes in parallel.

8. The method of claim 1, further comprising:

in response to a determination that at least one of the one or more particular pluggable databases is a proxy pluggable database, using information stored within the proxy pluggable database to forward the query to a target pluggable database of the proxy pluggable database that is contained within a second container database and accessible through a second database server.

9. The method of claim 8, wherein the target pluggable database is an application root replica and the second database server, in response to receiving the query, identifies a second one or more particular pluggable databases that are members of the application root replica within the second container database which are implicated by the query, wherein the second one or more particular pluggable databases are identified by consulting a second container map stored by the application root replica that specifies a second mapping between each partitioning criteria of a second set of partitioning criteria and a corresponding pluggable database of the members of the application root replica.

10. The method of claim 9, wherein the second database server executes the query only on each pluggable database of the second one or more particular pluggable databases, aggregates a result of executing the query on each pluggable database of the one or more pluggable databases into a second aggregated result, and sends the second aggregated result back to the database server.

11. The method of claim 8, wherein the query requires a join to be performed between the database object and a second database object and further comprising:

in response to a determination that the second database server applying the join would cause the execution of the query at the second database server to produce fewer records than without applying the join, the database server rewriting the query to cause the join to be performed by the second database server.

12. The method of claim 11, wherein the determination that the second database server applying the join would cause the execution of the query at the second database server to produce fewer records than without applying the join is performed based on one or more statistics related to database objects stored by the target pluggable database that are periodically sent to the database server from the second database server and stored within the proxy pluggable database.

13. A non-transitory computer-readable storage medium storing one or more instructions which, when executed by one or more processors, cause the one or more processors to perform:

receiving at a database server a query from a database client during a session with an application root specifying to retrieve one or more records of a database object that match one or more predicates from a plurality of member pluggable databases of the application root, wherein the plurality of member pluggable databases are contained within a container database to which the database server provides access;

the database server identifying one or more particular pluggable databases of the plurality of member pluggable databases that are implicated by the query based on a container map stored within the application root, wherein the container map specifies a mapping between each partitioning criteria of a set of partitioning criteria and a corresponding pluggable database of the plurality of member pluggable databases;

the database server executing the query only on each pluggable database of the one or more particular pluggable databases to produce a respective result set of records from the database object;

the database server aggregating the respective result set of results from each pluggable database of the one or more particular pluggable databases into an aggregated result set for the query;

the database server sending the aggregated result set to the database client.

14. The non-transitory computer-readable storage medium of claim 13, wherein the partitioning criteria of the container map is based on one of:

a list partitioning scheme, a range partitioning scheme, a hash partitioning scheme, or a composite partitioning scheme.

15. The non-transitory computer-readable storage medium of claim 13, wherein the partitioning criteria is based on a partitioning key representing a value of an attribute within the database object.

16. The non-transitory computer-readable storage medium of claim 13, wherein identifying the one or more particular pluggable databases is performed by comparing the one or more predicates of the query to the set of partitioning criteria and determining which of the plurality of member pluggable databases stores records which could potentially match the one or more predicates.

17. The non-transitory computer-readable storage medium of claim 13, wherein the query is received by a query coordinator process of the database server which spawns one or more slave processes to execute the query on each pluggable database of the one or more particular pluggable databases in parallel.

18. The non-transitory computer-readable storage medium of claim 13, wherein the one or more instructions further cause the one or more processors to perform:

rewriting the query before providing the query to the one or more slave processes, wherein rewriting the query causes the one or more predicates to be applied by the one or more slave processes in parallel.

19. The non-transitory computer-readable storage medium of claim 18, wherein the query requires a join to be performed between the database object and a second database object and the one or more instructions further cause the one or more processors to perform:

in response to a determination that the join is to be performed locally within each pluggable database of the one or more particular pluggable databases, rewriting the query before providing the query to the one or more slave processes, wherein rewriting the query causes the join to be performed by the one or more slave processes in parallel.

20. The non-transitory computer-readable storage medium of claim 13, wherein the one or more instructions further cause the one or more processors to perform:

in response to a determination that at least one of the one or more particular pluggable databases is a proxy pluggable database, using information stored within the proxy pluggable database to forward the query to a target pluggable database of the proxy pluggable database that is contained within a second container database and accessible through a second database server.

21. The non-transitory computer-readable storage medium of claim 20, wherein the target pluggable database is an application root replica and the second database server, in response to receiving the query, identifies a second one or more particular pluggable databases that are members of the application root replica within the second container database which are implicated by the query, wherein the second one or more particular pluggable databases are identified by consulting a second container map stored by the application root replica that specifies a second mapping between each partitioning criteria of a second set of partitioning criteria and a corresponding pluggable database of the members of the application root replica.

22. The non-transitory computer-readable storage medium of claim 21, wherein the second database server executes the query only on each pluggable database of the second one or more particular pluggable databases, aggregates a result of executing the query on each pluggable database of the one or more pluggable databases into a second aggregated result, and sends the second aggregated result back to the database server.

23. The non-transitory computer-readable storage medium of claim 20, wherein the query requires a join to be performed between the database object and a second database object and the one or more instructions further cause the one or more processors to perform:

in response to a determination that the second database server applying the join would cause the execution of the query at the second database server to produce fewer records than without applying the join, the database server rewriting the query to cause the join to be performed by the second database server.

24. The non-transitory computer-readable storage medium of claim 23, wherein the determination that the second database server applying the join would cause the execution of the query at the second database server to produce fewer records than without applying the join is performed based on one or more statistics related to database objects stored by the target pluggable database that are periodically sent to the database server from the second database server and stored within the proxy pluggable database.