 |
|
Oracle Concepts by Burleson
Consulting |
Recognizing the Features of SQL
SQL has become the de-facto standard for database
access, and SQL has come a long way since it’s inception in the late
1970’s. In the early 1980’s SQL was lionized as a revolutionary tool
for database access, primary because of its declarative nature and
easy syntax.
Prior to SQL, all database access requires
physical database navigation. In other words, the person issuing the
query would have to specify the order of execution and which indexes
were used to access the data. SQL overcame this shortcoming by
creating an SQL optimizer that creates an execution plan for the SQL.
As we learned in the previous theme SQL has three
simple operations, select, project, and join that allow
us to completely extract data from inside any relational database
table.
Unlike the navigational database languages, SQL
is declarative. By declarative, we mean that the person who writes an
SQL statement does not need to specify the navigation to service the
query. Relational database provide an SQL optimizer to determine the
optimal execution plan for the SQL statement.
The SQL optimizer analyzed the SQL query and goes
to the data dictionary to collect information about the tables that
are specified in the query. Using these statistics, the SQL optimizer
determines the best execution plan to get the requested rows. Some of
the thing that the optimizer does includes:
1 – Determine the order that the tables will be
joined.
2 – Determine the best indexes to use
3 – Determine the
best table join method to use
While the basic format of SQL statements is quite
simple, sophisticated queries can be very complex, using advanced SQL
features such as in-line views, correlated Subqueries, and so on.
Hence, the SQL optimizer has grown into an extremely complex software
package, and the major optimizers (Oracle, DB2) cost many millions of
dollars to develop.
So, if the SQL optimizers cost millions of
dollars, why is SQL tuning necessary? In the real world, there are an
infinite number of possible SQL queries, and it is impossible for any
software package to always generate an optimal execution plan. Hence,
the database vendors create “hints” to allow us to change execution
plans. Oracle, the world’s leading relational database has over 20
different hints for changing the behavior of the SQL optimizer.
It is our challenge as a database professional to
understand the internals procedures for SQL and be able to manage the
parsing and generation of the internal execution plans for the SQL
statements. Once you understand how the SQL optimizer works, you can
quickly identify and correct suboptimal SQL queries.
As we read Chapter 1, pay special attention to
the sections on the goals of SQL tuning and the basic process of SQL
tuning. We will be returning to these guidelines in more detail later
in this course, so it is important that you internalize the basic
goals and techniques.
For more details,
see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
