With as 必须跟select

最新推荐文章于 2023-11-02 11:19:58 发布

scan724

最新推荐文章于 2023-11-02 11:19:58 发布

阅读量2.3k

点赞数

分类专栏： sql 查询优化

本文链接：https://blog.csdn.net/zhaoyangjian724/article/details/42168747

版权

sql 查询优化专栏收录该内容

203 篇文章 35 订阅 ¥39.90 ¥99.00

订阅专栏

本文通过示例说明了在SQL更新语句中，WITH子句必须直接跟SELECT语句，不能用于DELETE或UPDATE之后，解释了错误`ORA-00928: missing SELECT keyword`的原因，并探讨了WITH子句在查询优化中的作用。

摘要由CSDN通过智能技术生成

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
----

了解本专栏