The database engine's SQL compiler is arguably the most important part of an RDBMS. It must recognize and understand natural language (SQL), then turn the SQL statements into "instructions" it gives to the database engine's retrieval and update processes. The job is compounded by speed requirements.
SQL compilers
process SQL statements in five basic steps. The first step parses the SQL, examines it for syntax errors, then converts the SQL parse tree into an internal representation. The second step examines the reformatted SQL to ensure that executing the statement won't violate referential integrity. This step also notes whether the database engine should process a
constraint or trigger for the SQL.
Next, the SQL compiler rewrites the SQL statement, replacing view references with actual column names and transforming the SQL for processing by the optimizer. The transformation eliminates redundant joins, adds implied predicates, and converts INTERSECT clauses to EXISTS subqueries.
In the fourth step, the optimizer uses cost-based algorithms to determine the most efficient execution method for the SQL statement. It finds the best join order, for example, and it decides whether the execution of the SQL statement will be CPU- or I/O-bound. The optimizer chooses an execution path for the SQL statement that will result in the quickest response from the database engine.
The fifth step "remembers" the essence of the SQL statement for later comparison with other SQL statements; the SQL compiler keeps a history of how well it optimizes statements so it can "learn" the fastest ways to access the database. After this fifth step, the SQL compiler delivers the compil
ed, optimized SQL statement to the retrieval and update processes in the database engine.
illustration_link (3 Kbytes)
An RDBMS's SQL compiler has a central role in making efficient queries.
SQL Compilers: The Performance Nexus
