System and method for dynamic database split generation in a massively parallel or distributed database environment

• US 10,394,818 B2
• Filed: 09/24/2015
• Issued: 08/27/2019
• Est. Priority Date: 09/26/2014
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Shivarudraiah, Ashok
• Primary Examiner(s): Mahmoudi, Tony
• Assistant Examiner(s): Dang, Thai V
• Application Number:
  US14/864,765
• Publication Number:
  US 20160092542A1
• Time in Patent Office: 1,433 Days
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations
• CPC Class Codes: G06F 16/278 Data partitioning, e.g. horizontal or vertical partitioning

Abstract

A system and method is described for database split generation in a massively parallel or other distributed database environment including a plurality of databases and a data warehouse layer providing data summarization and querying functionality. A database table accessor of the system obtains, from an associated client application, a query for data in a table of the data warehouse layer, wherein the query includes a user preference. The system obtains table data representative of properties of the table, and determines a splits generator in accordance with one or more of the user preference or the properties of the table. The system generates, by the selected splits generator, table splits dividing the user query into a plurality of query splits, and outputs the plurality of query splits to an associated plurality of mappers for execution by the associated plurality of mappers of each of the plurality of query splits against the table.

Claims

1. A method for dynamic database split generation in a massively parallel or other distributed database environment including a plurality of databases and a data warehouse layer providing querying of the plurality of databases and data summarization of the plurality of databases in a table, the method comprising:

obtaining by a database table accessor executing on one or more microprocessors, from an associated client application, a query for data in the table of the data warehouse layer, the query comprising query data representative of a user query and user splitter kind preference data representative of a user split preference specifying how an associated user would prefer the table to be split for performing the query for data;

obtaining table data representative of one or more properties of the table, the table data comprising table size data representative of a total size of the table;

selecting a splits generator from among an enumeration of splitter kinds in accordance with;

the user split preference when it is determined by the database table accessor that splitting the table using the user split preference would improve a performance of the query for data relative to splitting the table based on the one or more properties of the table, or

the one or more properties of the table when it is determined by the database table accessor that splitting the table based on the one or more properties of the table would improve the performance of the query for data relative to splitting the table based on the user split preference;

generating, by the selected splits generator, table splits dividing the user query into a plurality of query splits; and

outputting the plurality of query splits to an associated plurality of mappers for execution by the associated plurality of mappers of each of the plurality of query splits as tasks of a selected data processing framework against the table.

2. The method of claim 1, wherein the selecting the splits generator comprises selecting the splits generator from among the enumeration of splitter kinds comprising one or more user hints comprising hints on splitting the table based on one or more of partitions, sizes, row limits, and/or ROWID ranges.

3. The method of claim 1, wherein the selecting the splits generator comprises selecting the splits generator in accordance exclusively with the one or more properties of the table.

4. The method of claim 1, wherein the selecting the splits generator comprises selecting the splits generator in accordance exclusively with the user split preference.

5. The method of claim 1, wherein the obtaining the table data comprises obtaining metadata representative of the one or more properties of the table.

6. The method of claim 1, wherein:

the obtaining the table data comprises obtaining table data representative of one or more properties of an external table in a selected data warehouse engine layer.

19. The method of claim 1, wherein:

the database table accessor determines that splitting the table using the user split preference would improve a performance of the query for data relative to splitting the table based on the one or more properties of the table based on a predetermined strategy or heuristic including a plurality of factors comprising one or more of a user hint, a size of the table, whether the table is partitioned, and/or whether splitting the table in a particular way can improve an overall performance of the query for the data, or

the database table accessor determines that splitting the table based on the one or more properties of the table would improve the performance of the query for data relative to splitting the table based on the user split preference based on the predetermined strategy or heuristic.

7. A system for dynamic database split generation in a massively parallel or other distributed database environment including a plurality of databases and a data warehouse layer providing querying of the plurality of databases and data summarization of the plurality of databases in a table, the system comprising:

one or more microprocessors;

a database table accessor running on the one or more microprocessors, wherein the database table accessor operates to perform a method comprising;

obtaining, from an associated client application, a query for data in the table of the data warehouse layer, the query comprising query data representative of a user query and user splitter kind preference data representative of a user split preference specifying how an associated user would prefer the table to be split for performing the query for data;

obtaining table data representative of one or more properties of the table, the table data comprising table size data representative of a total size of the table;

selecting a splits generator from among an enumeration of splitter kinds in accordance with;

the user split preference when it is determined by the database table accessor that splitting the table using the user split preference would improve a performance of the query for data relative to splitting the table based on the one or more properties of the table, or

the one or more properties of the table when it is determined by the database table accessor that splitting the table based on the one or more properties of the table would improve the performance of the query for data relative to splitting the table based on the user split preference;

generating, by the selected splits generator, table splits dividing the user query into a plurality of query splits; and

outputting the plurality of query splits to an associated plurality of mappers for execution by the associated plurality of mappers of each of the plurality of query splits as tasks of a selected data processing framework against the table.

8. The system of claim 7, wherein the selecting the splits generator comprises selecting the splits generator from among the enumeration of splitter kinds comprising one or more user hints comprising hints on splitting the table based on one or more of partitions, sizes, row limits, and/or ROWID ranges.

9. The system of claim 7, wherein the selecting the splits generator comprises selecting the splits generator in accordance exclusively with the one or more properties of the table.

10. The system of claim 7, wherein the selecting the splits generator comprises selecting the splits generator in accordance exclusively with the user split preference.

11. The system of claim 7, wherein the obtaining the table data comprises obtaining metadata representative of the one or more properties of the table.

12. The system of claim 7, wherein:

the obtaining the table data comprises obtaining table data representative of one or more properties of an external table in a selected data warehouse layer.

13. A non-transitory computer readable storage medium, including instructions stored thereon which when read and executed by one or more computers of a database table accessor in a massively parallel or other distributed database environment including a plurality of databases and a data warehouse layer providing querying of the plurality of databases and data summarization of the plurality of databases in a table, cause the one or more computers of the database table accessor to perform a method of dynamic database split generation comprising:

obtaining, from an associated client application, a query for data in the table of the data warehouse layer, the query comprising query data representative of a user query and user splitter kind preference data representative of a user split preference specifying how an associated user would prefer the table to be split for performing the query for data;

obtaining table data representative of one or more properties of the table, the table data comprising table size data representative of a total size of the table;

selecting a splits generator from among an enumeration of splitter kinds in accordance with;

the user split preference when it is determined by the database table accessor that splitting the table using the user split preference would improve a performance of the query for data relative to splitting the table based on the one or more properties of the table, or

the one or more properties of the table when it is determined by the database table accessor that splitting the table based on the one or more properties of the table would improve the performance of the query for data relative to splitting the table based on the user split preference;

generating, by the selected splits generator, table splits dividing the user query into a plurality of query splits; and

outputting the plurality of query splits to an associated plurality of mappers for execution by the associated plurality of mappers of each of the plurality of query splits as tasks of a selected data processing framework against the table.

14. The non-transitory computer readable storage medium of claim 13, wherein the selecting the splits generator comprises selecting the splits generator from among the enumeration of splitter kinds comprising one or more user hints comprising hints on splitting the table based on one or more of partitions, sizes, row limits, and/or ROWID ranges.

15. The non-transitory computer readable storage medium of claim 13, wherein the selecting the splits generator comprises selecting the splits generator in accordance exclusively with the one or more properties of the table.

16. The non-transitory computer readable storage medium of claim 13, wherein the selecting the splits generator comprises selecting the splits generator in accordance exclusively with the user split preference.

17. The non-transitory computer readable storage medium of claim 13, wherein the obtaining the table data comprises obtaining metadata representative of the one or more properties of the table.

18. The non-transitory computer readable storage medium of claim 13, wherein:

the obtaining the table data comprises obtaining table data representative of one or more properties of an external table in a selected data warehouse layer.