Supporting dynamic tables in SQL query compilers

• US 5,930,795 A
• Filed: 01/21/1997
• Issued: 07/27/1999
• Est. Priority Date: 01/21/1997
• Status: Expired due to Term

• Current Assignee: Google LLC (Alphabet Inc.)
• Original Assignee: International Business Machines Corporation
• Inventors: Richey, Jeffrey D., Fuh, Gene Y.C., Chow, Jyh-Herng, Pirahesh, Mir Hamid, Chen, Yao-Ching Stephen, Tran, Brian Thinh-Vinh, Truong, Tuong Chanh, Cochrane, Roberta Jo, Mattos, Nelson Mendonca
• Primary Examiner(s): Kulik, Paul V.
• Assistant Examiner(s): CORRIELUS, JEAN M
• Application Number:
  US08/786,603
• Time in Patent Office: 917 Days
• Field of Search: 395/705, 395/704, 395/185.01, 395/710, 395/701, 707/100
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2448 for particular applications; for extensibility, e.g. user defined types
• CPC Class Codes: G06F 16/24549 Run-time optimisation

Abstract

A framework for a query compiler and run-time environment for resolving a table reference to a dynamic table that is first identified at run-time but is initially unknown at compile-time. A parser parses the table reference and creates a parsed representation for the table that identifies the type of dynamic table. A code generator creates executable plans containing run-time table object representations (TAOB), from the parsed representations, that contain the type of dynamic table. The TAOB is also extended to provide for parameters that are definable at run-time, including a table ID of the actual table entity being referenced. A routine is embedded in a run-time routine that checks the type of dynamic table in the TAOB and dispatches control to one of a plurality of corresponding routines, depending upon the type of dynamic table. The corresponding routines use the extended TAOB to dynamically link the table reference to the dynamic table, depending upon the type of dynamic table. Once the dynamic table has been linked to the table reference, query processing continues as if that table were a base or derived table.

Claims

1. A method, in a database management system, the method comprising:

resolving a table reference to an actual table entity unknown at compile-time (a dynamic table) further comprising;

supporting more than one type of dynamic table including a table function; and

generating a similar table object for each dynamic table, the similar table objects having different values within the table object to differentiate between the types of dynamic table.

2. The method of claim 1 further comprising executing a run-time routine independently of, except for checking for an existence of the dynamic table, the step of supporting more than one type of dynamic table when a dynamic table is not being referenced.

3. The method of claim 2 wherein the step of checking for the existence of the dynamic table is executed only once when a table being referenced is opened.

4. A method, in a database management system, the method comprising:

resolving a table reference to an actual table entity unknown at compile-time (a dynamic table), further comprising;

creating an extended run-time descriptor (extended table object (TAOB)) for the dynamic table; and

dispatching control, during a run-time routine, to dynamically link the table reference, using the extended table object, with an actual table entity.

5. The method of claim 4 wherein the step of dispatching control occurs during an open run-time routine.

6. The method of claim 4 further comprising marking, by a query compiler, in a parsed representation of the table reference, an identification of the type of dynamic table.

7. The method of claim 4 wherein the step of dispatching control further comprises invoking a dynamic table broker routine;

collaborating with a database engine to find the actual table entity to dynamically link the table reference; and

updating table attributes of a template TAOB, created by a code generator from the parsed representation, with information about the actual table entity to record the linkage between the table reference and the actual table entity.

8. The method of claim 4 further comprising a step of continuing query processing, after the table reference is resolved by the dynamic linking, as if the dynamic table became a base table or a derived table.

9. The method of claim 4 wherein the type of dynamic table is a transition table.

10. The method of claim 9 further comprising maintaining a run-time stack, by a creator of an actual transition table entity, and pushing the extended table object onto the stack.

11. The method of claim 10 further comprising looking at the top of the run-time stack, by the routine to which control is dispatched, to access the extended table object to find the table identifier of the actual transition table entity.

12. The method of claim 4 wherein the type of dynamic table is a user-defined temporary table.

13. The method of claim 12 wherein the routine to which control is dispatched performs the following steps:

looking up the actual table entity in a symbol table;

if the actual table entity is found, using the extended table object of the actual table entity to dynamically link the table reference; and

if the actual table entity is not found, creating the actual table entity, entering it into the symbol table for subsequent lookups, and using the extended table object of the created table entity to dynamically link the table reference.

14. The method of claim 4 wherein the type of dynamic table is a table parameter or table result.

15. A method, in a database management system, the method comprising:

resolving a table reference to an actual table entity unknown at compile-time (a dynamic table), further comprising;

including a type of dynamic table in an extended table object representation capable of receiving run-time parameters;

dispatching control, from a run-time routine, to a process depending upon the type of dynamic table identified in the extended table object representation;

including parameters defined at run-time in the extended table object representation including an identity of the actual table entity being referenced; and

using the extended table object representation, in the process dependent upon the type of dynamic table, for dynamically linking the table reference to the actual table entity.

16. A method, in a database management system, the method comprising:

resolving a table reference to an actual table entity unknown at compile-time (a dynamic table), further comprising;

handling, by the query compiler, table functions as a type of a dynamic table;

marking, by a query parser, a parsed representation of the dynamic table to distinguish between types of dynamic tables;

including a type of dynamic table marked in the parsed representation into a template run-time descriptor for the corresponding table reference (an extended table object (TAOB)) created by a code generator;

including parameters defined at run-time in an actual extended TAOB representing the actual table entity including an identity of the actual table entity being referenced;

dispatching control to a process for dynamically linking the table reference to the actual table entity only known at run-time by using the actual extended TAOB; and

continuing with query processing, after the dynamic linking resolves the table reference, as if the underlying table were a base or derived table.

17. The method of claim 16 wherein the step of using the actual extended TAOB further comprises copying information, including the identity of the actual table entity, into the template TAOB to dynamically link the table reference to the actual table entity.

18. A method, in a database management system, comprising:

resolving, at run-time, a table reference to an actual table entity unknown at compile-time (a dynamic table), further comprising;

mapping dynamic tables to table functions and identifying, in a parsed representation of the dynamic tables, the table reference as being unresolved;

extending a run-time descriptor for the table being referenced (table object TAOB)) to include parameters defined at run-time including a table identity of the actual table entity; and

using the extended TAOB at run-time to dynamically link the actual table entity with the table reference.

19. The method of claim 18 further comprising allowing a type attribute of the TAQB to identify a type of dynamic table .

20. The method of claim 19 further comprising passing control, if the type attribute indicates a dynamic table, from an open run-time routine to a process, dependent upon the type of dynamic table, for using the extended TAOB to dynamically link the table reference to the actual table entity.

21. A database management system comprising:

means for resolving a table reference to an actual table entity unknown at compile-time (a dynamic table) further comprising;

means for supporting more than one type of dynamic table including a table function; and

a code generator capable of generating a separate executable plan for each type of dynamic table, each executable plan generating a similar table object for each dynamic table, the similar table objects having different values within the table object to differentiate between the types of dynamic table.

22. The database management system of claim 21 further comprising means for executing a run-time routine independently of, except for having a means for checking for an existence of the dynamic table, the means for supporting more than one type of dynamic table when a dynamic table is not being referenced.

23. The database management system of claim 22 wherein the means for checking for the existence of the dynamic table is activated only once when a table being referenced is opened.

24. A database management system comprising:

a code generator capable of creating a temporary extended table object representation (TAOB) for a table, unknown at compile-time, referenced by a table reference (a dynamic table), having a type attribute capable of identifying the type of dynamic table and having fields capable of having parameters definable at run-time including a table identifier for the actual table entity defined at run-time; and

a dispatcher, embedded in a run-time routine, capable of dispatching control to a process for dynamically linking the table reference to the actual table entity.

25. The database management system of claim 24 further comprising a query compiler capable of identifying a type of the dynamic table in a parsed representation of the dynamic table reference.

26. The database management system of claim 24 further comprising a plurality of routines, a routine for each type of dynamic table, for receiving control from the dispatcher and for carrying out the process of dynamically linking the table reference to the actual table entity.

27. A database management system comprising:

means for resolving a table reference to an actual table entity unknown at compile-time (a dynamic table), the means for resolving further comprising;

means for creating an extended run-time descriptor (extended table object (TAOB)) for the dynamic table; and

means for dispatching control, during a run-time routine, to dynamically link the table reference, using the extended table object, with an actual table entity.

28. The database management system of claim 27 wherein the extended table object (TAOB) further comprises a type of dynamic table, and a capability to have parameters definable at run-time including a table identifier for the actual table entity defined at run-time.

29. The database management system of claim 27 further comprising a query compiler having a capability to identify in a parsed representation of the dynamic table corresponding to the table reference, an identification of the type of the dynamic table.

30. The database management system of claim 29 further comprising means for continuing with query processing, after the dynamic linking resolves the table reference, as if the dynamic table became a base or derived table.

31. Computer programming code residing on at least one computer usable medium for use in a database management system, the programming code comprising:

program code means, in a run-time routine, for dispatching control to a corresponding routine for dynamically linking, at run-time, a table reference to an actual table entity wherein the actual table entity was unknown at compile-time (a dynamic table); and

program code means for performing the dynamic linking, by the corresponding routine, by using an extended table object representation of the actual table entity having extended parameters including a type of the dynamic table and including parameters definable at run-time including a table identifier for the actual table entity.

32. The computer programming code of claim 31 wherein the program code means for dispatching control is resident in an OPEN run-time routine.

33. The computer programming code of claim 32, wherein the program code means for dispatching control is capable of identifying a type of the dynamic table in a template table object representation (TAOB).

34. Computer programming code residing on at least one computer usable medium for use in a database management system, the programming code comprising:

program code means for resolving a table reference to an actual table entity unknown at compile-time (a dynamic table), further comprising;

program code means for causing a marking, by a query compiler, in a parsed representation of the table reference, an identification of a type of dynamic table;

program code means for causing a run-time descriptor (extended table object (TAOB)) for the dynamic table to be extended to include the type of dynamic table and to include parameters defined at run-time including a table identifier for the actual table entity defined at run-time; and

program code means for causing a dispatching of control, during a run-time routine, to a routine to dynamically link the table reference, using the extended table object, with an actual table entity.

35. The programming code of claim 34 wherein the program code means for causing a dispatching of control further comprises program code means for invoking a dynamic table broker routine;

program code means for causing a collaboration with the database engine to find the actual table entity to dynamically link the table reference; and

program code means for causing an update of the table attributes of the TAOB with information about the actual table entity to record the linkage between the table reference and the actual table entity.

36. The programming code of claim 35 wherein the dynamic table broker routine is invoked when a dynamic table is opened for a table operation.

37. The programming code of claim 34 further comprising a plurality of routines, a routine for each type of dynamic table, for receiving control from the dispatcher and for carrying out the process of dynamically linking the table reference to the actual table entity.

38. Computer programming code residing on at least one computer usable medium for use in a database management system, the programming code comprising:

program code means for resolving a table reference to an actual table entity unknown at compile-time (a dynamic table) further comprising;

program code means for supporting more than one type of dynamic table including a table function; and

program code means for generating a similar table object for each dynamic table, the similar table objects having different values within the table object to differentiate between the types of dynamic table.

39. The computer programming code product of claim 38 further comprising executing a run-time routine independently of, except for checking for an existence of the dynamic table, the step of supporting more than one type of dynamic table when a dynamic table is not being referenced.

40. The method of claim 39 wherein the step of checking for the existence of the dynamic table is executed only once when a table being referenced is opened.

41. Computer programming code residing on at least one computer usable medium for use in a database management system, the programming code comprising:

means for resolving a table reference to an actual table entity unknown at compile-time (a dynamic table), further comprising;

means for creating an extended run-time descriptor (extended table object (TAOB)) for the dynamic table; and

means for dispatching control, during a run-time routine, to dynamically link the table reference, using the extended table object, with an actual table entity.