You Asked
Hi,
i made an observation that is difficult to me to understand (in oracle sql developer)
i have an sql update statement like this :
UPDATE A
SET A.a='1'
WHERE A.b not in (SELECT B.b FROM B where B.c='2') AND rownum<100000;
A and B are very huge tables, and this query takes about 10 min to execute.
However, when i put this query in a simple anonymous block (surrounding with begin and end) like this :
BEGIN
UPDATE A
SET A.a='1'
WHERE A.b not in (SELECT B.b FROM B where B.c='2') AND rownum<100000;
END;
This block (with the same update query) takes less than one second to execute.
Can somebody explain to me why this can be possible ?
Thanks.
and Connor said...
There could be a number of reasons
1) caching - the first update does the heavy lifting so to speak, second one in plsql gets all the benefit. Test this theory by reversing the order of execution
2) optimizer mode - plsql uses all_rows, your sql plus session might be ysing something else.
3) something else :-)
Use dbms_monitor to do a trace on both and check the execution plans for each.
Hope this helps.
Is this answer out of date? If it is, please let us know via a Comment