Automatically determining a database representation for an abstract datatype

• US 7,860,899 B2
• Filed: 03/26/2007
• Issued: 12/28/2010
• Est. Priority Date: 03/26/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Liu, Zhen Hua
• Primary Examiner(s): LeRoux; Etienne P
• Assistant Examiner(s): Le; Hung D
• Application Number:
  US11/728,650
• Publication Number:
  US 20080243916A1
• Time in Patent Office: 1,373 Days
• Field of Search: None
• US Class Current: 707/804
• CPC Class Codes: G06F 16/81 Indexing, e.g. XML tags; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/8365 Query optimisation

Abstract

A method and apparatus for determining a database representation for an abstract datatype that may have more than one database representation. A user supplies a logical description that references the abstract datatype, along with one or more sample queries that reference the abstract datatype. For example, the user supplies a DDL statement to create a table using that abstract datatype as a column, without specifying any database representation for the abstract datatype. Also the user supplies a set of queries that reference the abstract datatype columns. Based on the user information, a database representation for the abstract datatype is determined. A script file containing code to create one or more base structures for the abstract datatype and to create zero or more indexes for the base structures is generated. The script file may be commented to explain to the user why a particular base structure or index was included in the script file.

Claims

1. A method comprising:

receiving a logical description that references an abstract datatype in a database system, wherein the database system supports multiple database representations for the abstract datatype;

receiving one or more sample queries that reference the abstract datatype;

evaluating the one or more sample queries, wherein evaluating one or more sample queries comprises;

determining a plurality of database representations that the database system supports for the abstract datatype;

re-writing the one or more sample queries based on the plurality of database representations;

performing a cost analysis of the re-written sample queries;

based on the logical description and said evaluating said one or more sample queries, determining a database representation for the abstract datatype;

wherein said database representation includes one or more base structures that are used to store data for the abstract datatype;

wherein the method is performed by one or more computing devices.

2. The method of claim 1, further including the step of:

based on determining a database representation for the abstract datatype, generating a code to execute to create the database representation for the abstract datatype.

3. The method of claim 2, further comprising:

generating comments for the code, wherein the comments include a reason why the database representation was determined.

4. The method of claim 1, further including the step of:

based on determining a database representation for the abstract datatype generating a code to execute to create one or more base structures for the abstract datatype.

5. The method of claim 4, further including the step of:

based on determining a database representation for the abstract datatype, generating a code to execute to create one or more indexes for the one or more base structures for the abstract datatype.

6. The method of claim 4,

wherein the database system supports rewriting queries that reference the abstract datatype to instead reference the one or more base structures.

7. The method of claim 6,

wherein the database representation includes one or more indexes for the base structures;

and

wherein the database system supports rewriting queries that reference the abstract datatype to instead reference the one or more indexes.

8. The method of claim 1, further including the step of:

based on the cost analysis, outputting a cost of one or more of the re-written queries.

9. The method of claim 1, wherein determining the database representation for the abstract datatype is further based on a schema for the abstract datatype.

10. A method comprising:

receiving a logical description that references an XML datatype, wherein a database system supports multiple database representations for the XML datatype;

receiving one or more Data Manipulation Language (DML) queries that reference the XML datatype;

evaluating the one or more DML queries, wherein evaluating one or more DML queries comprises;

determining a plurality of database representations that the database system supports for the XML datatype;

re-writing the one or more DML queries based on the plurality of database representations;

performing a cost analysis of the re-written DML queries;

based on the logical description and said evaluating said one or more DML queries, determining a database representation for the XML datatype;

wherein said database representation includes one or more base structures that are used to store data for the XML datatype;

wherein the methodis performed by one or more computing devices.

11. The method of claim 10, further including the step of:

based on determining a database representation for the XML datatype, generating Data Description Language (DDL) code to execute to create one or more base structures for the XML datatype.

12. The method of claim 11, further including the step of:

based on determining a database representation for the XML datatype, generating Data Description Language (DDL) code to execute to create one or more indexes for the one or more base structures.

13. The method of claim 12, further comprising:

generating comments for the DDL code to execute to create one or more indexes for the base structures, wherein the comments include a reason why the database representation was determined.

14. The method of claim 10,

wherein the database system supports rewriting DML queries that reference the datatype to instead reference the one or more base structures.

15. The method of claim 14,

wherein the database representation includes one or more indexes for the base structures;

and

wherein the database system supports rewriting queries that reference the XML datatype to instead reference the one or more indexes.

16. The method of claim 10, further including the step of:

based on the cost analysis, outputting a cost of one or more of the re-written queries.

17. The method of claim 10, wherein determining the database representation for the XML datatype is further based on a schema for the datatype.

18. A computer readable storage medium comprising instructions which, when executed by one or more processors, cause the one of more processors to perform:

receiving a logical description that references an abstract datatype in a database system, wherein the database system supports multiple database representations for the abstract datatype;

receiving one or more sample queries that reference the abstract datatype;

evaluating the one or more sample queries, wherein evaluating one or more sample queries comprises;

determining a plurality of database representations that the database system supports for the abstract datatype;

re-writing the one or more sample queries based on the plurality of database representations;

performing a cost analysis of the re-written sample queries;

based on the logical description and said evaluating said one or more sample queries, determining a database representation for the abstract datatype;

wherein said database representation includes one or more base structures that are used to store data for the abstract datatype.

19. The computer readable storage medium of claim 18, further including instructions for performing:

based on determining a database representation for the abstract datatype, generating a code to execute to create the database representation for the abstract datatype.

20. The computer readable storage medium of claim 19, further comprising instructions for performing:

generating comments for the code, wherein the comments include a reason why the database representation was determined.

21. The computer readable storage medium of claim 18, further including instructions for performing:

based on determining a database representation for the abstract datatype generating a code to execute to create one or more base structures for the abstract datatype.

22. The computer readable storage medium of claim 21, further including instructions for performing:

based on determining a database representation for the abstract datatype, generating a code to execute to create one or more indexes for the one or more base structures for the abstract datatype.

23. The computer readable storage medium of claim 21,

wherein the database system supports rewriting queries that reference the abstract datatype to instead reference the one or more base structures.

24. The computer readable storage medium of claim 23,

wherein the database representation includes one or more indexes for the base structures;

and

wherein the database system supports rewriting queries that reference the abstract datatype to instead reference the one or more indexes.

25. The computer readable storage medium of claim 18, further including instructions for performing:

based on the cost analysis, outputting a cost of one or more of the re-written queries.

26. The computer readable storage medium of claim 18, wherein determining the database representation for the abstract datatype is further based on a schema for the abstract datatype.

27. A computer readable storage medium comprising instructions which, when executed by one or more processors, cause the one of more processors to perform:

receiving a logical description that references an XML datatype, wherein a database system supports multiple database representations for the XML datatype;

receiving one or more Data Manipulation Language (DML) queries that reference the XML datatype;

evaluating the one or more DML queries, wherein evaluating one or more DML queries comprises;

determining a plurality of database representations that the database system supports for the XML datatype;

re-writing the one or more DML queries based on the plurality of database representations;

performing a cost analysis of the re-written DML queries;

based on the logical description and said evaluating said one or more DML queries, determining a database representation for the XML datatype;

wherein said database representation includes one or more base structures that are used to store data for the XML datatype.

28. The computer readable storage medium of claim 27, further including instructions for performing:

based on determining a database representation for the XML datatype, generating Data Description Language (DDL) code to execute to create one or more base structures for the XML datatype.

29. The computer readable storage medium of claim 28, further including instructions for performing:

based on determining a database representation for the XML datatype, generating Data Description Language (DDL) code to execute to create one or more indexes for the one or more base structures.

30. The computer readable storage medium of claim 29, further comprising instructions for performing:

generating comments for the DDL code to execute to create one or more indexes for the base structures, wherein the comments include a reason why the database representation was determined.

31. The computer readable storage medium of claim 27,

wherein the database system supports rewriting DML queries that reference the datatype to instead reference the one or more base structures.

32. The computer readable storage medium of claim 31,

wherein the database representation includes one or more indexes for the base structures;

and

wherein the database system supports rewriting queries that reference the XML datatype to instead reference the one or more indexes.

33. The computer readable storage medium of claim 27, further including instructions for performing:

based on the cost analysis, outputting a cost of one or more of the re-written queries.

34. The computer readable storage medium of claim 27, wherein determining the database representation for the XML datatype is further based on a schema for the datatype.