Oracle 5.0, Reference Manual

Page: 3004 / 3232

Restrictions on Subqueries
2984
Cursors are nonholdable (not held open after a commit).
Cursors are asensitive.
Cursors are nonscrollable.
Cursors are not named. The statement handler acts as the cursor ID.
You can have open only a single cursor per prepared statement. If you need several cursors, you must
prepare several statements.
You cannot use a cursor for a statement that generates a result set if the statement is not supported
in prepared mode. This includes statements such as
CHECK TABLE
,
HANDLER READ
, and
SHOW
BINLOG EVENTS
.
E.3. Restrictions on Subqueries
• In MySQL 5.0 before 5.0.36, if you compare a
NULL
value to a subquery using
ALL
,
ANY
, or
SOME
,
and the subquery returns an empty result, the comparison might evaluate to the nonstandard result
of
NULL
rather than to
TRUE
or
FALSE
.
• Subquery optimization for
IN
is not as effective as for the
=
operator or for the
IN(value_list)
[878] operator.
A typical case for poor
IN
subquery performance is when the subquery returns a small number of
rows but the outer query returns a large number of rows to be compared to the subquery result.
The problem is that, for a statement that uses an
IN
subquery, the optimizer rewrites it as a
correlated subquery. Consider the following statement that uses an uncorrelated subquery:
SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);
The optimizer rewrites the statement to a correlated subquery:
SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);
If the inner and outer queries return
M
and
N
rows, respectively, the execution time becomes on the
order of O(
M
×
N
), rather than O(
M
+
N
) as it would be for an uncorrelated subquery.
An implication is that an
IN
subquery can be much slower than a query written using an
IN(value_list)
[878] operator that lists the same values that the subquery would return.
• In general, you cannot modify a table and select from the same table in a subquery. For example,
this limitation applies to statements of the following forms:
DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
Exception: The preceding prohibition does not apply if you are using a subquery for the modified
table in the
FROM
clause. Example:
UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...);
Here the result from the subquery in the
FROM
clause is stored as a temporary table, so the relevant
rows in
t
have already been selected by the time the update to
t
takes place.
• Row comparison operations are only partially supported:
• For
expr [NOT] IN subquery
,
expr
can be an
n
-tuple (specified using row constructor
syntax) and the subquery can return rows of
n
-tuples. The permitted syntax is therefore more
specifically expressed as
row_constructor [NOT] IN table_subquery