CLUSTERING A TABLE IN A RELATIONAL DATABASE MANAGEMENT SYSTEM

• US 20140095502A1
• Filed: 09/28/2012
• Published: 04/03/2014
• Est. Priority Date: 09/28/2012
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ziauddin, Mohamed, Witkowski, Andrew
• Granted Patent: US 9,430,550 B2
• Time in Patent Office: Days
• US Class Current: 707/737
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/278 Data partitioning, e.g. horizontal or vertical partitioning
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 16/285 Clustering or classification

Abstract

Techniques are provided that address the problems associated with prior approaches for clustering a fact table in a relational database management system. According to one aspect of the invention, a database server clusters a fact table in a database based on one or more dimension tables. More specifically, rows are stored in the fact table in a sorted order and the order in which the rows are sorted is based on values in one or more columns of one or more of the dimension tables. A user specifies the columns of the dimension tables on which the sorted order is based in “clustering criteria”. The database server uses the clustering criteria to automatically store the rows in the fact table in the sorted order in response to certain user-initiated database operations on the fact-table.

Claims

1. A method for clustering a target table in a relational database management system, the method comprising:

receiving a statement to define the target table;

wherein the statement specifies clustering criteria;

wherein the clustering criteria specifies one or more columns on which an ordering of data rows within the target table is to be based;

wherein at least one of the one or more columns on which the ordering is to be based is a column of a table other than the target table;

in response to receiving a command to load the data rows into the target table, ordering the data rows within the target table based on values in the one or more columns;

wherein the method is performed by one or more computing devices.

2. The method of claim 1, wherein:

the one or more columns include one or more particular columns of one or more particular tables;

the one or more particular tables do not include the target table; and

the method further comprises;

in response to receiving the command to insert the data rows into the target table, transforming the command to a command which, when executed;

performs a left outer join between the target table and the one or more particular tables to produce a set of joined rows,

orders the joined rows based on the values in the one or more particular columns, and

stores the data rows within the target table based on the ordering of the joined rows.

3. The method of claim 1, wherein the clustering criteria specifies that the ordering of the data rows within the target table is to be based on a linear sort order;

and wherein ordering the data rows within the target table based on the values in the one or more columns includes ordering the data rows within the target table in the linear sort order based on the values in the one or more columns.

4. The method of claim 1, wherein the clustering criteria specifies that the ordering of the data rows within the target table is to be based on a interleaved sort order;

and wherein ordering the data rows within the target table based on the values in the one or more columns includes ordering the data rows within the target table in the interleaved sort order based on the values in the one or more columns.

5. The method of claim 4, wherein the interleaved sort order is based on a space-filing curve selected from the group consisting of z-order and Hilbert.

6. The method of claim 1, wherein the clustering criteria specifies join criteria for joining the target table with the other table, the join criteria specifying a foreign key columns of the target table and a unique key columns of the other table.

7. The method of claim 1, wherein the target table is a fact table of a star schema and the other table is a dimension table of the star schema.

8. The method of claim 1, wherein the statement to define the target table is a CREATE TABLE statement having a clustering clause specifying the clustering criteria.

9. The method of claim 1, wherein the clustering criteria specifies when the ordering of data rows within the target table is to be performed.

10. The method of claim 1, wherein the clustering criteria specifies a plurality of columns of a plurality of tables on which an ordering of data rows within the target table is to be based;

and wherein none of the plurality of columns on which the ordering is to be based is a column of the target table; and

in response to receiving a command to load the data rows into the target table, ordering the data rows within the target table based on values in the plurality of columns.

11. One or more non-transitory computer-readable media storing instructions which, when executed by one or more processors, cause performance of a method for clustering a target table in a relational database management system, the method comprising:

receiving a statement to define the target table;

wherein the statement specifies clustering criteria;

wherein the clustering criteria specifies one or more columns on which an ordering of data rows within the target table is to be based;

wherein at least one of the one or more columns on which the ordering is to be based is a column of a table other than the target table;

in response to receiving a command to load the data rows into the target table, ordering the data rows within the target table based on values in the one or more columns;

wherein the method is performed by one or more computing devices.

12. The one or more non-transitory computer-readable media of claim 11, wherein:

the one or more columns include one or more particular columns of one or more particular tables;

the one or more particular tables do not include the target table; and

the method further comprises;

in response to receiving the command to insert the data rows into the target table, transforming the command to a command which, when executed;

performs a left outer join between the target table and the one or more particular tables to produce a set of joined rows,

orders the joined rows based on the values in the one or more particular columns, and

stores the data rows within the target table based on the ordering of the joined rows.

13. The one or more non-transitory computer-readable media of claim 11, wherein the clustering criteria specifies that the ordering of the data rows within the target table is to be based on a linear sort order;

and wherein ordering the data rows within the target table based on the values in the one or more columns includes ordering the data rows within the target table in the linear sort order based on the values in the one or more columns.

14. The one or more non-transitory computer-readable media of claim 11, wherein the clustering criteria specifies that the ordering of the data rows within the target table is to be based on a interleaved sort order;

and wherein ordering the data rows within the target table based on the values in the one or more columns includes ordering the data rows within the target table in the interleaved sort order based on the values in the one or more columns.

15. The one or more non-transitory computer-readable media of claim 14, wherein the interleaved sort order is based on a space-filing curve selected from the group consisting of z-order and Hilbert.

16. The one or more non-transitory computer-readable media of claim 11, wherein the clustering criteria specifies join criteria for joining the target table with the other table, the join criteria specifying a foreign key columns of the target table and a unique key columns of the other table.

17. The one or more non-transitory computer-readable media of claim 11, wherein the target table is a fact table of a star schema and the other table is a dimension table of the star schema.

18. The one or more non-transitory computer-readable media of claim 11, wherein the statement to define the target table is a CREATE TABLE statement having a clustering clause specifying the clustering criteria.

19. The one or more non-transitory computer-readable media of claim 11, wherein the clustering criteria specifies when the ordering of data rows within the target table is to be performed.

20. The one or more non-transitory computer-readable media of claim 11, wherein the clustering criteria specifies a plurality of columns of a plurality of tables on which an ordering of data rows within the target table is to be based;

and wherein none of the plurality of columns on which the ordering is to be based is a column of the target table; and

in response to receiving a command to load the data rows into the target table, ordering the data rows within the target table based on values in the plurality of columns.