Transparently upgrading derived database objects

• US 9,665,365 B2
• Filed: 08/11/2015
• Issued: 05/30/2017
• Est. Priority Date: 09/28/2012
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Buzsaki, George, Kumanduri, Murali Krishna, Warren, Mark
• Primary Examiner(s): Chavis, John
• Application Number:
  US14/823,849
• Publication Number:
  US 20150347498A1
• Time in Patent Office: 658 Days
• Field of Search: 707675
• CPC Class Codes: G06F 11/2056 by mirroring
• CPC Class Codes: G06F 11/2058 using more than 2 mirrored copies
• CPC Class Codes: G06F 16/178 Techniques for file synchronisation in file systems
• CPC Class Codes: G06F 16/23 Updating
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/2372 Updates performed during offline database operations
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: G06F 16/245 Query processing
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 8/65 Updates security arrangements therefor G06F21/57
• CPC Class Codes: G06F 8/656 while running
• CPC Class Codes: G06F 8/658 Incremental updates; Differential updates
• CPC Class Codes: G06F 8/71 Version control security arrangements therefor G06F21/57; Configuration management

Abstract

A method for transparently upgrading derived database objects commences upon analysis of the data dictionary definition for an initial derived database object as compared to the data dictionary definition for a patched derived database object, then determining if a regenerated instance of the derived database object would be logically different from the initial derived database object. The determination is based on comparisons of the definitions of the initial derived database objects and patched definitions of respective database objects. The method produces a regenerated instance of the derived database object when a regenerated instance of the patched derived database object would be logically different from the initial derived database object. The method will suppress the production of a regenerated instance when a regenerated instance of the derived database object would not be logically different from the initial derived database object. Once regenerated or suppressed, a post-upgrade software application accesses the regenerated derived database object instance (if regenerated) or the initial derived database object if the regenerated instance was suppressed.

Claims

1. A computer implemented method comprising:

determining if a first materialized view is defined by a first query that only references columns of a database table that exist prior to an upgrade of the database table;

suppressing regeneration of the first materialized view in response to a determination that the first materialized view is defined by the first query that only references columns of the database table that exist prior to the upgrade of the database table;

determining if a second materialized view is defined by a second query that references at least one column that is to be added to the database table by the upgrade of the database table; and

regenerating the second materialized view in response to a determination that the second materialized view is defined by the second query that references the at least one column that is to be added to the database table by the upgrade of the database table.

2. The method of claim 1, further comprising:

accessing an initial instance of the first materialized view prior to application of the upgrade;

accessing an initial instance of the second materialized view prior to application of the upgrade;

accessing the initial instance of the first materialized view following application of the upgrade; and

accessing a regenerated instance of the second materialized view produced by the regenerating of the second materialized view following application of the upgrade.

3. The method of claim 1, wherein regenerating the second materialized view comprises:

regenerating the second materialized view in a patch edition of a database while one or more software applications access a run edition of the database that is to be swapped with the patch edition of the database following completion of a patch that performs the upgrade of the database table.

4. The method of claim 1, further comprising:

reading a stored rule that specifies that materialized views that only reference columns of a database table that exist prior to a patching operation are not to be regenerated in a patch edition that is produced by that patching operation.

5. The method of claim 1, further comprising:

reading a stored rule that specifies that materialized views that reference at least one column of a database table that is added by a patching operation are to be regenerated in a patch edition that is produced by that patching operation.

6. The method of claim 1, further comprising:

preparing a patch edition of a database by including a reference to the first materialized view within a run edition of the database that one or more software applications access during application of a patch to the patch edition;

preparing the patch edition of the database by further including a reference to the second materialized view within the run edition of the database; and

replacing, within the patch edition of the database, the reference to the second materialized view with a regenerated instance of the second materialized view produced by the regenerating of the second materialized view.

7. The method of claim 1, further comprising:

generating a same canonical physical definition for functionally equivalent logical definitions at least by resolving the functionally equivalent logical definitions.

8. The method of claim 7, further comprising:

incorporating first logic from first code objects of the first materialized view into a first logical definition for the first materialized view, without requiring external code references in the first logical definition; and

incorporating second logic from second code objects of the second materialized view into a second logical definition for the second materialized view, without requiring the external code references in the second logical definition.

9. The method of claim 8, further comprising:

generating a first physical definition for the first logical definition at least by resolving first references to one or more first synonyms, a logical table for the database table, or one or more first logical columns to a corresponding physical storage table of the database table or one or more physical columns in the corresponding physical storage table; and

codifying the first physical definition into the same canonical physical definition when the first physical definition is determined to be equivalent to a physical definition of a materialized view generated prior to the upgrade.

17. The non-transitory computer-readable medium of claim 6, wherein the operations further comprise:

incorporating first logic from first code objects of the first materialized view into a first logical definition for the first materialized view, without requiring external code references in the first logical definition; and

incorporating second logic from second code objects of the second materialized view into a second logical definition for the second materialized view, without requiring the external code references in the second logical definition.

18. The non-transitory computer-readable medium of claim 17, wherein the operations further comprise:

generating a first physical definition for the first logical definition at least by resolving first references to one or more first synonyms, a logical table for the database table, or one or more first logical columns to a corresponding physical storage table of the database table or one or more physical columns in the corresponding physical storage table; and

codifying the first physical definition into the same canonical physical definition when the first physical definition is determined to be equivalent to a physical definition of a materialized view generated prior to the upgrade.

10. A non-transitory computer-readable medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform operations comprising:

determining that a first materialized view is defined by a query that only references columns of a database table that exist prior to an upgrade of the database table;

in response to determining that the first materialized view is defined by the query that only references columns of the database table that exist prior to the upgrade of the database table, suppressing regeneration of the first materialized view;

determining that a second materialized view is defined by a query that references at least one column that is to be added to the database table by the upgrade of the database table; and

in response to determining that the second materialized view is defined by the query that references at least one column that is to be added to the database table by the upgrade of the database table, regenerating the second materialized view.

11. The non-transitory computer-readable medium of claim 10, wherein the operations further comprise:

preceding application of the upgrade, accessing an initial instance of the first materialized view;

preceding application of the upgrade, accessing an initial instance of the second materialized view;

following application of the upgrade, accessing the initial instance of the first materialized view; and

following application of the upgrade, accessing a regenerated instance of the second materialized view produced by the regenerating of the second materialized view.

12. The non-transitory computer-readable medium of claim 10, wherein regenerating the second materialized view comprises:

regenerating the second materialized view in a patch edition of a database while one or more software applications access a run edition of the database that is to be swapped with the patch edition of the database following completion of a patch that performs the upgrade of the database table.

13. The non-transitory computer-readable medium of claim 10, wherein the operations further comprise:

reading a stored rule that specifies that materialized views that only reference columns of a database table that exist prior to a patching operation are not to be regenerated in a patch edition that is produced by that patching operation.

14. The non-transitory computer-readable medium of claim 10, wherein the operations further comprise:

reading a stored rule that specifies that materialized views that reference at least one column of a database table that is added by a patching operation are to be regenerated in a patch edition that is produced by that patching operation.

15. The non-transitory computer-readable medium of claim 10, wherein the operations further comprise:

initializing a patch edition of a database to include a reference to the first materialized view within a run edition of the database that one or more software applications access during application of a patch to the patch edition;

initializing the patch edition of the database to include a reference to the second materialized view within the run edition of the database; and

replacing, within the patch edition of the database, the reference to the second materialized view with a regenerated instance of the second materialized view produced by the regenerating of the second materialized view.

16. The non-transitory computer-readable medium of claim 10, wherein the operations further comprise:

generating a same canonical physical definition for functionally equivalent logical definitions at least by resolving the functionally equivalent logical definitions.

19. A computer system comprising:

a computer processor to execute a set of program code instructions; and

a memory to hold the program code instructions, in which the program code instructions comprises program code which, when executed by the computer processor, cause the computer processor to;

determine if a first materialized view is defined by a first query that only references columns of a database table that exist prior to an upgrade of the database table;

suppress regeneration of the first materialized view in response to a determination that the first materialized view is defined by the first query that only references columns of the database table that exist prior to the upgrade of the database table;

determine if a second materialized view is defined by a second query that references at least one column that is to be added to the database table by the upgrade of the database table; and

regenerate the second materialized view in response to a determination that the second materialized view is defined by the second query that references at least one column that is to be added to the database table by the upgrade of the database table.

20. The computer system of claim 19, wherein the program code instructions further comprise program code which, when executed by the computer processor, cause the computer processor to:

access an initial instance of the first materialized view prior to application of the upgrade;

access an initial instance of the second materialized view prior to application of the upgrade;

access the initial instance of the first materialized view following application of the upgrade; and

access a regenerated instance of the second materialized view produced by the regenerating of the second materialized view following application of the upgrade.

21. The computer system of claim 19, wherein the program code instructions, when executed by the computer processor, cause the computer processor to generate the second materialized view further comprises instructions which, when executed by the computer processor, further cause the computer processor to:

regenerate the second materialized view in a patch edition of a database while one or more software applications access a run edition of the database that is to be swapped with the patch edition of the database following completion of a patch that performs the upgrade of the database table.

22. The computer system of claim 19, wherein the program code instructions further comprise program code which, when executed by the computer processor, further cause the computer processor to:

prepare a patch edition of a database by including a reference to the first materialized view within a run edition of the database that one or more software applications access during application of a patch to the patch edition;

prepare the patch edition of the database by further including a reference to the second materialized view within the run edition of the database; and

replacing, within the patch edition of the database, the reference to the second materialized view with a regenerated instance of the second materialized view produced by the regenerating of the second materialized view.

23. The computer system of claim 19, wherein the program code instructions further comprise program code which, when executed by the computer processor, further cause the computer processor to:

generating a same canonical physical definition for functionally equivalent logical definitions at least by resolving the functionally equivalent logical definitions;

incorporating first logic from first code objects of the first materialized view into a first logical definition for the first materialized view, without requiring external code references in the first logical definition;

incorporating second logic from second code objects of the second materialized view into a second logical definition for the second materialized view, without requiring the external code references in the second logical definition;

generating a first physical definition for the first logical definition at least by resolving first references to one or more first synonyms, a logical table for the database table, or one or more first logical columns to a corresponding physical storage table of the database table or one or more physical columns in the corresponding physical storage table; and

codifying the first physical definition into the same canonical physical definition when the first physical definition is determined to be equivalent to a physical definition of a materialized view generated prior to the upgrade.