Oracle 5.0, Справочное руководство

Страница: 703 / 3232

Optimizing
SELECT
Statements
683
and converts it to this expression:
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND trigcond( oe_1 = ie_1 )
AND ...
AND trigcond( oe_N = ie_N )
)
Each
trigcond(X)
is a special function that evaluates to the following values:
•
X
when the “linked” outer expression
oe_i
is not
NULL
•
TRUE
when the “linked” outer expression
oe_i
is
NULL
Note that trigger functions are not triggers of the kind that you create with
CREATE TRIGGER
.
Equalities that are wrapped into
trigcond()
functions are not first class predicates for the query
optimizer. Most optimizations cannot deal with predicates that may be turned on and off at query
execution time, so they assume any
trigcond(X)
to be an unknown function and ignore it. At the
moment, triggered equalities can be used by those optimizations:
• Reference optimizations:
trigcond(X=Y [OR Y IS NULL])
can be used to construct
ref
[646] ,
eq_ref
[646] , or
ref_or_null
[647] table accesses.
• Index lookup-based subquery execution engines:
trigcond(X=Y)
can be used to construct
unique_subquery
[647] or
index_subquery
[647] accesses.
• Table-condition generator: If the subquery is a join of several tables, the triggered condition will be
checked as soon as possible.
When the optimizer uses a triggered condition to create some kind of index lookup-based access
(as for the first two items of the preceding list), it must have a fallback strategy for the case when the
condition is turned off. This fallback strategy is always the same: Do a full table scan. In
EXPLAIN
output, the fallback shows up as
Full scan on NULL key
in the
Extra
column:
mysql> EXPLAIN SELECT t1.col1,
-> t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
...
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t2
type: index_subquery
possible_keys: key1
key: key1
key_len: 5
ref: func
rows: 2
Extra: Using where; Full scan on NULL key
If you run
EXPLAIN EXTENDED
followed by
SHOW WARNINGS
, you can see the triggered condition:
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`col1` AS `col1`,
<in_optimizer>(`test`.`t1`.`col1`,
<exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2
on key1 checking NULL
where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having
trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS
`t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`
from `test`.`t1`