Method and apparatus for reducing the memory required to store bind variable descriptors in a database

• US 5,802,523 A
• Filed: 06/21/1996
• Issued: 09/01/1998
• Est. Priority Date: 06/21/1996
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Jasuja, Amit, Taranto, Roger
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Alam, Hosain T.
• Application Number:
  US08/669,244
• Time in Patent Office: 802 Days
• Field of Search: 707/103, 707/101, 707/102, 707/10, 395/705, 395/710
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2438 Embedded query languages
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

The present invention discloses a method and apparatus for reducing the memory required to store bind variable descriptors in a database system. In embodiments of the invention, bind variable descriptors continue to be maintained in the shared cursor objects, but bind variables are no longer maintained in the instantiation objects. If the bind variables associated with an instantiation object are accurately described by the bind variable descriptors in a shared object to which the instantiation object is linked, then only data indicating the bind variables associated with the instantiation object are accurately described is maintained in the instantiation object. In contrast, if the bind variable descriptors associated with an instantiation object are not accurately described, only data describing how the attributes of the bind variables associated with the instantiation object differ from those associated with the shared cursor object are maintained in the instantiation object.

Claims

1. A method for executing queries on a server, said method comprising the computer implemented steps of:

storing in a first memory space a first private object for executing a first query from one or a set of clients, wherein said first private object is private to said first query;

storing in a second memory space a first shared object;

storing in said first shared object data describing attributes of a set of bind variables for a particular query;

storing in said first private object data establishing a link to said first shared object;

if differences exist between attributes of a set of bind variables for said first query and attributes of the set of bind variables for said particular query, then storing data in said first private object indicating the differences between attributes of the set of bind variables for said first query and attributes of the set of bind variables for said particular query;

generating compiled code required to execute said particular query;

storing said compiled code in said first shared object; and

executing said first query using the data and the compiled code stored in said first shared object, as well as the data stored in said first private object.

2. The method of claim 1, further comprising the steps of:

deallocating the memory in said second memory space that has stored therein said compiled code;

determining said first query must be reexecuted;

regenerating said compiled code;

storing in said first shared object said compiled code; and

reexecuting said first query using the data and the compiled code stored in said first shared object, as well as the data stored in said first private object.

3. The method of claim 1, wherein:

each bind variable in the set of bind variables for said particular query corresponds to a different bind variable in said set of bind variables for said first query; and

said method further comprises the step of selecting each bind variable in the set of bind variables for said particular query to be at least as long as the corresponding bind variable in the set of bind variables for said first query.

4. The method of claim 1, further comprising the steps of:

storing in said first private object data describing attributes of the set of bind variables for said first query; and

deallocating the memory allocated for said first private object that has stored therein the data describing attributes of the set of bind variables for said first query.

5. The method of claim 1, further comprising the steps of:

storing in a third memory space a second private object for executing a second query from one of said set of clients, wherein said second private object is private to said second query;

determining that the execution of said particular query or said second query can be performed by executing the same compiled code;

storing in said second private object data establishing a link to said first shared object;

if differences exist between attributes of a set of bind variables for said second query and attributes of the set of bind variables for said particular query, then storing data in said second private object indicating the differences between attributes of the set of bind variables for said second query and attributes of the set of bind variables for said particular query; and

executing said second query using the data and the compiled code stored in said first shared object, as well as the data stored in said second private object.

6. The method of claim 5, wherein:

each bind variable in the set of bind variables for said second query corresponds to a different bind variable in the set of bind variables for said particular query; and

said method further comprises the step of;

determining an attribute of a first bind variable in said set of bind variables for said second query differs from said corresponding bind variable in said set of bind variables for said particular query.

7. The method of claim 6, wherein said first bind variable in the set of bind variables for said second query is shorter than said corresponding bind variable in the set of bind variables for said particular query.

8. The method of claim 6, wherein said first bind variable in the set of bind variables for said second query is of a different data type than said corresponding bind variable in the set of bind variables for said particular query.

9. The method of claim 6, wherein said step of executing said second query further comprises the steps of:

receiving data representing a value for said first bind variable in the set of bind variables for said second query;

altering the data representing said value for said first bind variable to match the attributes of said corresponding bind variable in the set of bind variables for said particular query; and

storing the altered data representing said value as part of said second private object prior to executing said second query.

10. The method of claim 5, wherein said first query and said second query are identical.

11. The method of claim 1, further comprising the steps of:

storing in a third memory space a second private object for executing a second query from one of said set of clients, wherein said second private object is private to said second query;

storing in a fourth memory space a second shared object;

storing in said first shared object data describing attributes of a set of bind variables for a new query;

storing in said second private object data establishing a link to said second shared object;

if differences exist between attributes of a set of bind variables for said second query and attributes of the set of bind variables for said new query, then storing data in said second private object indicating the differences between attributes of the set of bind variables for said second query and attributes of the set of bind variables for said new query; and

generating compiled code required to execute said new query;

storing in said second shared object said compiled code required to execute said new query;

executing said second query using the data and the compiled code stored in said second shared object, as well as the data stored in said second private object;

determining that the execution of said first query can be performed by executing the compiled code generated to execute said new query;

storing in said first private object data establishing a link to said second shared object;

if differences exist between attributes of the set of bind variables for said first query and attributes of the set of bind variables for said new query, then storing data in said first private object indicating the differences between attributes of the set of bind variables for said first query and attributes of the set of bind variables for said new query;

deallocating the memory in said second memory space that has stored therein said first shared object; and

executing said first query using the data and the compiled code stored in said second shared object, as well as the data stored in said first private object.

12. The method of claim 1, wherein said first query and said particular query are identical.

13. A method for storing data describing attributes of bind variables for a first query, said method comprising the computer-implemented steps of:

A) receiving a request to create a cursor for said first query, wherein the data accessible using said cursor is the data in a database that satisfies said first query;

B) allocating a first set of one or more memory areas for storing data for said cursor;

D) determining if one of a plurality of objects can be used to execute said first query, wherein each object in said plurality of objects is for storing data and compiled code for executing a query associated with said object, wherein each object of said plurality of objects has stored therein a set of data describing attributes of the bind variables for said query associated with said object; and

E) if one of said plurality of objects can be used to execute said first query, then performing the steps of;

E1) Selecting as a selected object the one of said plurality of objects that can be used to execute said first query;

E2) if differences exist between attributes of the bind variables for said first query and attributes of the bind variables for the query associated with said selected object, then storing data in said first set of one or more memory areas indicating how attributes of the bind variables for said first query differ from attributes of the bind variables for the query associated with said selected object; and

B3) storing in said first set of one or more memory areas data associating said cursor with said selected object.

14. The method of claim 13 further comprises the steps of:

storing in a subset of said first set of one or more memory areas data describing attributes of the bind variables for said first query, wherein the data in said database that satisfies said first query depends upon the values selected for the bind variables for said first query; and

deallocating said subset of said first set of one or more memory areas.

15. The method of claim 13 further comprises the steps of:

F) if one of said plurality of objects cannot be used to execute said first query, then performing the steps of;

F1) adding a new object to said plurality of objects that can be used to execute said first query; and

F2) storing in said first set of one or more memory areas data associating said cursor with said new object.

16. The method of claim 15, wherein:

if one of said plurality of objects cannot be used to execute said first query, then also performing the step of

if differences exist between attributes of the bind variables for said first query and attributes of the bind variables for the query associated with said selected object, then storing data in said first set of one or more memory areas indicating how attributes of the bind variables for said first query differ from attributes of the bind variables for the query associated with said selected object.

17. The method of claim 15, wherein:

said step of adding said new object comprises the steps of;

allocating a second set of one or more memory areas for said new object; and

storing in said second set of one or more memory areas a second set of data describing attributes of the bind variables for said first query;

said method further comprising the steps of;

allocating a third set of one or more memory areas for said new object;

generating compiled code required to execute said first query;

storing said compiled code in said third set of one or more memory spaces; and

executing said compiled code to execute said first query.

18. The method of claim 13, wherein the attributes of one or more of the bind variables for said first query differ from the attributes of the corresponding bind variables for said query associated with said selected object in that the one or more of said bind variables for said first query are shorter.

19. The method of claim 13, wherein the attributes of one or more of the bind variables for said first query differ from the attributes of the corresponding bind variables for said query associated with said selected object in that the one or more of said bind variables for said first query are of a different data type.

20. A method for executing queries on a server, said method comprising the computer implemented steps of:

storing in a first memory space a first private object for executing a first query from one of a set of clients, wherein said first private object is private to said first query;

storing in a second memory space a first shared object;

storing in said first shared object data describing attributes of a set of bind variables for said first query;

storing in said first private object data establishing a link to said first shared object;

generating compiled code required to execute said first query;

storing said compiled code in said first shared object;

executing said first query using the data and the compiled code stored in said first shared object, as well as the data stored in said first private object;

storing in a third memory space a second private object for executing a second query from one of said set of clients, wherein said second private object is private to said second query;

determining that the execution of said first query or said second query can be performed by executing the same compiled code;

storing in said second private object data establishing a link to said first shared object;

if differences exist between attributes of a set of bind variables for said second query and attributes of the set of bind variables for said first query, then storing data in said second private object indicating the differences between attributes of the set of bind variables for said second query and attributes of the set of bind variables for said first query; and

executing said second query using the data and the compiled code stored in said first shared object, as well as the data stored in said second private object.

21. The method of claim 20, further comprising the steps of:

deallocating the memory in said second memory space that has stored therein said compiled code;

determining said first query must be reexecuted;

regenerating said compiled code;

storing in said first shared object said compiled code; and

reexecuting said first query using the data and the compiled code stored in said first shared object, as well as the data stored in said first private object.

22. The method of claim 20, wherein:

each bind variable in the set of bind variables for said second query corresponds to a different bind variable in the set of bind variables for said first query; and

said method further comprises the step of;

determining an attribute of a first bind variable in said set of bind variables for said second query differs from said corresponding bind variable in said set of bind variables for said first query.

23. The method of claim 22, wherein said first bind variable in the set of bind variables for said second query is shorter than said corresponding bind variable in the set of bind variables for said first query.

24. The method of claim 22, wherein said first bind variable in the set of bind variables for said second query is of a different data type than said corresponding bind variable in the set of bind variables for said first query.

25. The method of claim 22, wherein said step of executing said second query further comprises the steps of:

receiving data representing a value for said first bind variable in the set of bind variables for said second query;

altering the data representing said value for said first bind variable to match attributes of said corresponding bind variable in the set of bind variables for said first query; and

storing the altered data representing said value as part of said second private object prior to executing said second query.

26. A database system comprising:

a set of one or more memory devices;

a shared object stored in said set of one or more memory devices;

data storing in said shared object describing attributes of a set of bind variables for a particular query;

compiled code stored in said shared object, said compiled code for executing said particular query;

a first private object stored in said set of one or more memory devices, said first private object being private to a client that requested execution of a first query, said first private object not having stored therein attributes of a set of bind variables for said first query;

data stored in said first private object establishing a link to said shared object; and

a first series of one or more instructions, which when executed by one or more processors, causes said one or more processors to execute said first query using said shared object and said first private object.

27. The database system of claim 26, further comprising:

data stored in said first private object indicating the differences, if any, between attributes of the set of bind variables for said first query and attributes of the set of bind variables for said particular query.

28. The database system of claim 26 further comprising:

a second private object stored in said set of one or more memory devices, said second private object being private to the a client that requested execution of a second query, said private object not having stored therein attributes of a set of bind variables for said second query;

data stored in said second private object establishing a link to said shared object, and

a second series of one or more instructions, which when executed by said one or more processors, causes said one or more processors to execute said second query using said shared object and said second private object.

29. The database system of claim 28, wherein said second query and said particular query are identical.

30. The database system of claim 28, further comprising:

data stored in said second private object indicating whether attributes of the set of bind variables for said second query differ from attributes of the set of bind variables for said particular query.

31. The database system of claim 26, further comprising:

data stored in said second private object indicating the differences, if any, between attributes of the set of bind variables for said second query and attributes of the set of bind variables for said particular query.

32. The database system of claim 28, wherein said client that requested execution of said second query is said client that requested execution of said first query.