Research And Realization Of Some Critical Technologies For Interactive Query Optimizer

Database technology is one of the wide application technologies in the domain of computer science. As the indispensable part of database, optimizer has a decisive effect on the performance of database system. There are two commonly used query optimization techniques for the optimizer: Heuristics and Cost-estimation optimization. Unfortunately, these optimization techniques are always not enough to serve the best plan for the database executor in practical application because of their limitations, which may be an obstacle to the effective running of database system.To solve above problems, the interactive technology is applied on database and the interactive optimizer appears, which is composed of the Information Tools and the Hint. Under the interactive condition, when the optimizer makes mistakes, the Database Administrator (DBA) can get the details of the plan by using Information Tools, and then turn on the better plan by applying Hint in order to make up optimizer's mistakes in time and improve system performance efficiently at last.In this paper, we make a study of interactive technology application on the optimizer of relational database. With regard to the three modules of the Information Tools, the function and the structure, along with the detailed design descriptions, have been discussed. As for the Hint, to resolve the problem of precision control on execution plan with table-join Hint, which is caused by the enormous execution plan space when several tables are joined for a query request?and exists in various famous commercial databases abroad, a table-join Hint control model based on the join operator of relational algebra is proposed. In this model, a bijection between table-join Hint and optimizer execution plan is defined to provide maintenance of accuracy for every part of the plan, without any modification of the existing theory of optimizer construction. Furthermore, a conflict-estimation rule on the basis of the control model is presented, which can not only be used to figure out the result of semantic conflict among all sorts of table-join Hints quickly and exactly when the Hints have the different set of table parameters, but also provide the excellent extensibility for the control model. Finally, with Shenzhou Oscar database as the platform, we implement the above key technologies, of which the validity and effectiveness have been testified by relevant experiments.