Parsing the SQL Statement
Oracle Tips by Burleson Consulting
When a request is made by a program to
retrieve data columns, Oracle receives the SQL statement and places
it into the library cache areas of the shared pool. Once the
statement is in the shared pool, Oracle invokes the parser to
validate the SQL syntax. The basic purpose of the SQL parse is to
validate the structure of the SQL syntax, verify that the user is
authorized to view the data, and in some cases, reformulate the
query to make it more efficient for the optimizer.
The process of parsing the SQL statement is
illustrated in Figure 3-1.
Figure 1: The parsing
of a SQL statement
While the basic process of parsing SQL is
very straightforward, there are several optimal processes that will
rewrite a SQL statement to make a faster execution plan.
initialization parameter directs Oracle to rewrite data warehouse
queries to prevent resummarization of large volumes of data.
set to FORCE, this initialization parameter directs Oracle to
replace literal values in SQL statements with host variables.
Oracle has recognized that SQL statements can
sometimes be restructured to allow the optimizer to generate a more
favorable execution plan. Let’s take a closer look at these rewrite
Oracle has a special feature called
materialized views that can greatly speed-up data warehouse
queries. In a materialized view, a summary table is created from a
base table, and all queries that perform a similar summation against
the base table will be transparently rewritten to reference the
pre-built summary table.
What follows is a simple example. We begin by
creating a materialized view that sums sales data.
enable query rewrite
Now, when we have any query that summarizes sales,
that query will be dynamically rewritten to reference the summary
alter session set query_rewrite_enabled=true;
set autotrace on
In the execution plan for this query we see that the
sum_sales table is being referenced.
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=83)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SUM_SALES' (Cost=1 Card=423
Note: If you use bind variables in a
query, the query will be not be rewritten to use materialized views
even if you have enabled query rewrite.
Once the query rewrite feature is enabled, you can use
standard SQL hints to force the SQL parser to rewrite the query.
As Oracle SQL evolves and becomes more sophisticated,
there will be more cases where the SQL parser will rewrite queries
into a more efficient form. Next, let’s look at a method for making
literal SQL reusable inside the library cache.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.