Clustering a table in a relational database management system
First Claim
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 by which data rows of the target table are to be ordered within contiguous disk blocks;
wherein at least one of the one or more columns by which data rows of the target table are to be ordered within contiguous disk blocks is a column of a table other than the target table;
wherein the clustering criteria specifies join criteria, the join criteria comprising a foreign key column of the target table and a unique key column of the other table;
in response to receiving a command to load particular data rows into the target table, ordering the particular data rows within contiguous disk blocks based on values in the one or more columns and according to the clustering criteria;
wherein the ordering the particular data rows within contiguous disk blocks based on values in the one or more columns and according to the clustering criteria comprises joining the particular data rows with rows of the other table according to the join criteria; and
wherein the method is performed by one or more computing devices.
1 Assignment
0 Petitions
Accused Products
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.
-
Citations
18 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 by which data rows of the target table are to be ordered within contiguous disk blocks; wherein at least one of the one or more columns by which data rows of the target table are to be ordered within contiguous disk blocks is a column of a table other than the target table; wherein the clustering criteria specifies join criteria, the join criteria comprising a foreign key column of the target table and a unique key column of the other table; in response to receiving a command to load particular data rows into the target table, ordering the particular data rows within contiguous disk blocks based on values in the one or more columns and according to the clustering criteria; wherein the ordering the particular data rows within contiguous disk blocks based on values in the one or more columns and according to the clustering criteria comprises joining the particular data rows with rows of the other table according to the join criteria; and wherein the method is performed by one or more computing devices. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9)
-
-
10. 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 by which data rows of the target table are to be ordered within contiguous disk blocks; wherein at least one of the one or more columns by which data rows of the target table are to be ordered within contiguous disk blocks is a column of a table other than the target table; wherein the clustering criteria specifies join criteria, the join criteria comprising a foreign key column of the target table and a unique key column of the other table; in response to receiving a command to load particular data rows into the target table, ordering the particular data rows within contiguous disk blocks based on values in the one or more columns and according to the clustering criteria; and wherein the ordering the particular data rows within contiguous disk blocks based on values in the one or more columns and according to the clustering criteria comprises joining the particular data rows with rows of the other table according to the join criteria. - View Dependent Claims (11, 12, 13, 14, 15, 16, 17, 18)
-
Specification