The invention will be described below in details with reference to the preferred embodiments and the drawings, wherein:
The main object of the invention aims to eliminate the necessity of customizing hints for each different database platforms. To this end, the application proposes a new solution, that is, providing SQL query-based versatile hint defining means and a hint service adapter (herein after referred to as “the adapter”) serving as an intermediate between the database platforms and the hint defining means, for validating hints defined by the hint defining means on the database platforms and deploying the validated hints on the database platforms.
A different platform needs a different adapter. In an embodiment of the invention, different adapters may be provided for different database platforms and be selected by a DBA according to the database platform in use.
For further facilitating the DBA, a selector may be provided for detecting relevant information of a database platform to determine the type and version of the database platform and thus select an appropriate adapter.
As a more preferred embodiment, the hint defining means 114 of the hint customizing tool 100 as shown in
The table join graph and the default table-joining sequence graph may be displayed visually on a graphic user interface (GUI). As shown in
Configuring means 120 may modify a table-joining sequence graph as shown in
In general, a hint may further contain the following contents in addition to the table-joining sequence:
1. How a table is accessed, for example, it can tell whether index access or r-scan is preferred, which index is to be used, and whether list-prefetch should be used or not, etc.
2. Table-joining method in multi table access.
3. Parallelism such as parallel mode and parallel degree.
These hint contents may be regarded as the properties of a table node or a joining-method node. According to a preferred embodiment, the configuring means 120 may be designed so as to pop out a dialog window as shown in
The joining sequence of multiple tables shown in 2.2 in above table may be determined by directly drag-and-dropping table nodes. However, the sequence property may also be displayed in a dialog box relating to the properties of a node and its value may be modified so that the joining sequence may be changed.
It is to be noted that the properties shown above are just illustrative examples. In practice of the invention, properties may be included as many as those potentially involved in all database platforms. And, when displaying a property dialog box, the property dialog box may be customized according to the type or version of the database platform determined by the selector or appointed by the DBA, so that unnecessary property options may be masked.
To assistant user to define plan hint criteria, the following features may be provided in the GUI:
1. Provide the existing access plan information to help user to decide the hint criteria.
2. Show changeable hints and hide those non-changeable hints.
3. Show local predicates and join predicates on the join graph to help user to decide the hint criteria.
When the configuration of respective nodes has been completed, the hint generating means 122 may generate a hint. That is, the customization information is finally transformed into the internal hint definition criteria which are the abstract of hint definition. Because the internal hint definition criteria is independent of database's platform and hint type. It can be used to generate the hints for any database platform and any hint type automatically. At the same time, it also let user get rid of typos and syntax errors which are mentioned above.
The adapter validates the generated hints in a real database platform environment and finally gives validation report to user. From the report, user can know:
1. Which parts of the hints are used by database optimizer.
2. Which parts of the hints are not used and why they are not used. For these hints, it can also give some advice.
3. What's the difference in the access path.
Finally, the adapter deploys validated hints into the database platform. The deployment means the hints generated in the hint generating means 114 are added into respective database platform in a manner specific to respective database platform.
According to a more preferred embodiment, for reducing the time consumed by the validation performed by the adapter in real database platform environment, those hints that are being defined may be validated in real time with further reference to information about the database platform collected and provided by the adapter. Although such real-time validation could not replace completely the functions of the validation in a real database platform environment, it could realize most of the functions of the latter, and thus remarkably reduce the time consumed in validation.
In the preferred embodiment discussed above, if the relevant information about a database is already known to the hint customization tool according to the invention, for example, if the hint customization tool ever collected relevant information of the database, which has not been changed thereafter, then it is unnecessary for the hint customization tool to be connected to the database. Contrariwise, it is necessary for the database to be connected to the database to obtain relevant information.
A hint customization tool according to the invention has been described above. For understanding the respective components of the hint customization tool, further reference may be made to the following detailed description of the corresponding hint customization method.
A hint customization method corresponding to the hint customization tool described above will be discussed below.
Similarly, as in the hint customization tool as described above, if the relevant information about a database is known, then it is unnecessary to connect the database when performing the method according to the invention. If the case is contrary, then it is necessary to connect to the database firstly to check the information about the platform and version of the database (Step 602 in
Then, in Step 504, the input SQL statement will be analyzed to generate the join graph and the following information will be collected:
With the required information collected correctly, a table join graph and a default table joining sequence graph independent of platform may be constructed.
Then, in Step 506, the user can customize the SQL hints based on the table join graph and default table joining sequence graph. For example, in the intra-table level, he/she can customize table access method, indexes used, etc; in the inter-table level, he/she can define join orders between tables, join method, etc. For specific customization, reference may be made to the description about the configuring means 120.
After user finishes customization of a hint, then a SQL hint is generated (Step 508). SQL hints are sent though the adapter to the database engine side for validation (Step 510). Then a feedback is obtained, such as a SQL hint validation report. The hint validation report may help a user to know the following things:
1. The generated optimization hint according to the user's definition criteria and selected hint type.
2. The difference between the old access plan without using plan hint and the new plan using the generated hint. Visual Plan Hint will highlight them.
3. Which parts in the hints has taken effective.
4. Which parts in the hints are invalid/unused and why those parts are not used and also give the corresponding advices.
If a user does not satisfy the current result according to the feedback, the user may further modify the table joining sequence graph and configure the properties of nodes, so as to re-define the hints and re-validate the hints until the result is satisfying.
If the SQL hints generated from the definition of the user are just what the user wants, then the customized hints may be deployed to the database platform through the adapter (Step 512), so that other applications may use the hints. Since the hint customization process is independent of the specific database platform and hint type, it can be deployed to any database with any hint type. User just needs to define the hints just once.
In a more preferred embodiment, as shown in
The other is platform dependent validation. For those platform dependent SQL hints, they are validated through rule engine-based driven methods. These knowledge-based rules are constructed loaded by the SQL hint service adapter as mentioned above for different platform types. For example, for DB2 for z/OS platform, hybrid join requires the inner table being accessed by index-based list prefetch.
Now preferred embodiments of the hint customization tool and method according to the invention have been described. From above it could be seen that the invention has the following advantages:
1. Independent of specific database platform. User does not need to master the details of hints mechanism for each platform and greatly reduce DBAs' burden.
2. Enables users to define/validate/deploy optimization hints visually and make use of hints easier, provides runtime validation and expert advices and free user from the implied semantic errors and conflicts.
3. Based on graphical interface, user can define much more complex optimization hint.
A person skilled in the art will understand that there are many alternatives to the components and steps described above. Therefore, the protection scope of the invention shall not be limited to the specific details in the description, but shall be construed as encompassing all equivalents.
Number | Date | Country | Kind |
---|---|---|---|
2006-10142365.3 | Oct 2006 | CN | national |