Platform-independent method and system for graphically presenting the evaluation of a query in a database management system

• US 6,725,212 B2
• Filed: 08/31/2001
• Issued: 04/20/2004
• Est. Priority Date: 08/31/2001
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Reinwald, Berthold, Lapis, George, Edlund, Stefan Bengt, Lohman, Guy Maring, Couch, Tanya
• Primary Examiner(s): AMSBURY, WAYNE P
• Application Number:
  US09/944,921
• Publication Number:
  US 20030093410A1
• Time in Patent Office: 963 Days
• Field of Search: 707/2, 707/102
• US Class Current: 1/1
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method and system for graphically representing a plan for a query in a relational database management system is disclosed. The method includes receiving and processing an input query to form a plurality of plans, selecting at least one plan of the plurality of plans, and transforming the selected plan into a self-describing formatted file which is platform independent. The method further includes generating a graph representing the selected plan from the self-describing formatted file.

Claims

1. A method for graphically representing a plan for an input query for a relational database management system comprising the steps of:

a) receiving an input query;

b) processing the input query to form a plurality of structured plans;

c) selecting at least one structured plan of the plurality of structured plans;

d) transforming the selected structured plan into a self-describing formatted file, wherein the self-describing formatted file is platform independent; and

e) generating a graph representing the selected structured plan from the self-describing formatted file.

2. The method of claim 1, wherein the self-describing formatted file is an extensible markup language (XML) file.

3. The method of claim 2, wherein the transforming step (d) further includes:

d1) providing a document schema to define the XML file; and

d2) utilizing the document schema to translate the selected plan into the XML file.

4. The method of claim 3, wherein the utilizing step (d2) further includes:

d2i) mapping the selected plan to the XML using the definitions provided in the document schema.

5. The method of claim 4, wherein the document schema is a description type document (DTD).

6. The method of claim 1, wherein the graph generating step (e) further includes:

e1) reading the self-describing formatted file by a self-describing formatted file processor;

e2) creating a plurality of nodes according to the self-describing formatted file; and

e3) creating a plurality of arcs to connect the plurality of nodes according to the self-describing formatted file to form the graphical representation of the selected plan.

7. The method of claim 6, wherein the graph generating step (e) further includes:

e4) collecting detailed information related to each node and associating the related information with each corresponding node, such that the related information is available if requested.

8. The method of claim 1, wherein the relational database management system is a UNIX based system.

9. The method of claim 1, wherein the relational database management system is a WINDOWS based system.

10. The method of claim 1, wherein steps (a)-(d) are performed by the relational database management system.

11. The method of claim 10, wherein step (e) is performed by a client system.

12. The method of claim 11, wherein the client system includes a monitor, the method further including:

(f) displaying the graph on the monitor.

13. The method of claim 1, wherein the selected plan is a best query execution plan.

14. A computer readable medium containing programming instructions for graphically representing a plan for an input query for a relational database management system, the programming instructions for:

a) receiving an input query;

b) processing the input query to form a plurality of structured plans;

c) selecting at least one structured plan of the plurality of structured plans;

d) transforming the selected structured plan into a self-describing formatted file, wherein the self-describing formatted file is platform independent; and

e) generating a graph representing the selected structured plan from the self-describing formatted file.

15. The computer readable medium of claim 14, wherein the self-describing formatted file is an extensible markup language (XML) file.

16. The computer readable medium of claim 15, wherein the transforming instruction (d) further includes:

d1) providing document schema to define the XML file; and

d2) utilizing the document schema to translate the selected plan into the XML file.

17. The computer readable medium of claim 16, wherein the utilizing instruction (d2) further includes:

d2i) mapping the selected plan to the XML using the definitions provided in the document schema.

18. The computer readable medium of claim 17, wherein the document schema is a description type document (DTD).

19. The computer readable medium of claim 14, wherein the graph generating instruction (e) further includes:

e1) reading the self-describing formatted file by a self-describing formatted file processor;

e2) creating a plurality of nodes according to the self-describing formatted file; and

e3) creating a plurality of arcs to connect the plurality of nodes according to the self-describing formatted file to form the graphical representation of the selected plan.

20. The computer readable medium of claim 19, wherein the graph generating instruction (e) further includes:

e4) collecting detailed information related to each node and associating the related information with each corresponding node, such that the related information is available if requested.

21. The computer readable medium of claim 14, wherein the relational database management system is a UNIX based system.

22. The computer readable medium of claim 14, wherein the relational database management system is a WINDOWS based system.

23. The computer readable medium of claim 14, wherein instructions (a)-(d) are performed by the relational database management system.

24. The computer readable medium of claim 23, wherein instruction (e) is performed by a client system.

25. The computer readable medium of claim 24, wherein the client system includes a monitor, the computer readable medium further including instructions for:

(f) displaying the graph on the monitor.

26. The computer readable medium of claim 14, wherein the selected plan is a best query execution plan.

27. A system for graphically representing a plan for an input query to a relational database management system (RDBMS), the system comprising:

a query compiler in the RDBMS for processing the input query and generating a plurality of structured plans for the input query;

means for selecting at least one structured plan of the plurality of structured plans;

a self-describing formatted file generator coupled to the query optimizer for transforming the selected structured plan into a self-describing formatted file, wherein the self-describing formatted file is platform independent; and

means for generating a graph representing the selected plan from the self-describing formatted file.

28. The system of claim 27, wherein the self-describing formatted file is an extensible markup language (XML) file.

29. The system of claim 28, wherein the means for generating the graph includes a Visual Explain module, the Visual Explain module further including an XML file processor coupled to a graph generator.

30. The system of claim 29, wherein the self-describing formatted file generator utilizes a document schema to transform the selected plan into the XML file.

31. The system of claim 30, wherein the XML file processor includes means for processing the XML file and means for instructing the graph generator to graph the XML file.

32. The system of claim 31, wherein the graph generator includes means for creating a plurality of nodes according to instructions from the XML file processor, and means for creating a plurality of arcs to connect the plurality of nodes to form the graphical representation of the selected plan.

33. The system of claim 30, wherein the document schema is a description type document (DTD).

34. The system of claim 27, wherein the relational database management system is a UNIX based system.

35. The system of claim 27, wherein the relational database management system is a WINDOWS based system.

36. The system of claim 27, wherein the system further includes a client system, wherein the client system includes the means for generating a graph.

37. The system of claim 36, wherein the client system includes a monitor coupled to the means for generating the graph for displaying the graph to a user.

38. A method for graphically representing a query execution plan for an input query for a relational database management system comprising the steps of:

a) receiving an input query;

b) compiling the input query to form a structured query execution plan;

c) transforming the structured query execution plan into a file written in Extensible Markup Language (XML), wherein the XML file is platform independent;

d) reading the XML file by an XML file processor;

e) creating a plurality of nodes according to the XML file;

f) collecting detailed information related to each node and associating the related information with each corresponding node, such that the related information is available if requested; and

g) connecting the plurality of nodes with a plurality of arcs to generate a graph representing the query execution plan from the XML file.

39. The method of claim 38, wherein the relational database management system is a UNIX based operating system.

40. The method of claim 38, wherein steps (d)-(g) are performed by a client system.

41. The method of claim 40, wherein the client system includes a monitor, the method further including:

(h) displaying the graph on the monitor.

42. The method of claim 38, wherein the query execution plan is the best query execution plan.