Automatically determining a database representation for an abstract datatype

• US 20080243916A1
• Filed: 03/26/2007
• Published: 10/02/2008
• 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
• Granted Patent: US 7,860,899 B2
• Time in Patent Office: Days
• US Class Current: 707/103Z
• 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; and

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

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 representation includes one or more base structures;

and

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, wherein determining a database representation includes evaluating the one or more sample queries against one or more of the database representations that the database system supports for the abstract datatype.

9. The method of claim 1, wherein determining a database representation 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; and

basing the determination of the database representation on the cost analysis.

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

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

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

12. A method comprising:

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

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

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

13. The method of claim 12, 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.

14. The method of claim 13, 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.

15. The method of claim 14, 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.

16. The method of claim 12,

wherein the database representation includes one or more base structures;

and

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

17. The method of claim 16,

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.

18. The method of claim 12, wherein determining a database representation includes evaluating the one or more DML queries against one or more of the database representations that the database system supports for the XML datatype.

19. The method of claim 12, wherein determining a database representation 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; and

basing the determination of the database representation on the cost analysis.

20. The method of claim 19, further including the step of:

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

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