Relational database system and method with high availability compliation of SQL programs

• US 5,717,911 A
• Filed: 01/23/1995
• Issued: 02/10/1998
• Est. Priority Date: 01/23/1995
• Status: Expired due to Term

• Current Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Original Assignee: Tandem Computers Incorporated (HP Inc.)
• Inventors: Gilbert, Gary M., Meier, Richard A., Sharma, Sunil, Putzolu, Franco, O'Shea, Thomas J., Madrid, Louise M., Davis, Elizabeth G., Maier, Donald S., Troisi, James H., Zeller, Hansjorg, Celis, Pedro
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): ROBINSON, GRETA LEE
• Application Number:
  US08/377,287
• Time in Patent Office: 1,114 Days
• Field of Search: 395/600, 395/611, 395/612, 395/602, 395/601, 364/282.1, 364/283.4
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

An SQL program executor and an SOL compiler both include options for avoiding recompilations of execution plans whenever the runtime object and the compile time object referenced by an execution plan meet predefined schema similarity criteria. Thus, recompilation is sometimes avoided even though the compile time object has been altered or the run time object is not the same object as the compile time object. To enable late binding, schema information is stored in each execution plan for each object to be accessed by the execution plan. The stored schema information is a subset of the full object schema, and includes information about all the aspects of the objects to be accessed which must remain unchanged in order for the execution plan to be operable. While executing a compiled program, the SQL executor avoids recompilations of execution plans whenever the runtime object and the compile time object referenced by an execution plan meet predefined schema similarity criteria. When a recompilation of an SQL program is required, only the execution plans which do not meet the schema similarity criteria are recompiled if the compiled program has been assigned appropriate runtime parameters. The late binding features of the SQL executor also enable a compiled program to access objects distinct from the objects referenced by the program at compile time. Similarly, the SQL compiler includes the ability to recompile only those execution plans which do not meet the schema similarity criteria and thus are inoperable.

Claims

1. A computer system for storing and providing user access to data in stored objects, comprising:

memory for storing objects, each object having an associated schema denoting information about said object'"'"'s internal structure;

a first source code statement;

a first execution plan, corresponding to said source code statement, for accessing specified ones of said stored objects, said first execution plan including schema information denoting a subset of said schema information for said specified objects at the time said first source code statement was last compiled;

an object management system for executing user commands, including

an object definition module, responsive to object definition commands, for defining and altering said schema for specified ones of said objects;

a compiler for compiling source code statements so as to generate execution plans; and

a compiled plan executer, which executes said execution plans in response to user commands so as to access ones of said objects, said compiled plan executer including;

similarity comparison instructions for performing a predefined similarity test on said schema information for said execution plans and said schemas for said objects to be accessed by said execution plans;

recompiling instructions, executed when said similarity test fails, for recompiling said source code statements; and

plan execution instructions for executing said compiled statements, wherein said plan execution instructions are executed when said similarity test passes, and otherwise after recompilation of said corresponding source code statements.

2. The computer system of claim 1,

each object furthering including a timestamp;

each said execution plan including a timestamp for each object referenced by said each execution plan indicating said object'"'"'s timestamp when the corresponding source code statement was last compiled;

said compiled plan executer including;

timestamp comparison instructions for comparing said timestamps in each execution plan to be executed with corresponding timestamps in said objects referenced by said each execution plan and for executing said recompilation instructions only when said compared timestamps do not match.

3. A computer system for storing and providing user access to data in stored objects, comprising:

memory for storing objects, each object having an associated schema denoting information about said object'"'"'s internal structure;

a source code program including a plurality of source code statements;

a compiled program, corresponding to said source code program, for accessing data within said stored objects, said compiled program including a plurality of compiled statements, each compiled statement including an execution plan for accessing specified ones of said objects, and schema information denoting a subset of said schema information for said specified objects at the time said statement was last compiled;

an object management system for executing user commands, including

an object definition module, responsive to object definition commands, for defining and altering said schema for specified ones of said objects;

a program compiler for compiling said source code program so as to generate said compiled program, said program compiler including a statement compiler for compiling specified individual ones of said source code statements in said program; and

a compiled plan executer, which executes said compiled program in response to user commands so as to access specified ones of said objects, said compiled plan executer including;

similarity comparison instructions for performing a predefined similarity test on said schema information for said compiled statement and said schemas for said specified objects;

recompiling instructions, executed when said similarity test fails, for recompiling said source code statement corresponding to said compiled statement for which said similarity test failed without recompiling other source code statements, in said source code program; and

plan execution instructions for executing said compiled statement, wherein said plan execution instructions are executed when said similarity test passes, and otherwise after recompilation of said corresponding source code statement.

4. The computer system of claim 3,

each object furthering including a timestamp;

each compiled statement including a timestamp for each object referenced by said compiled statement indicating said object'"'"'s timestamp when said statement was last compiled;

said compiled plan executer including;

timestamp comparison instructions for comparing said timestamps in each compiled statement to be executed with corresponding timestamps in said objects referenced by said each compiled statement and for executing said recompilation instructions only when said compared timestamps do not match.

5. A computer system for storing and providing user access to data in stored databases, comprising:

memory for storing said databases, said databases including database tables, each database table having a plurality of columns and an associated schema denoting information about said table'"'"'s columns;

a source code program including a plurality of source code statements;

a compiled program, corresponding to skid source code program, for accessing data within said stored databases, said compiled program including a plurality of compiled statements, each compiled statement including an execution plan for accessing specified ones of said database tables, and schema information denoting a subset of said schema information for said specified database tables at the time said statement was last compiled;

a database management system for executing user commands, including

a table definition module, responsive to database table definition commands, for defining and altering said schema for specified ones of said database tables;

a program compiler for compiling said source code program so as to generate said compiled program, said program compiler including a statement compiler for compiling specified individual ones of said source code statements in said program; and

a compiled plan executer, which executes said compiled program in response to user commands so as to access specified ones of said database tables, said compiled plan executer including;

similarity comparison instructions for performing a predefined similarity test on said schema information for said compiled statement and said schemas for said specified database tables;

recompiling instructions, executed when said similarity test fails, for recompiling said source code statement corresponding to said compiled statement for which said similarity test failed without recompiling other source code statements in said source code program; and

plan execution instructions for executing said compiled statement, wherein said plan execution instructions are executed when said similarity test passes, and otherwise after recompilation of said corresponding source code statement.

6. The computer system of claim 5,

each database table furthering including a timestamp;

each compiled statement including a timestamp for each database table referenced by said compiled statement indicating said database table'"'"'s timestamp when said statement was last compiled;

said compiled plan executer including;

timestamp comparison instructions for comparing said timestamps in each compiled statement to be executed with corresponding timestamps in said database tables referenced by said each compiled statement and for executing said recompilation instructions only when said compared timestamps do not match.

7. A computer system for storing and providing user access to data in stored objects, comprising:

memory for storing objects, each object having an associated timestamp and an associated schema denoting information about said object'"'"'s internal structure;

an object management system for executing user commands, including;

an object definition module, responsive to object definition commands, for defining and altering said schema for specified ones of said objects and for updating each object'"'"'s associate timestamp to indicate when said object was last defined or altered;

a compiler for compiling source code statements so as to generate execution plans;

each execution plan corresponding to one of said source code statements, for accessing a compile-time specified set of said stored objects, each execution plan including schema information denoting a subset of said schema information for said compile-time specified objects at the time said corresponding source code statement was last compiled;

each said execution plan including a timestamp for each object referenced by said each execution plan indicating said object'"'"'s timestamp value when the corresponding source code statement was last compiled; and

a compiled plan executer, which executes said execution plans in response to user commands so as to access a run-time specified set of said objects, said compiled plan executer including;

similarity comparison instructions for performing a predefined similarity test on said schema information for said execution plans and said schemas for said objects to be accessed by said execution plans;

object identity and timestamp comparison instructions for comparing said run-time specified set of objects with said compile-time specified set of objects for each execution plan to be executed, and comparing said timestamps in each execution plan to be executed with the corresponding timestamps in said run-time specified set of objects, and for executing said similarity comparison instructions only when said sets of objects do not match or said compared timestamps do not match;

recompiling instructions, executed when said similarity test fails, for recompiling said source code statements; and

plan execution instructions for executing said compiled statements, wherein said plan execution instructions are executed when said similarity test passes, and otherwise after recompilation of said corresponding source code statements.

8. A method of storing and providing user access to data in stored objects, said method comprising the steps of:

(a) storing in a memory one or more objects, each object having an associated schema denoting information about said object'"'"'s internal structure;

(b) obtaining a source code program including at least one source code statement;

(c) compiling said source code program into a compiled program including at least one compiled statement, each compiled statement including an execution plan for accessing a compile-time specified set of said objects and including compile-time schema information denoting a subset of said schema information for said compile-time specified set of objects;

(d) modifying one or more of said objects so as to modify said schema associated with the modified objects;

(e) specifying a run-time set of objects to use when executing a selected one of said compiled statements;

(f) recompiling said selected compiled statement when said schema information for said selected compiled statement and said schemas for said run-time specified set of objects do not meet predefined similarity criteria.

9. The method of claim 8,

wherein said step (f) is are performed automatically during an initial execution of each compiled statement in said compiled program.

10. The method claim 8,

wherein said step (f) is performed automatically before executing said compiled program.

11. The method of claim 8,

each of said objects including a timestamp;

each of said execution plans including a timestamp for each of said compile-time specified objects referenced by said execution plan indicating each said object'"'"'s timestamp when the corresponding source code statement was last compiled; and

wherein said step (f) is performed, for each execution plan having at least one object whose execution plan timestamp does not match said object'"'"'s timestamp.

12. A method of storing and providing user access to data in stored objects, said method comprising the steps of:

(a) storing in a memory one or more objects, each object having an associated timestamp and an associated schema denoting information about said object'"'"'s internal structure;

(b) obtaining one or more source code statements;

(c) compiling said source code statements into execution plans;

each execution plan corresponding to one of said source code statements, for accessing a compile-time specified set of said stored objects, each execution plan including schema information denoting a subset of said schema information for said compile-time specified objects at the time said corresponding source code statement was last compiled;

each said execution plan including a timestamp for each object referenced by said each execution plan indicating said object'"'"'s timestamp when the corresponding source code statement was last compiled;

(d) defining and altering said schema for specified ones of said objects and for updating each object'"'"'s associate timestamp to indicate when said object was last defined or altered; and

(e) executing said execution plans in response to user commands so as to access a run-time specified set of said objects, including;

comparing said run-time specified set of objects with said compile-time specified set of objects for each execution plan to be executed, and comparing said timestamps in each execution plan to be executed with the corresponding timestamps in said run-time specified set of objects;

when said sets of objects do not match and when said compared timestamps do not match, performing a predefined similarity test on said schema information for said execution plans and said schemas for said objects to be accessed by said execution plans;

when said similarity test fails, recompiling said source code statements to generate updated execution plans; and

executing each execution plan without recompilation of the corresponding source code statement when said similarity test passes, and otherwise executing each execution plan after recompilation of the corresponding source code statement.

13. A method of storing and providing user access to data in stored objects, said method comprising the steps of:

(a) storing in a memory one or more objects, each object having an associated schema denoting information about said object'"'"'s internal structure and having a timestamp;

(b) obtaining a source code program including at least one source code statement;

(c) compiling said source code program to generate a compiled program, including compiling each source code statement in said source code program into a corresponding execution plan for accessing a compile-time specified set of said stored objects, each execution plan including schema information denoting a subset of said schema information for said compile-time specified objects at the time said corresponding source code statement was last compiled;

each said execution plan including a timestamp for each object referenced by said each execution plan indicating said object'"'"'s timestamp when the corresponding source code statement was last compiled;

(d) obtaining a user-recompilation directive for use in directing automatic recompilation of said compiled program, or automatic recompilation of a subset of the source code statements therein;

(e) initiating execution of said compiled program;

(f) when said user-recompilation directive indicates recompilation of said entire source code program if any execution plan therein is invalid,

analyzing said execution plans in said compiled program prior to executing any of said execution plans; and

determining if any of said execution plans contain a timestamp for an object that does not match said object'"'"'s current timestamp, and when such a determination is made, recompiling said source code program to generate a new compiled program prior to executing any of said execution plans therein;

(g) when said user-recompilation directive indicates recompilation of each execution plan in said compiled program that is invalid,

analyzing each said execution plan in said compiled program prior to a first execution of that execution plan; and

determining if said execution plan contains a timestamp for an object that does not match said object'"'"'s current timestamp, and when such a determination is made, recompiling the corresponding source code statement to generate a new execution plan, and then executing said new execution plan; and

(h) when said user-recompilation directive indicates recompilation of each execution plan in said compiled program that is inoperable,

analyzing each said execution plan in said compiled program prior to a first execution of that execution plan; and

determining if said execution plan contains a timestamp for an object that does not match said object'"'"'s current timestamp, and when such a determination is made, performing a predefined similarity test on said schema information for said execution plan and said schemas for said objects to be accessed by said execution plan;

when said similarity test fails for a particular execution plan, recompiling the corresponding source code statement to generate a new execution plan for said corresponding source code statement; and

executing each execution plan in said compiled program without recompilation of the corresponding source code statement when said timestamps match or said similarity test passes, and otherwise executing each execution plan after recompilation of the corresponding source code statement.

14. The method of claim 13,

wherein each of said objects represent a database including one or more database tables.

15. The method of claim 13,

each of said objects further including a similarity check enable flag that when enabled indicates that programs and execution plans referencing said object are eligible for automatic recompilation when execution of said programs and execution plans is initiated, and that when disabled indicates that programs and execution plans referencing said object are not eligible for automatic recompilation when execution of said programs and execution plans is initiated.

16. A method of storing and providing user access to data in stored objects, said method comprising the steps of:

(a) storing in a memory one or more objects, each object having an associated schema denoting information about said object'"'"'s internal structure and having a timestamp;

(b) obtaining a source code program including at least one source code statement;

(c) compiling said source code program to generate a compiled program, including compiling each source code statement in said source code program into a corresponding execution plan for accessing a compile-time specified set of said stored objects, each execution plan including schema information denoting a subset of said schema information for said compile-time specified objects at the time said corresponding source code statement was last compiled;

each said execution plan including a timestamp for each object referenced by said each execution plan indicating said object'"'"'s timestamp when the corresponding source code statement was last compiled;

(d) obtaining a user-recompilation directive for use in directing automatic recompilation of said compiled program, or automatic recompilation of a subset of the source code statements therein;

(e) initiating execution of said compiled program so as to access a run-time specified set of said objects;

(f) when said user-recompilation directive indicates recompilation of said entire source code program if any execution plan therein is invalid,

analyzing said execution plans in said compiled program prior to executing any of said execution plans, including determining whether any of said execution plans contain a timestamp for an object that does not match said object'"'"'s current timestamp, and determining whether for any of said execution plans said run-time specified set of objects does not match said compile-time specified set of objects, and when either determination is made for at least one execution plan in said compiled program, recompiling said source code program to generate a new compiled program prior to executing any of said execution plans therein;

(g) when said user-recompilation directive indicates recompilation of each execution plan in said compiled program that is invalid,

analyzing each said execution plan in said compiled program prior to a first execution of that execution plan, including comparing said run-time specified set of objects with said compile-time specified set of objects for said execution plan, and comparing said timestamps in said execution plan with the corresponding timestamps in said run-time specified set of objects;

and when sets of objects do not match and when said compared timestamps do not match with respect to a particular execution plan, recompiling the corresponding source code statement to generate a new execution plan, and then executing said new execution plan; and

(h) when said user-recompilation directive indicates recompilation of each execution plan in said compiled program that is inoperable,

prior to a first execution of each said execution plan comparing said run-time specified set of objects with said compile-time specified set of objects for said execution plan, and comparing said timestamps in said execution plan with the corresponding timestamps in said run-time specified set of objects;

when said sets of objects do not match and when said compared timestamps do not match, performing a predefined similarity test on said schema information for said execution plan and said schemas for said objects to be accessed by said execution plan;

executing each execution plan in said compiled program without recompilation of the corresponding source code statement when said timestamps match and said run-time and compile-time specified sets of object match or said similarity test passes, and otherwise executing each execution plan after recompilation of the corresponding source code statement.

17. The method of claim 16,

wherein each of said objects represent a database including one or more database tables.

18. The method of claim 16,

each of said objects further including a similarity check enable flag that when enabled indicates that programs and execution plans referencing said object are eligible for automatic recompilation when execution of said programs and execution plans is initiated, and that when disabled indicates that programs and execution plans referencing said object are not eligible for automatic recompilation when execution of said programs and execution plans is initiated.

19. A method for storing and providing user access to data in stored objects, said method comprising the steps of:

(a) storing in a memory one or more objects, each object having an associated schema denoting information about said object'"'"'s internal structure and having a timestamp;

(b) obtaining a source code program including at least one source code statement;

(c) compiling said source code program to generate a compiled program, including compiling each source code statement in said source code program into a corresponding execution plan for accessing a first compile-time specified set of said stored objects, each execution plan including schema information denoting a subset of said schema information for said first compile-time specified objects at the time said corresponding source code statement was last compiled;

each said execution plan including a timestamp for each object referenced by said each execution plan indicating said object'"'"'s timestamp when the corresponding source code statement was last compiled;

(d) obtaining a user-recompilation directive to recompile said compiled program or recompile of a subset of the source code statements therein with respect to a second compile-time specified set of said stored objects;

(e) when said user-recompilation directive indicates recompilation of each execution plan in said compiled program that is invalid, for each said execution plan in said compiled program performing the steps of;

comparing said second compile-time specified set of objects with said first compile-time specified set of objects for said execution plan, and comparing said timestamps in said execution plan with the corresponding timestamps in said second compile-time specified set of objects; and

when said sets of objects do not match and when said compared timestamps do not match, recompiling the corresponding source code statement to generate a new execution plan; and

(h) when said user-recompilation directive indicates recompilation of each execution plan in said compiled program that is inoperable, for each said execution plan in said compiled program performing the steps of;

comparing said second compile-time specified set of objects with said first compile-time specified set of objects for said execution plan, and comparing said timestamps in said execution plan with the corresponding timestamps in said second compile-time specified set of objects;

when said sets of objects do not match and when said compared timestamps do not match, performing a predefined similarity test on said schema information for said execution plan and said schemas for said second compile-time set of objects; and

recompiling said execution plan when said similarity test fails.

20. The method of claim 19,

wherein each of said objects represent a database including one or more database tables.

21. A memory for storing data and programs that access the stored data while being executed by a computer system, the memory comprising:

an object management system for executing user commands, including;

an object definition module, responsive to object definition commands, for defining and altering objects, each object having an associated timestamp and an associated schema denoting information about said object'"'"'s internal structure, said object definition module including instructions for altering said schema for specified ones of said objects and for updating each object'"'"'s associate timestamp to indicate when said object was last defined or altered;

a compiler for compiling source code statements so as to generate execution plans;

each execution plan corresponding to one of said source code statements, for accessing a compile-time specified set of said stored objects, each execution plan including schema information denoting a subset of said schema information for said compile-time specified objects at the time said corresponding source code statement was last compiled; and

a compiled plan executer, which executes said execution plans in response to user commands so as to access a run-time specified set of said objects, said compiled plan executer including;

similarity comparison instructions for performing a predefined similarity test on said schema information for said execution plans and said schemas for said objects to be accessed by said execution plans;

recompiling instructions, executed when said similarity test fails, for recompiling said source code statements; and

plan execution instructions for executing said compiled statements, wherein said plan execution instructions are executed when said similarity test passes, and otherwise after recompilation of said corresponding source code statements.

22. The memory of claim 21,

each said execution plan including a timestamp for each object referenced by said each execution plan indicating said object'"'"'s timestamp when the corresponding source code statement was last compiled;

and

said compiled plan executer including;

object identity and timestamp comparison instructions for comparing said run-time specified set of objects with said compile-time specified set of objects for each execution plan to be executed, and comparing said timestamps in each execution plan to be executed with the corresponding timestamps in said run-time specified set of objects, and for executing said similarity comparison instructions only when said sets of objects do not match or said compared timestamps do not match.

23. The memory of claim 22,

wherein each of said objects represent a database including one or more database tables.

24. The memory of claim 21,

wherein each of said objects represent a database including one or more database tables.