2012-10-12 11gR2 concepts page 129 - 160

Joins -- 连接
Join types include the following:
 -- Inner joins -- 内连接
An inner join is a join of two or more tables that returns only rows that satisfy the
join condition. For example, if the join condition is
employees.department_id=departments.department_id, then rows that
do not satisfy this condition are not returned.
 -- Outer joins -- 外连接
An outer join returns all rows that satisfy the join condition and also returns rows
from one table for which no rows from the other table satisfy the condition. For
example, a left outer join of employees and departments retrieves all rows in
the employees table even if there is no match in departments. A right outer
join retrieves all rows in departments even if there is no match in employees.
 -- Cartesian products -- 笛卡尔积
If two tables in a join query have no join condition, then the database returns their
Cartesian product. Each row of one table combines with each row of the other. For
example, if employees has 107 rows and departments has 27, then the
Cartesian product contains 107*27 rows. A Cartesian product is rarely useful.

Use of the Optimizer -- 优化器的使用
For any SQL statement processed by
Oracle, the optimizer performs the following operations:
--Evaluation of expressions and conditions
-- Inspection of integrity constraints to learn more about the data and optimize based
on this metadata
-- Statement transformation
-- Choice of optimizer goals
-- Choice of access paths
-- Choice of join orders
The optimizer generates most of the possible ways of processing a query and assigns a
cost to each step in the generated execution plan. The plan with the lowest cost is
chosen as the query plan to be executed.

For example, you may set the optimizer goal
to either of the following:
-- Total throughput -- 吞吐量优先
The ALL_ROWS hint instructs the optimizer to get the last row of the result to the
client application as fast as possible.
-- Initial response time -- 响应优先
The FIRST_ROWS hint instructs the optimizer to get the first row to the client as
fast as possible.

The
optimizer performs the following operations:
1. The optimizer receives the parsed query and generates a set of potential plans for
the SQL statement based on available access paths and hints.
2. The optimizer estimates the cost of each plan based on statistics in the data
dictionary. The cost is an estimated value proportional to the expected resource
use needed to execute the statement with a particular plan.
3. The optimizer compares the costs of plans and chooses the lowest-cost plan,
known as the query plan, to pass to the row source generator (see "SQL Row
Source Generation" on page 7-19).

 

Access Paths -- 访问路径
The database can use several different access paths to retrieve data from a table. The
following is a representative list:
 -- Full table scans -- 全表扫描
This type of scan reads all rows from a table and filters out those that do not meet
the selection criteria. The database sequentially scans all data blocks in the
segment, including those under the high water mark that separates used from
unused space (see "Segment Space and the High Water Mark" on page 12-27).
 -- Rowid scans -- 通过ROWID的扫描
The rowid of a row specifies the data file and data block containing the row and
the location of the row in that block. The database first obtains the rowids of the
selected rows, either from the statement WHERE clause or through an index scan,
and then locates each selected row based on its rowid.
 -- Index scans -- 索引
This scan searches an index for the indexed column values accessed by the SQL
statement (see "Index Scans" on page 3-6). If the statement accesses only columns
of the index, then Oracle Database reads the indexed column values directly from
the index.
 -- Cluster scans
A cluster scan is used to retrieve data from a table stored in an indexed table
cluster, where all rows with the same cluster key value are stored in the same data
block (see "Overview of Indexed Clusters" on page 2-21). The database first
obtains the
of a selected row by scanning the cluster index. Oracle Database
locates the rows based on this rowid.
 -- Hash scans 哈希扫描
A hash scan is used to locate rows in a hash cluster, where all rows with the same
hash value are stored in the same data block (see "Overview of Hash Clusters" on
page 2-23. The database first obtains the hash value by applying a hash function
to a cluster key value specified by the statement. Oracle Database then scans the
data blocks containing rows with this hash value.

 

The preceding checks identify the errors that can be found before statement execution.
Some errors cannot be caught by parsing. For example, the database can encounter
deadlocks or errors in data conversion only during statement execution

Syntax Check Oracle Database must check each SQL statement for syntactic validity. A
statement that breaks a rule for well-formed SQL syntax fails the check. For example,
the following statement fails because the keyword FROM is misspelled as FORM:
SQL> SELECT * FORM employees; -- 语法检查
SELECT * FORM employees
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
Semantic Check The semantics of a statement are its meaning. Thus, a semantic check
determines whether a statement is meaningful, for example, whether the objects and
columns in the statement exist. A syntactically correct statement can fail a semantic
check, as shown in the following example of a query of a nonexistent table:
SQL> SELECT * FROM nonexistent_table; -- 语义检查
SELECT * FROM nonexistent_table
*
ERROR at line 1:
ORA-00942: table or view does not exist

Shared Pool Check During the parse, the database performs a shared pool check to
determine whether it can skip resource-intensive steps of statement processing. To this
end, the database uses a hashing algorithm to generate a hash value for every SQL
statement. The statement hash value is the SQL ID shown in V$SQL.SQL_ID.
When a user submits a SQL statement, the database searches the shared SQL area to
see if an existing parsed statement has the same hash value. The hash value of a SQL
statement is distinct from the following values: -- 共享池检查
 -- Memory address for the statement -- 内存地址
Oracle Database uses the SQL ID to perform a keyed read in a lookup table. In this
way, the database obtains possible memory addresses of the statement.
 -- Hash value of an execution plan for the statement -- 执行计划的哈希值
A SQL statement can have multiple plans in the shared pool. Each plan has a
different hash value. If the same SQL ID has multiple plan hash values, then the
database knows that multiple plans exist for this SQL ID.