Validating the Oracle SQL engine.

ABSTRACTThe query engine is the component inside a database system that is responsible for the compilation and execution of every SQL statement submitted by a database user or application. One of the most important steps of query compilation is query optimization. The goal of query optimization is to find the best execution plan based on metadata and statistics about the objects related to the tables referenced in the statement. There are two distinct kinds of query optimization: logical and physical. During logical optimization the structures of the query are changed by applying transformations such as view merging, subquery unnesting, etc. Physical optimization considers the physical properties of the tables (disk layout, access methods, size) and properties of the relational algebra operations (commuting joins) to find the best execution plan for a given query shape as presented by the logical optimization. The changes made to the SQL statement during query optimization can be temporary (to cost a particular optimization) or permanent (after an optimization is considered final). In this paper we discuss the quality problems that occur during the manipulation of the query structures that take place during query compilation with a focus on query optimization. We propose (1) a method to validate query structure change; (2) a method to validate plans that are discarded during query optimization but that may be picked under different circumstances.