EVALUATING MATERIALIZED VIEWS IN A DATABASE SYSTEM

Abstract

A system and method of evaluating a materialized view relating to a base table for a database system. The method includes the steps of defining a simulated materialized view for the base table within a user session and storing the definition for the simulated materialized view in computer memory such that the definition is available only within the user session. The method further includes the step of maintaining the definition(s) for one or more active materialized views relating to the base table in computer memory such that the definition(s) is/are available outside the user session. An optimizer is invoked to evaluate the simulated materialized view within the user session as if the materialized view was available outside the user session. As an additional step, a simulated materialized view is then selected on detecting a potential improvement of performance.

Description

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is a block diagram of an exemplary large computer system in which the techniques described below are implemented.

FIG. 2 is a block diagram of the parsing engine of the computer system of FIG. 1.

FIG. 3 is a flow chart of the parser of FIG. 2.

Claims

1. A method of evaluating a materialized view relating to a base table for a database system, the method comprising: defining a simulated materialized view for the base table within a user session;storing the definition for the simulated materialized view in computer memory such that the definition is available only within the user session;maintaining the definition(s) for one or more active materialized views relating to the base table in computer memory such that the definition(s) is/are available outside the user session; andinvoking an optimizer to evaluate the simulated materialized view within the user session as if the materialized view was available outside the user session.

2. The method of claim 1 further comprising the step of collecting statistics on the simulated materialized view within the user session.

3. The method of claim 2 further comprising the step of collecting statistics on the simulated materialized view within the user session as if the materialized view was available outside the user session.

4. The method of claim 1 further comprising the step of calculating the cost of a query as if the simulated materialized view was defined for the base table.

5. The method of claim 2 further comprising the step of calculating maintenance costs of updates as if the simulated materialized view was defined for the base table.

6. A method of selecting a materialized view relating to a base table for a database system, comprising: defining a simulated materialized view for the base table within a user session;storing the definition for the simulated materialized view in computer memory such that the definition is available only within the user session;maintaining the definition(s) for one or more active materialized views relating to the base table in computer memory such that the definition(s) is/are available outside the user session;invoking an optimizer to evaluate the simulated materialized view within the user session as if the materialized view was available outside the user session; andselecting the simulated materialized view on detecting a potential improvement of performance.

7. The method of claim 6 further comprising the step of collecting statistics on the simulated materialized view within the user session.

8. The method of claim 7 further comprising the step of collecting statistics on the materialized view within the user session as if the materialized view was available outside the user session.

9. The method of claim 7 further comprising the step of calculating the cost of the query as if the materialized view was defined for the base table.

10. The method of claim 7 further comprising the step of calculating maintenance costs of updates as if the materialized view was defined for the base table.

11. A system for evaluating a materialized view relating to a base table for a database system, the system comprising: an index definition module configured to enable a user to define a simulated materialized view for the base table within a user session;a memory structure maintained in computer memory configured to have stored in it the simulated materialized view such that the definition is available only within the user session;a dictionary structure maintained in computer memory configured to have stored in it one or more active materialized views relating to the base table such that the definition(s) is/are available outside the user session; andan optimizer configured to evaluate the simulated materialized view within the user session, as if the materialized view was available outside the user session.

12. The system of claim 11 further configured to enable a user to collect statistics on the simulated materialized view within the user session.

13. The system of claim 12 wherein the statistics are collected within the user session as if the simulated materialized view was available outside the user session.

14. The system of claim 11 further configured to enable a user to calculate the cost of a query as if the simulated materialized view was defined for the base table.

15. The system of claim 11 further configured to enable a user to calculate maintenance costs of updates as if the simulated materialized view was defined for the base table.

16. A system for selecting a materialized view relating to a base table for a database system, the system comprising: an index definition module configured to enable a user to define a simulated materialized view for the base table within a user session;a memory structure maintained in computer memory configured to have stored in it the simulated materialized view such that the definition is available only within the user session;a dictionary structure maintained in computer memory configured to have stored in it one or more active materialized views relating to the base table such that the definition(s) is/are available outside the user session;an optimizer configured to evaluate the simulated materialized view within the user session as if the materialized view was available outside the user session; anda performance assessment module enabling a user to select the simulated materialized view on detecting a potential improvement of performance.

17. The system of claim 16 further configured to enable a user to collect statistics on the simulated materialized view within the user session.

18. The system of claim 17 further configured to enable a user to collect statistics on the materialized view within the user session as if the materialized view was available outside the user session.

19. The system of claim 16 further configured to calculate the cost of the query as if the materialized view was defined for the base table.

20. The system of claim 16 further configured to calculate maintenance costs of updates as if the materialized view was defined for the base table.

21. A computer program stored on tangible storage media comprising executable instructions for performing a method of evaluating a materialized view relating to a base table for a database system, the method comprising: defining a simulated materialized view for the base table within a user session;storing the definition for the simulated materialized view in computer memory such that the definition is available only within the user session;maintaining the definition(s) for one or more active materialized views relating to the base table in computer memory such that the definition(s) is/are available outside the user session; andinvoking an optimizer to evaluate the simulated materialized view within the user session as if the materialized view was available outside the user session.

22. The computer program of claim 21, the method further comprising the step of collecting statistics on the simulated materialized view within the user session.

23. The computer program of claim 22, the method further comprising the step of collecting statistics on the simulated materialized view within the user session as if the materialized view was available outside the user session.

24. The computer program of claim 21, the method further comprising the step of calculating the cost of a query as if the simulated materialized view was defined for the base table.

25. The computer program of claim 21, the method further comprising the step of calculating maintenance costs of updates as if the simulated materialized view was defined for the base table.

26. A computer program stored on tangible storage media comprising executable instructions for performing a method of selecting a materialized view relating to a base table for a database system, the method comprising: defining a simulated materialized view for the base table within a user session;storing the definition for the simulated materialized view in computer memory such that the definition is available only within the user session;maintaining the definition(s) for one or more active materialized views relating to the base table in computer memory such that the definition(s) is/are available outside the user session;invoking an optimizer to evaluate the simulated materialized view within the user session as if the materialized view was available outside the user session; andselecting the simulated materialized view on detecting a potential improvement of performance.

27. The computer program of claim 26, the method further comprising the step of collecting statistics on the simulated materialized view within the user session.

28. The computer program of claim 27, the method further comprising the step of collecting statistics on the materialized view within the user session as if the materialized view was available outside the user session.

29. The computer program of claim 26, the method further comprising the step of calculating the cost of the query as if the materialized view was defined for the base table.

30. The computer program of claim 26, the method further comprising the step of calculating maintenance costs of updates as if the materialized view was defined for the base table.