Clustering a table in a relational database management system
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.
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.
2. The method of claim 1 , wherein the method further comprises:
in response to receiving the command to load the particular data rows into the target table, transforming the command to a command which, when executed:
uses the join criteria to perform a left outer join between the particular data rows and the rows of the other table to produce a set of joined rows,
orders the joined rows based on values in the one or more columns and according to the clustering criteria, and
orders the particular data rows within contiguous disk blocks based on the ordering of the joined rows.
3. The method of claim 1 , wherein the clustering criteria specifies that data rows of the target table are to be ordered within contiguous disk blocks in a linear sort order; and wherein ordering the particular data rows within contiguous disk blocks based on values in the one or more columns and according to the clustering criteria includes ordering the particular data rows within contiguous disk blocks in the linear sort order based on values in the one or more columns and according to the clustering criteria.
4. The method of claim 1 , wherein the clustering criteria specifies that data rows of the target table are to be ordered within contiguous disk blocks in an interleaved sort order; and wherein ordering the particular data rows within contiguous disk blocks based on values in the one or more columns and according to the clustering criteria includes ordering the particular data rows within contiguous disk blocks in the interleaved sort order based on values in the one or more columns and according to the clustering criteria.
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 target table is a fact table of a star schema and the other table is a dimension table of the star schema.
7. 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.
8. The method of claim 1 , wherein the clustering criteria specifies when data rows of the target table are to be ordered within contiguous disk blocks.
9. The method of claim 1 , wherein the clustering criteria specifies a plurality of columns of a plurality of tables by which data rows of the target table are to be ordered within contiguous disk blocks; and wherein none of the plurality of columns by which data rows of the target table are to be ordered within contiguous disk blocks is a column of the target table; and in response to receiving the command to load the particular data rows into the target table, ordering the particular data rows within contiguous disk blocks based on values in the plurality of columns and according to the clustering criteria.
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.
11. The one or more non-transitory computer-readable media of claim 10 , wherein the method further comprises:
in response to receiving the command to load the particular data rows into the target table, transforming the command to a command which, when executed:
uses the join criteria to perform a left outer join between the particular data rows and the rows of the other table to produce a set of joined rows,
orders the joined rows based on values in the one or more columns and according to the clustering criteria, and
orders the particular data rows within contiguous disk blocks based on the ordering of the joined rows.
12. The one or more non-transitory computer-readable media of claim 10 , wherein the clustering criteria specifies that data rows are to be ordered within contiguous disk blocks in a linear sort order; and wherein ordering the particular data rows within contiguous disk blocks based on values in the one or more columns and according to the clustering criteria includes ordering the particular data rows within contiguous disk blocks in the linear sort order based on values in the one or more columns and according to the clustering criteria.
13. The one or more non-transitory computer-readable media of claim 10 , wherein the clustering criteria specifies that data rows are to be ordered within contiguous disk blocks in an interleaved sort order; and wherein ordering the particular data rows within contiguous disk blocks based on values in the one or more columns and according to the clustering criteria includes ordering the particular data rows within contiguous disk blocks in the interleaved sort order based on values in the one or more columns.
14. The one or more non-transitory computer-readable media of claim 13 , wherein the interleaved sort order is based on a space-filing curve selected from the group consisting of z-order and Hilbert.
15. The one or more non-transitory computer-readable media of claim 10 , wherein the target table is a fact table of a star schema and the other table is a dimension table of the star schema.
16. The one or more non-transitory computer-readable media of claim 10 , wherein the statement to define the target table is a CREATE TABLE statement having a clustering clause specifying the clustering criteria.
17. The one or more non-transitory computer-readable media of claim 10 , wherein the clustering criteria specifies when data rows of the target table are to be ordered within contiguous disk blocks.
18. The one or more non-transitory computer-readable media of claim 10 , wherein the clustering criteria specifies a plurality of columns of a plurality of tables by which data rows of the target table are to be ordered within contiguous disk blocks; and wherein none of the plurality of columns by which data rows of the target table are to be ordered within contiguous disk blocks is a column of the target table; and in response to receiving the command to load the particular data rows into the target table, ordering the particular data rows within contiguous disk blocks based on values in the plurality of columns and according to the clustering criteria.