Reference data segmentation from single to multiple tables

Patent metadata

Field	Value
Publication number	US-9507837-B2
Application number	US-201213633064-A
Country	US
Kind code	B2
Filing date	Oct 1, 2012
Priority date	Oct 1, 2012
Publication date	Nov 29, 2016
Grant date	Nov 29, 2016

Abstract

Embodiments of the present invention can be used to improve cross reference look-up performance by performing multi-table data segmentation. In accordance with an embodiment, a method of multi-table data segmentation can comprise augmenting each of a plurality of definition files associated with a database table with multi-table data. The method can further comprise creating a plurality of multi-tables. Each multi-table is associated with a different one of the plurality of definition files. The method can further comprise transposing data stored in the database table based on the plurality of definition files, and migrating the transposed data from the database table to the plurality of multi-tables.

First claim

What is claimed is:

1. A method for multi-table data segmentation, the method comprising:
identifying a plurality of definition files associated with a cross-reference table of a database, wherein each of the plurality of definition files defines a mapping from an application-specific format to a common format;
generating a plurality of multi-table definition files by augmenting each of the plurality of definition files to include a multi-table identifier and a multi-table flag;
creating a plurality of multi-tables from the cross-reference table based on the previously generated plurality of multi-table definition files, wherein each multi-table is created using a different one of the plurality of multi-table definition files such that a given one of plurality of multi-table definition files is used to define one or more table structures of a corresponding one of the plurality of multi-tables;
transposing data stored in the cross-reference table to the plurality of multi-tables based on the plurality of multi-table definition files; and
migrating the transposed data from the cross-reference table to the plurality of multi-tables.

2. The method of claim 1 wherein creating a plurality of multi-tables comprises:
parsing each multi-table definition file to determine table information for that multi-table definition file, wherein the table information includes a name, a number of columns, and column names;
automatically generating a plurality of scripts, wherein each script defines one of the multi-tables and wherein each script is generated using the table information parsed from a different one of the multi-table definition files; and
executing each of the plurality of scripts to create the plurality of multi-tables.

3. The method of claim 1 further comprising:
creating a mapping definition table wherein the mapping definition table maps each multi-table definition file to its associated multi-table.

4. The method of claim 1 wherein transposing data stored in the database table to the plurality of multi-tables based on the plurality of multi-table definition files comprises:
generating a transposition script for each of the multi-table definition files, wherein each transposition script defines how the data associated with that multi-table definition file is segmented and transformed to be compatible with that multi-table definition file's associated multi-table.

5. The method of claim 4 wherein migrating the transposed data from the database table to the plurality of multi-tables comprises:
executing each transposition script to segment and transform the data associated with each multi-table definition file; and
copying the segmented and transformed data from the database table to the plurality of multi-tables.

6. The method of claim 1 , further comprising:
validating that the transposed data has been successfully migrated to the plurality of multi-tables.

7. A non-transitory computer readable storage medium including instructions stored thereon which, when executed by a processor, cause the processor to perform the steps of:
identifying a plurality of definition files associated with a cross-reference table of a database, wherein each of the plurality of definition files defines a mapping from an application-specific format to a common format;
generating a plurality of multi-table definition files by augmenting each of the plurality of definition files to include a multi-table identifier and a multi-table flag;
creating a plurality of multi-tables from the cross-reference table based on the previously generated plurality of multi-table definition files, wherein each multi-table is created using a different one of the plurality of multi-table definition files such that a given one of plurality of multi-table definition files is used to define one or more table structures of a corresponding one of the plurality of multi-tables;
transposing data stored in the cross-reference table to the plurality of multi-tables based on the plurality of multi-table definition files; and
migrating the transposed data from the cross-reference table to the plurality of multi-tables.

8. The non-transitory computer readable storage medium of claim 7 wherein the instructions which, when executed by the processor, cause the processor to perform the step of creating a plurality of multi-tables, further comprise instructions which when executed, cause the processor to perform the steps of:
parsing each multi-table definition file to determine table information for that multi-table definition file, wherein the table information includes a name, a number of columns, and column names;
automatically generating a plurality of scripts, wherein each script defines one of the multi-tables and wherein each script is generated using the table information parsed from a different one of the multi-table definition files; and
executing each of the plurality of scripts to create the plurality of multi-tables.

9. The non-transitory computer readable storage medium of claim 7 further comprising instructions which, when executed by the processor, cause the processor to perform the step of:
creating a mapping definition table wherein the mapping definition table maps each multi-table definition file to its associated multi-table.

10. The non-transitory computer readable storage medium of claim 7 wherein the instructions which, when executed by the processor, cause the processor to perform the step of transposing data stored in the database table to the plurality of multi-tables based on the plurality of multi-table definition files further comprise instructions which, when executed, cause the processor to perform the step of:
generating a transposition script for each of the definition files, wherein each transposition script defines how the data associated with that multi-table definition file is segmented and transformed to be compatible with that multi-table definition file's associated multi-table.

11. The non-transitory computer readable storage medium of claim 10 wherein the instructions which, when executed by the processor, cause the processor to perform the step of migrating the transposed data from the database table to the plurality of multi-tables further comprise instructions which, when executed, cause the processor to perform the steps of:
executing each transposition script to segment and transform the data associated with each multi-table definition file; and
copying the segmented and transformed data from the database table to the plurality of multi-tables.

12. The non-transitory computer readable storage medium of claim 7 , further comprising instructions which, when executed by the processor, cause the processor to perform the step of:
validating that the transposed data has been successfully migrated to the plurality of multi-tables.

13. A system for multi-table data segmentation, the system comprising:
a computer, including a non-transitory computer readable storage medium and processor;
a database including a cross-reference data table and a plurality of definition files, wherein each of the plurality of definition files defines a mapping from an application-specific format to a common format;
a multi-table data segmentation tool, executing on the computer, wherein the multi-table data segmentation tool is operable to
identify the plurality of definition files;
generate a plurality of multi-table definition files by augmenting each of the plurality of definition files to include a multi-table identifier and a multi-table flag;
create a plurality of multi-tables from the cross-reference table based on the previously generated plurali

Assignees

• Oracle Int Corp

Inventors

• Fan Irene
• Ng Chor Chung
• Modi Rajan
• Gau Johnny

Classifications

• G06F16/25 Primary

  Integrating or interfacing systems involving database management systems · CPC title

• G06F16/254 Primary

  Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses · CPC title

• G06F17/30557

  Physics · mapped topic

• G06F17/30563 Primary

  Physics · mapped topic