Method for performing complicated schema changes within a database

• US 6,216,136 B1
• Filed: 07/15/1998
• Issued: 04/10/2001
• Est. Priority Date: 07/21/1997
• Status: Expired due to Term

• Current Assignee: Oracle America, Inc. (Oracle Corporation)
• Original Assignee: Telefonaktiebolaget LM Ericsson
• Inventors: Ronström, Ulf Mikael
• Primary Examiner(s): Amsbury, Wayne
• Assistant Examiner(s): PARDO, THUY N
• Application Number:
  US09/115,905
• Time in Patent Office: 1,000 Days
• Field of Search: 707/3, 707/8, 707/101, 707/103, 707/100, 707/203, 707/204, 707/102, 395/710
• US Class Current: 1/1
• CPC Class Codes: G06F 16/213 with details for schema evolution support
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability
• CPC Class Codes: Y10S 707/99954 Version management

Abstract

The present invention relates to a method of performing a schema change within a data-base, where the schema change is a complicated schema change, such as a complicated soft schema change, which results in the compatibility of a first schema version, before the schema change, with a second schema version, after the schema change, or a complicated hard schema change which renders a first schema version, before the schema change, incompatible with a second schema version after the schema change. The execution of a schema change is, according to the method, divided into a number of phases (1, 2, 3, 4, 5). According to one embodiment, a schema change is divided into three phases, a preparatory phase (1), a scanning phase (2), and a terminating phase (3). According to another embodiment, two further phases may be carried out between the scanning phase (2) and the terminating phase (3). These further phases are an evaluating phase (4) and a decision phase (5).

Claims

1. A method of performing in a data-base a schema change, such as a complicated soft schema change where a first schema version prior to the schema change is compatible with a second schema version after the schema change, or such as a complicated hard schema change where a first schema version before said schema change is incompatible with a second schema version after said schema change, comprising the steps of:

dividing the complicated schema change into three phases, of which

a first phase, a preparatory phase, comprises creating conditions which enable transactions according to said second schema version to be commenced while retaining conditions for enabling transactions according to said first schema version to continue, this first phase being a simple schema change;

a second phase, a scan phase, comprises the transmission of data according to said first schema version to data according to said second schema version; and

of which

a third phase, a terminating phase, comprises the removal of those units that shall not be present upon completion of a schema change, which is a simple schema change.

2. A method according to claim 1, where said complicated schema change is a complicated soft schema change, wherein said preparatory phase includes the application of different triggers, keys, foreign keys and/or indexes, whereby said second schema version can be updated with respect to transactions that affect said first schema version, and the application of different triggers, keys, foreign keys and/or indexes whereby said first schema version can be updated with respect to transactions that affect said second schema version.

3. A method according to claim 2, wherein ordinary transactions according to said second schema version are allowed to start immediately upon completion of said data transfer, even though transactions according to said first schema version should still be ongoing.

4. A method according to claim 1, wherein said complicated schema change is a complicated hard schema change, wherein said preparatory phase includes the application of different triggers, keys, foreign keys and/or indexes whereby said second schema version can be updated according to transactions that affect said first schema version, or the application of different triggers, keys, foreign keys, and/or indexes whereby said first schema version can be updated according to transactions that affect said second schema version.

5. A method according to claim 4, wherein ordinary transactions according to said second schema version are allowed to start immediately all transactions according to said first schema version have been completed.

6. A method according to claim 1, wherein said terminating phase is begun after all transactions according to said first schema version are completed.

7. A method according to claim 1, wherein said complicated schema change includes a two further phases;

in that said two phases are positioned between said scan phase and said terminating phase, wherein

a first intermediate phase, an evaluating phase, includes performing certain evaluating transactions according to said second schema version whereby the function of said second schema version can be evaluated; and

wherein

a second intermediate phase, a decision phase, includes deciding whether the schema change shall be completed or broken-off, this decision being based on the result of said third evaluating phase.

8. A method according to claim 2, wherein said evaluating transactions are specially configured control-transactions.

9. A method according to claim 2, wherein said evaluating transactions are ordinary transactions according to said second schema version.

10. A method according to claim 2, wherein ordinary transactions according to said second schema version are allowed to start immediately a decision is reached in said decision phase to complete said schema change even though transactions according to said first schema version are still ongoing.

11. A method according to claim 4, wherein said evaluating transactions are specially configured control-transactions that can be performed according to said second schema version with no detrimental effect on said first schema version.

12. A method according to claim 4, wherein ordinary transactions according to said second schema version are allowed to start immediately a decision is made in said evaluating phase to complete said schema change, and immediately all transactions according to said first schema version have been completed.

13. A method according to claim 7, wherein said terminating phase is begun in accordance with the decision reached in said evaluating phase and, if said decision is to complete said schema change, after all transactions according to said first schema version have been completed.

14. A method according to claim 2, wherein when said schema change is carried out, said terminating phase includes the removal of any triggers, keys, foreign keys, indexes and/or other units that are not used by said second schema version;

and in that when said schema change is aborted, said terminating phase includes the removal of any triggers, keys, foreign keys, indexes and/or other units that are not used by said first schema version.

15. A method according to claim 7, in conjunction with a software change in which present software shall be replaced by new software, or where new software that has no earlier correspondence shall be implemented in said data-base or by a user of said data-base, wherein said schema change is synchronised with said software change;

in that transactions that are generated by said new software constitute said evaluating transactions;

in that evaluation in said evaluating phase includes evaluation of said second schema version, evaluation of said new software, and evaluation of their mutual co-operation; and

that a decision as to whether to implement said new software and to carry out said schema change is made on the basis of the results obtained from said evaluating phase.

16. A method according to claim 1, wherein said schema change includes the storage of information in a SAGA table;

in that said information includes both UNDO-information and REDO-information, where said UNDO-information includes the information required to re-set said first schema version in the event of a schema change being interrupted or aborted in said preparatory phase or said scan phase and possibly in an evaluating phase or a decision phase for some reason or other, and where said REDO-information includes the information required to complete said schema change in the event of said schema change being interrupted or aborted in said terminating phase for some reason or another.

17. A method according to claim 1, wherein said preparatory phase includes the storage of said UNDO-information;

and in that said scan phase includes the storage of said REDO-information.

18. A method according to claim 7, wherein said preparatory phase includes the storage of said UNDO-information;

and in that said evaluating phase includes the storage of said REDO-information.

19. A method according to claim 7, wherein said evaluating transactions are specially configured control-transactions.

20. A method according to claim 7, wherein said evaluating transactions are ordinary transactions according to said second schema version.

21. A method according to claim 7, wherein ordinary transactions according to said second schema version are allowed to start immediately a decision is reached in said decision phase to complete said schema changed even though transactions according to said first schema version are still ongoing.

22. A method according to claim 7, wherein said evaluating transactions are specially configured control-transactions that can be performed according to said second schema version with no detrimental effect on said first schema version.

23. A method according to claim 7, wherein ordinary transactions according to said second schema version are allowed to start immediately a decision is made in said evaluating phase to complete said schema change, and immediately all transactions according to said first schema version have been completed.

24. A method according to claim 4, wherein when said schema change is carried out, said terminating phase includes the removal of any triggers, keys, foreign keys, indexes and/or other units that are not used by said second schema version;

and in that when said schema change is aborted, said terminating phase includes the removal of any triggers, keys, foreign keys, indexes and/or other units that are not used by said first schema version.

25. A method according to claim 7, wherein said schema change includes the storage of information in a SAGA table;

in that said information includes both UNDO-information and REDO-information, where said UNDO-information includes the information required to re-set said first schema version in the event of a schema change being interrupted or aborted in said preparatory phase or said scan phase and possibly in an evaluating phase or a decision phase for some reason or other, and where said REDO-information includes the information required to complete said schema change in the event of said schema change being interrupted or aborted in said terminating phase for some reason or another.

26. A method according to claim 16, wherein said preparatory phase includes the storage of said UNDO-information;

and in that said scan phase includes the storage of said REDO-information.

27. A method according to claim 16, wherein said preparatory phase includes the storage of said UNDO-information;

and in that said evaluating phase includes the storage of said REDO-information.