Database Reference
In-Depth Information
Note that this section contains both the predicate present in the SQL statement itself and the predicates that may
be generated by the query optimizer or by Virtual Private Database policies. In the preceding example, you have the
following predicates:
•
The operation in line 1 checks whether the value of the bind variables lead to an empty
result set or not. The query can return rows only if the
:T2_ID_MIN<=:T2_ID_MAX
predicate is
fullfiled. If the predicate isn't fulfilled, the remainder of the query operations aren't executed.
•
The hash join at line 2 uses the
"T1"."N"="T2"."N"
predicate to join the two tables. In other
words, the access predicate might show a join condition as well. In this specific case, the
access predicate is used to specify that the hash table in memory containing the data of the
t1
table, whose hash key is
t1.n
, is probed with the values of column
t2.n
returned by accessing
the
t2
table (how hash joins work is described in detail in Chapter 14).
The index scan at line 4 accesses the
•
t_pk
index to look up the
id
column of the
t1
table. In
this case, the access predicate shows on which key the lookup is performed.
t1
table are read through a full scan. Then, when the rows have been
extracted from the blocks, the
"T1"."ID">:T1_ID
predicate is applied to filter them.
The following section shows which columns are returned as output when each operation is executed. Here's the
excerpt:
•
At line 5, all rows in the
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T2"."N"[NUMBER,22], "T2"."ID"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000]
2 - (#keys=1) "T2"."N"[NUMBER,22], "T2"."ID"[NUMBER,22],
"T2"."PAD"[VARCHAR2,1000]
3 - "T2"."ID"[NUMBER,22], "T2"."N"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000]
4 - "T2".ROWID[ROWID,10], "T2"."ID"[NUMBER,22]
5 - "T1"."N"[NUMBER,22]
In this case, it's significant to note that while the table access at line 3 returns the
id
,
n
, and
pad
columns, the
table access at line 5 returns only the
n
column. For this reason, the estimated amount of data (
Bytes
) returned from
line 3 for each row (7,392/14 = 528 bytes) is much greater than for line 5 (22,776/876 = 26 bytes). Chapter 16 talks
more about the ability of the database engine to partially read a row and why, from a performance point of view,
doing so is sensible.
Finally, there's a section providing notes and warnings about the optimization phase, the environment, or the
SQL statement itself:
Note
-----
- dynamic sampling used for this statement (level=2)
Here you're informed that the query optimizer used dynamic sampling to gather object statistics.
