With as 必须跟select

update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id);

create table a as (select * from (select * from dba_objects order by object_id) where rownum<100)


create table b as (select * from (select * from dba_objects order by object_id) where rownum<100)


SQL> update b set object_name=object_name||'xxx';

99 rows updated.


SQL> select count(*) from a;

  COUNT(*)
----------
	99

子查询会被扫描99次

SQL> alter session set statistics_level=all;

Session altered.

SQL> update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id);

99 rows updated.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------
SQL_ID	9n2d868fqhcq4, child number 0
-------------------------------------
update b set b.object_name=(select a.object_name from a where
a.object_id=b.object_id)

Plan hash value: 1917715316

-------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |	  |	 1 |	    |	   0 |00:00:00.01 |	504 |
|   1 |  UPDATE 	   | B	  |	 1 |	    |	   0 |00:00:00.01 |	504 |
|   2 |   TABLE ACCESS FULL| B	  |	 1 |	 99 |	  99 |00:00:00.01 |	  4 |
|*  3 |   TABLE ACCESS FULL| A	  |	99 |	  1 |	  99 |00:00:00.01 |	396 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("A"."OBJECT_ID"=:B1)

Note
-----
   - dynamic sampling used for this statement (level=2)


25 rows selected.


可以看到A表被扫描了99次,类似于NL。



改为with as 呢？

with  A as (select a.object_name from a )
update b set b.object_name=a.object_name 
where  a.object_id=b.object_id;

SQL> with  A as (select a.object_name from a )
update b set b.object_name=a.object_name 
where  a.object_id=b.object_id;  2    3  
update b set b.object_name=a.object_name
*
ERROR at line 2:
ORA-00928: missing SELECT keyword

with as 必须紧跟引用的select 语句,而不是delete update merge