Database Reference
In-Depth Information
Constraints:
The query optimizer takes advantage of
NOT NULL
constraints, unique key
constraints, primary key constraints, foreign key constraints, and some check constraints.
As described later in this chapter, constraints are also central to assessing whether applying
certain query transformations is possible or sensible. In addition, as described in Chapter 13,
constraints also enable additional access paths. For these reasons, it's advisable to create all
known constraints when defining the objects to be stored in the database.
Physical design:
There are three main physical design areas that have an effect on the
query optimizer. First, Oracle Database offers five strategies to store data: heap-organized
tables (this is the default), index-organized tables, external tables, index clusters, and hash
clusters. In addition, heap-organized tables and index-organized tables can be partitioned.
One or several access paths are associated to each strategy. Chapter 13 covers those access
paths in detail. Second, for each data storage strategy, with the exception of external tables,
Oracle Database can manage various types of indexes. Each of the index types (described
in Chapter 13) adds specific access paths. In addition, all storage strategies support
materialized views that, with query rewrite, give the query optimizer additional ways to
optimize queries. This topic is covered in Chapter 15. Third, even though column order in
tables don't impact available access paths, it influences some of the costs computed by the
query optimizer. The reasons behind this are explained in Chapters 7 and 16.
SQL controls:
In most situations, the query optimizer is able to generate optimal execution
plans. But there are cases where it's unable to do so, and Oracle Database provides a
number of features to ameliorate trouble when those cases occur. Chapter 11 covers those
features in detail. For the moment, it's important only to know that features like stored
outlines, SQL profiles, and SQL plan baselines allow you to store in the data dictionary
information that influences the decisions taken by the query optimizer while generating
execution plans.
Execution environment:
A set of initialization parameters controls the behavior of the
query optimizer. Such parameters are set at the system level through the initialization
or server parameter file of the database engine.When required, they can be overridden
at the session level by issuing the
ALTER SESSION
statement. As described in Chapter 11,
some of them can even be changed at the SQL statement level. There are parameters you
can configure at the operating system level server side as well as client side. The National
Language Support (NLS) parameters are one example—they can be configured on both
sides of the connection. In fact, NLS parameters can also be set with environment variables
or, on Windows boxes, through the registry. Especially with the client-side settings, you
must be very careful: it's often forgotten, for client/server applications, that some client-side
environment variables affect the query optimizer. Chapter 9 discusses the most important
initialization parameters that control the behavior of the query optimizer. Some NLS
parameters are covered in Chapter 13.
Bind variables:
Bind variables are fully described in Chapter 2. Besides the value, the
definition (i.e., datatype) of bind variables also has a strong influence on the execution
plans generated by the query optimizer.
Dynamic sampling:
Based on object statistics stored in the data dictionary, the query
optimizer can't always accurately estimate the cost of an operation or predicate. When
the query optimizer recognizes such a case, in some situations it can dynamically gather
additional statistics during query optimization. To do so, the query optimizer executes
recursive queries against the objects referenced by the SQL statement to be optimized. This
feature is described in Chapter 9.
