Oracle 5.0, Reference Manual

Page: 673 / 3232

EXPLAIN EXTENDED
Output Format
653
statement, what the
SELECT
looks like after the application of rewriting and optimization rules, and
possibly other notes about the optimization process. Here is an example:
mysql> EXPLAIN EXTENDED
-> SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 4
Extra: Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t2
type: index_subquery
possible_keys: a
key: a
key_len: 5
ref: func
rows: 2
Extra: Using index
2 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a` AS `a`,
<in_optimizer>(`test`.`t1`.`a`,
<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`)
in t2 on a checking NULL having
<is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a
IN (SELECT t2.a FROM t2)` from `test`.`t1`
1 row in set (0.00 sec)
Because the statement displayed by
SHOW WARNINGS
may contain special markers to provide
information about query rewriting or optimizer actions, the statement is not necessarily valid SQL and
is not intended to be executed. The output may also include rows with
Message
values that provide
additional non-SQL explanatory notes about actions taken by the optimizer.
The following list describes special markers that can appear in
EXTENDED
output displayed by
SHOW
WARNINGS
:
•
<cache>(expr)
The expression (such as a scalar subquery) is executed once and the resulting value is saved in
memory for later use.
•
<exists>(query fragment)
The subquery predicate is converted to an
EXISTS
predicate and the subquery is transformed so
that it can be used together with the
EXISTS
predicate.
•
<in_optimizer>(query fragment)
This is an internal optimizer object with no user significance.
•
<index_lookup>(query fragement)
The query fragment is processed using an index lookup to find qualifying rows.
•
<is_not_null_test>(expr)