Combining row based and column based tables to form mixed-mode tables
Various ways of storing data in tabular form such that some records are stored in a row oriented manner and some data is stored in a column oriented manner. The records respectively stored in the row and column oriented manners may, or may not, overlap (in whole or in part. Some embodiments accomplish this dual orientation of data by: (i) using a range partition; (ii) using a union all view command; and/or (iii) using a first table, a staging table and a materialized query table.
1. A method for storing data in tabular form, the method comprising:
creating a table with a set of one, or more, partitions such that the table includes at least a first partition range and a second partition range;
populating the first partition range with a first data set that includes a first plurality of records, with each record of the first plurality of records having a plurality of attribute values corresponding to a plurality of ordered attributes;
populating the second partition range with a second data set that includes a second plurality of records, with each record of the second plurality of records having a plurality of attribute values corresponding to a plurality of ordered attributes; and
storing the table as a machine readable data structure on a non-transitory storage medium of a machine readable storage device by transforming matter of a storage medium of a machine readable data storage device to correspond to the table data structure;
wherein:
the plurality of ordered attributes of the first plurality of records has the same attributes and the same order as the plurality of ordered attributes of the second plurality of records;
the first plurality of records is stored on the storage medium of the machine readable storage device in a row oriented manner; and
the second plurality of records is stored on the storage medium of the machine readable storage device in a column oriented manner;
whereby selective use two different orientations for the first and second pluralities of records improves, relative to a single orientation table, a balance between the following design imperatives: speed of stored data access and storage space efficiency.
2. The method of claim 1 wherein:
no record of the first plurality of records has an identical set of attribute values as any record of the second plurality of records.
3. The method of claim 1 wherein the creation of the table and the population of the first and second partition ranges is controlled by relational database management system software.
4. The method of 1 wherein:
the first plurality of records include records most likely to be revised with respect to their attribute records and/or augmented with additional records.
5. The method of 1 wherein:
the second plurality of records include attribute values most likely to be queried.
6. The method of claim 1 wherein the records of the first plurality of records is more recent than the records of the second plurality of records.
7. The method of claim 1 wherein:
the table is optimized for point queries and updates that add or change only a small amount of data because the first plurality of records is stored on the storage medium in a row oriented manner; and
the table is optimized for running business intelligence queries faster because the second plurality of records is stored in a column oriented manner.
8. A method for storing data in tabular form, the method comprising:
creating a first table and a second table;
populating the first table with a first data set that includes a first plurality of records, with each record of the first plurality of records having a plurality of attribute values corresponding to a plurality of ordered attributes;
populating the second table with a second data set that includes a second plurality of records, with each record of the second plurality of records having a plurality of attribute values corresponding to a plurality of ordered attributes;
in response to a union all view command, creating a third table that includes both the first plurality of records and the second plurality of records; and
storing the third table as a machine readable data structure on a non-transitory storage medium of a machine readable storage device by transforming matter of a storage medium of a machine readable data storage device to correspond to the table data structure;
wherein:
the plurality of ordered attributes of the first plurality of records has the same attributes and the same order as the plurality of ordered attributes of the second plurality of records;
the first plurality of records is stored on the storage medium of the machine readable storage device in a row oriented manner in both the first table and the third table; and
the second plurality of records stored on the storage medium of the machine readable storage device in a column oriented manner in both the second table and the third table
whereby selective use two different orientations for the first and second pluralities of records improves, relative to a single orientation set of tables, a balance between the following design imperatives: speed of stored data access and storage space efficiency.
9. The method of claim 8 wherein:
no record of the first plurality of records has an identical set of attribute values as any record of the second plurality of records.
10. The method of claim 8 wherein the creation of the first, second and third tables is controlled by relational database management system software.
11. The method of 8 wherein:
the first plurality of records include records most likely to be revised with respect to their attribute records and/or augmented with additional records.
12. The method of 8 wherein:
the second plurality of records include attribute values most likely to be queried.
13. The method of claim 8 wherein the records of the first plurality of records is more recent than the records of the second plurality of records.
14. The method of claim 8 wherein:
the third table is optimized for point queries and updates that add or change only a small amount of data because the first plurality of records is stored on the storage medium in a row oriented manner; and
the third table is optimized for running business intelligence queries faster because the second plurality of records is stored in a column oriented manner.