从五个SQL看with as使用

最新推荐文章于 2023-05-04 10:03:26 发布
最新推荐文章于 2023-05-04 10:03:26 发布
阅读量432 收藏 1
点赞数
文章标签： 数据库

 

SQL语句是关系型数据库最直接也是最普遍的数据访问、操作方式。应该说，RDBMS的成功因素之中，SQL语言标准的推广有不可磨灭的功劳。Oracle、SQL Server和DB2等主流数据库都是在标准SQL的基础上，加以个性化拓展，实现高效的数据库应用。

with as是Oracle SQL语系里面经常使用到的一种语句结构。特别是在一些数据仓库挖掘系统中，SQL语句结构和嵌套结构是很复杂的。with as可以帮助我们简化语句结构，提高语句可读性，另外还可以提升语句执行计划控制性。下面我们通过五个SQL语句，分析其执行计划看with as特性。

 

1、环境介绍

 

我们选择Oracle 10gR2进行实验，实验schema为scott。

 

SQL> select * from v$version;

 

BANNER

-----------------------------

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

 

SQL> select table_name from user_tables;

 

TABLE_NAME

------------------------------

DEPT

EMP

BONUS

SALGRADE

 

从语法上看，with as就是将SQL语句中单独的子查询语句提取出来，作为一个单独的命名进行组织。在原来的语句中，就可以使用新的别名来替代。

 

SQL> select count(*)

  2  from (select emp.empno empno, dept.dname from emp, dept where emp.deptno=dept.deptno);

 

  COUNT(*)

----------

        14

 

SQL> with a as (select emp.empno empno, dept.dname from emp, dept where emp.deptno=dept.deptno)

  2  select count(*)

  3  from a;

 

  COUNT(*)

----------

        14

 

2、语句分析

 

在很多文档中，都介绍了with as的用途中有一条是数据临时表化。当我们在一个SQL中包括子查询的时候，Oracle可能不会按照“子查询=》主查询”的顺序执行语句的。SQL Transformation过程中有一个子查询展开（subquery unnested）。一般的子查询都会被展开，作为一系列的join动作出现在执行计划中。with as所谓的数据临时表化，就是支持将subquery先进行执行，形成内存中的临时表，再进行后续操作。

下面是我们第一个SQL语句，是一个子查询与一个数据表的连接。

 

SQL> explain plan for with a as (select emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno)

  2   select *

  3   from a m, dept n

  4   where m.loc=n.loc;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3732937302

--------------------------------------------------------------------------------

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| T

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |         |    14 |   728 |    10  (20)| 0

|*  1 |  HASH JOIN                    |         |    14 |   728 |    10  (20)| 0

|   2 |   MERGE JOIN                  |         |    14 |   448 |     6  (17)| 0

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)| 0

|   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 0

|*  5 |    SORT JOIN                  |         |    14 |   294 |     4  (25)| 0

|   6 |     TABLE ACCESS FULL         | EMP     |    14 |   294 |     3   (0)| 0

|   7 |   TABLE ACCESS FULL           | DEPT    |     4 |    80 |     3   (0)| 0

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("DEPT"."LOC"="N"."LOC")

   5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

 

21 rows selected

 

上面SQL中，将子查询整理在with as中，以a的别名出现在系统中。在主查询里面，a与dept进行连接。从执行计划看，虽然有用with as组织，Oracle优化器CBO还是将子查询展开了，作为一系列的join操作。由此：在单次使用with as的情况下，临时表预执行动作是不会进行的。

下面语句，我们同样抽取a，主查询中两次使用a查询结果。

 

SQL> explain plan for with a as (select emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno)

  2   select *

  3   from a m, a n

  4   where m.loc=n.loc;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 202182242

--------------------------------------------------------------------------------

| Id  | Operation                      | Name                        | Rows  | B

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                             |    49 |

|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |

|   2 |   LOAD AS SELECT               |                             |       |

|   3 |    MERGE JOIN                  |                             |    14 |

|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT                        |     4 |

|   5 |      INDEX FULL SCAN           | PK_DEPT                     |     4 |

|*  6 |     SORT JOIN                  |                             |    14 |

|   7 |      TABLE ACCESS FULL         | EMP                         |    14 |

|*  8 |   HASH JOIN                    |                             |    49 |

|   9 |    VIEW                        |                             |    14 |

|  10 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D6605_207360D8 |    14 |

|  11 |    VIEW                        |                             |    14 |

|  12 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D6605_207360D8 |    14 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

   8 - access("M"."LOC"="N"."LOC")

 

26 rows selected

 

执行计划出现了变化。最典型的是在之前，使用Temp Table Transformation，将子查询进行预加载，形成内存中的SYS_TEMP_0FD9D6605_207360D8临时表。之后，对这个临时表进行join连接操作。这也就是我们经常看到的对于with as预加载的现象。对比之前的实验，我们可以知道：只有在主查询中超过一次使用with as中的子查询结构，Oracle才会“自动”的进行临时表预加载。

这样的处理也是有道理的。子查询和查询嵌套主要是便于可读性提升上，绝大多数的子查询和嵌套，都可以通过一系列的连接来完成。而数据库系统是擅长连接操作的。所以，从CBO角度看，大多数子查询被打开，构成系列的连接动作。

此外，子查询单独执行还存在临时空间占用的问题。如果进行子查询单独执行，临时结果的缓存要占用额外的空间，成本消耗相对较高。

但是如果在主查询中多次使用with as确定的别名，情况就不一样了。缓存一部分数据，要比重新执行一遍或者多次连接子查询数据表要强得多。所以，当多次进行with as子循环检索的时候，Oracle会自动的进行临时表化。

下面我们看第三个SQL语句。

 

SQL> explain plan for with a as (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where

 

emp.deptno=dept.deptno)

  2   select *

  3   from a m, dept n

  4   where m.loc=n.loc;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2532848790

--------------------------------------------------------------------------------

| Id  | Operation                      | Name                        | Rows  | B

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                             |    14 |

|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |

|   2 |   LOAD AS SELECT               | DEPT                        |       |

|   3 |    MERGE JOIN                  |                             |    14 |

|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT                        |     4 |

|   5 |      INDEX FULL SCAN           | PK_DEPT                     |     4 |

|*  6 |     SORT JOIN                  |                             |    14 |

|   7 |      TABLE ACCESS FULL         | EMP                         |    14 |

|*  8 |   HASH JOIN                    |                             |    14 |

|   9 |    TABLE ACCESS FULL           | DEPT                        |     4 |

|  10 |    VIEW                        |                             |    14 |

|  11 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D6606_207360D8 |    14 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

   8 - access("M"."LOC"="N"."LOC")

 

25 rows selected

 

这个SQL中，我们使用了with as，在主查询中只引用了一次a。但是，在执行计划中还是出现了临时表加载计算动作。区别就在于在子查询中使用了materialize的hint。

with as和materialize是经常匹配出现的。如果with as不满足临时表加载动作条件（引用两次），但是使用了materialize标记的话，Oracle还是会生成对应的临时表加载执行计划的。

如果不使用with as子句，而是直接使用materialize hint的话，会不会也出现临时表加载动作呢？

 

SQL> explain plan for select *

  2  from (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno) m,

  3       (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno) n

  4  where m.loc=n.loc;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2298815876

--------------------------------------------------------------------------------

| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |         |    49 |  3136 |    13  (16)|

|*  1 |  HASH JOIN                     |         |    49 |  3136 |    13  (16)|

|*  2 |   HASH JOIN                    |         |    14 |   602 |    10  (20)|

|   3 |    MERGE JOIN                  |         |    14 |   448 |     6  (17)|

|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)|

|   5 |      INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)|

|*  6 |     SORT JOIN                  |         |    14 |   294 |     4  (25)|

|   7 |      TABLE ACCESS FULL         | EMP     |    14 |   294 |     3   (0)|

|   8 |    TABLE ACCESS FULL           | DEPT    |     4 |    44 |     3   (0)|

|   9 |   TABLE ACCESS FULL            | EMP     |    14 |   294 |     3   (0)|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

   2 - access("DEPT"."LOC"="DEPT"."LOC")

   6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

 

24 rows selected

 

单独使用materialize hint，是不会起到临时表作用的。with as可以支持多个别名块创建动作，看第五个SQL。

 

SQL> explain plan for with a as (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where

 

emp.deptno=dept.deptno),

  2                        b as (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where

 

emp.deptno=dept.deptno)

  3  select *

  4  from a m,

  5       b n

  6  where m.loc=n.loc;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1083325232

--------------------------------------------------------------------------------

| Id  | Operation                      | Name                        | Rows  | B

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                             |    49 |

|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |

|   2 |   LOAD AS SELECT               |                             |       |

|   3 |    MERGE JOIN                  |                             |    14 |

|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT                        |     4 |

|   5 |      INDEX FULL SCAN           | PK_DEPT                     |     4 |

|*  6 |     SORT JOIN                  |                             |    14 |

|   7 |      TABLE ACCESS FULL         | EMP                         |    14 |

|   8 |   LOAD AS SELECT               |                             |       |

|   9 |    MERGE JOIN                  |                             |    14 |

|  10 |     TABLE ACCESS BY INDEX ROWID| DEPT                        |     4 |

|  11 |      INDEX FULL SCAN           | PK_DEPT                     |     4 |

|* 12 |     SORT JOIN                  |                             |    14 |

|  13 |      TABLE ACCESS FULL         | EMP                         |    14 |

|* 14 |   HASH JOIN                    |                             |    49 |

|  15 |    VIEW                        |                             |    14 |

|  16 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D6607_207360D8 |    14 |

|  17 |    VIEW                        |                             |    14 |

|  18 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D6608_207360D8 |    14 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

  12 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

  14 - access("M"."LOC"="N"."LOC")

 

34 rows selected

 

3、结论

 

综合上面的讨论，我们可以得到with as应用的条件和好处。总体上看，with as可以带来两方面的好处。第一是语句清晰度上，将原有的复杂子查询内容拆解为若干语句块，提高了可读性。第二是对于自动临时表，可以将子查询数据先检索出来，在内存中进行组织连接。

最后我们想讨论一下对于subquery unnested特性。大多数情况下，我们认为Oracle这个特性是可以生成更好地执行计划的。但是，在实践中我们也的确发现过很多这样的场景：子查询速度很快、数据量也不大，但是连接之后速度变慢，结果是发现执行计划中子查询被打开。

默认情况下，Oracle在估算多条件语句结果集数量的时候，是有“计算偏小”的特性的。也就是估算的结果集数量要小于实际数量，这个是由于默认统计量没有考虑列相关性的原因。所以，大部分查询展开都是被通过的。

如果出现这样的情况，with as + materialize是一种强制进行子查询先执行的好办法。


来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-1166038/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-1166038/

ciqu9915
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
sql with as用法详解
01-19
一．WITH AS的含义 WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。 特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一
SQL:with as用法
卜可的博客
08-31 1万+
WITH AS短语，也叫做子查询部分，定义一个SQL片断后，该SQL片断可以被整个SQL语句所用到。有的时候，with as是为了提高SQL语句的可读性，减少嵌套冗余。比如sql：with A as ( select * from user ) select * from A, customer where customer.userid = user.id**这个sql语句
SQL语句 with as 用法
热门推荐
我是一只小小小菜鸟的博客
09-23 11万+
一直以来很少使用过with  as  的用法，现在打算记录这条语句的使用方法。   比如  with A as (select * from class)     select *from A   这个语句的意思就是，先执行select * from class   得到一个结果，将这个结果记录为A  ，在执行select *from A  语句。A 表只是一个别名。 也就是将重复用
sql的with as
qq_43119740的博客
11-21 810
一直以来很少在SQL中使用过with as 的用法，现在打算记录这条语句的使用方法。 WITH AS短语，也叫做子查询部分（subquery factoring），是用来定义一个SQL片断，该SQL片断会被整个SQL语句所用到。这个语句算是公用表表达式（CTE）。 比如 with A as (select * from class) select *from A 这个语句的意思就是，先执行se...
SQL With AS 用法
cione508的博客
03-03 430
with a as (select * from table) 对于复杂的SQL查询，可以通过临时表的方式 多表时 with a as (select * from table1 ), b as (select * from table1) ... select * from a,b,... where a. = b.
SQL Server中with as使用介绍
09-09
在SQL Server中，`WITH AS` 语句，也称为公用表表达式（Common Table Expression，简称CTE），是一种强大的工具，它允许我们为复杂的查询创建临时的结果集，这个结果集可以在同一个查询中多次引用。这极大地提高了...
巧用with as 对sql语句多次利用
05-22
对于需重复查询的sql语句，可巧用with as 对sql语句进行精简，提高查询效率
SQL语句 with as 用法
weixin_72476421的博客
05-04 4053
（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询），注意：只要后面有引用的就可以，不一定非要在主查询中引用，比如后面的with查询也引用了，也是可以的。1.使用with子句可以让子查询重用相同的with查询块,通过select调用（with子句只能被select查询块引用），一般在with查询用到多次情况下。也就是将重复用到的大批量 的SQL语句，放到with as 中，加一个别名，在后面用到的时候就可以直接用。8.with查询的结果列有别名，引用的时候必须使用别名或*。
SQL 中 with as 用法(转)
cooljun618的专栏
12-21 9322
with sql1 as (select to_char(a) s_name from test_tempa), sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1)) select * from sql1ITPUB个人空间2g N
[Tommas] SQL 中 WITH AS 的用法
a5622236的博客
01-26 121
WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到； 下面的例子定义了一个 Temp 片段，Temp 可以被当做一个表调用； ;WITH Temp AS( --查询存在'应付'的(客户/单证号)SELECT DISTINCT SETTLING_CORPORATION_ID ...
SQL - WITH AS 用法
ExplorerMan的博客
02-22 750
在此同时求点击：点击点击点击。。。 再举个简单的例子 with a as (select * from test) select * from a; 其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它 这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了 mysql中没有with as 写法。
使用with as 优化SQL
落落的专栏 专注SQL调优 性能调优
06-03 6596
在一个SQL语句中，如果某个表需要被访问多次，而且每次访问的时候限制调剂基本上一致的话，我们就可以利用with as来优化下面举个例子：SQL> CREATE TABLE t1 (id number, name varchar2(10));Table created.SQL> begin  2      FOR i IN 1 .. 1000 LOOP  3          INSERT INTO t1 VALUES(i,'fuck');  4      END loop;  5      commit; 
mysql sql with as_SQL WITH AS用法实列
weixin_39628384的博客
02-26 234
本帖最后由 惜 于 2018-11-29 20:39 编辑SQL 查询所有用户最后一天的数据[SQL] 纯文本查看 复制代码CREATE TABLE UserLog(UserId INT NOT NULL,UpdateDate DATETIME NOT NULL DEFAULT GETDATE(),[Action] VARCHAR(300) NOT NULL)GOINSERT INTO UserL...
Oracle with as 嵌套
weixin_30909575的博客
06-13 1421
oracle with as可以理解为临时视图，可以极大的简化sql语句，并且支持嵌套使用。 With c3 As(Select * From v_tr_daily Where p_date=to_date('2019-05-21','yyyy-mm-dd')) ,c1 As( Select type_id,dept_id,drill_dept,sum(mine_ore0) From ...
关于oracle with as用法（比较简单的介绍）
aobi6343的博客
11-01 679
with as语法 –针对一个别名 with tmp as (select * from tb_name) –针对多个别名 with tmp as (select * from tb_name), tmp2 as (select * from tb_name2), tmp3 as (select * from tb_name3)...
Sql的with as 的使用
好热
03-05 6593
with as语法 –针对一个别名 with tmp as (select * from tb_name) –针对多个别名 with    tmp as (select * from tb_name),    tmp2 as (select * from tb_name2),    tmp3 as (sel
with as 用法
12-02 2933
原文传送门：http://blog.chinaunix.net/u2/73123/showart_1357755.html withITPUB个人空间vf/+{0H$NbKX;esql1 as (select to_char(a) s_name from test_tempa),R ]F5^5/KFW0n B0sql2 as (select to_char(b)
oracle的临时表和With As总结
Harry的博客
04-10 6513
ORACLE数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。 1. 会话级临时表 会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。 Create Global Temporary Ta
Oracle with as + /*+ materialize*/ 优化
鱼丸丶粗面
06-29 5625
文章目录with aswith as + /* materialize*/ 优化 with as `with as· with as + /* materialize*/ 优化 当 with as 中的表被多次调用时，CBO 优化器会自动决定是否将查询结果写入临时表，进行缓存 /*+ materialize*/ hint 提示，会强制性要求 with as 中的结果转换为 临时表，以此来提高 sql 效率 /*+ inline*/ hint 提示，与上相反，不转换 一般来说，当 with as 表被使
SQL server with as使用
最新发布
05-20
使用 SQL Server 中的 WITH AS 子句可以为一个查询创建一个临时表，然后在这个临时表上进行查询。这个临时表只在查询执行期间存在，并在查询结束时自动删除。 以下是一个使用 WITH AS 子句的示例查询： ``` WITH ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值