ORACLE WITH AS 用法(Oracle with as usage)

With查询语句不是以select开始的,而是以“WITH”关键字开头    可认为在真正进行查询之前预先构造了一个临时表,之后便可多次使用它做进一步的分析和处理

WITH Clause方法的优点     增加了SQL的易读性,如果构造了多个子查询,结构会更清晰;更重要的是:“一次分析,多次使用”,这也是为什么会提供性能的地方,达到了“少读”的目标。

     第一种使用子查询的方法表被扫描了两次,而使用WITH Clause方法,表仅被扫描一次。这样可以大大的提高数据分析和查询的效率。

     另外,观察WITH Clause方法执行计划,其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。

语法:

with tempName as (select ....)
select ...
--针对一个别名
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),
   …
--相当于建了个e临时表
with e as (select * from scott.emp e where e.empno=7499)
select * from e;
 
--相当于建了e、d临时表
with
     e as (select * from scott.emp),
     d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;

其实就是把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它,这样对于大批量的sql语句起到一个优化的作用,而且清楚明了。

向一张表插入数据的 with as 用法:

insert into table2
with
    s1 as (select rownum c1 from dual connect by rownum <= 10),
    s2 as (select rownum c2 from dual connect by rownum <= 10)
select a.c1, b.c2 from s1 a, s2 b where...;

with as 相当于虚拟视图。

with as短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个sql片断,该sql片断会被整个sql语句所用到。

有的时候,是为了让sql语句的可读性更高些,也有可能是在union all的不同部分,作为提供数据的部分。  特别对于union all比较有用。

因为union all的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用with as短语,则只要执行一遍即可。

如果with as短语所定义的表名被调用两次以上,则优化器会自动将with as短语所获取的数据放入一个temp表里,如果只是被调用一次,则不会。

而提示materialize则是强制将with as短语里的数据放入一个全局临时表里。

很多查询通过这种方法都可以提高速度。

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 sql1
union all
select * from sql2
union all
select 'no records' from dual
       where not exists (select s_name from sql1 where rownum=1)
       and not exists (select s_name from sql2 where rownum=1);

with as优点增加了sql的易读性,如果构造了多个子查询,结构会更清晰;更重要的是:“一次分析,多次使用”,这也是为什么会提供性能的地方,达到了“少读”的目标

转载:https://www.cnblogs.com/mingforyou/p/8295239.html

————————

The with query statement does not start with select, but with the keyword “with”     It can be considered that a temporary table is constructed in advance before the real query, and then it can be used many times for further analysis and processing

Advantages of the with Clause method      The readability of SQL is increased. If multiple sub queries are constructed, the structure will be clearer; More importantly, “one analysis, multiple uses”, which is why it provides performance, and achieves the goal of “less reading”.

In the first method using subqueries, the table is scanned twice, while using the with Clause method, the table is scanned only once. This can greatly improve the efficiency of data analysis and query.

In addition, observe the execution plan of the with Clause method, where “sys_temp_xxxx” is the temporary table of intermediate statistical results constructed during operation.

Syntax:

with tempName as (select ....)
select ...
--针对一个别名
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),
   …
--相当于建了个e临时表
with e as (select * from scott.emp e where e.empno=7499)
select * from e;
 
--相当于建了e、d临时表
with
     e as (select * from scott.emp),
     d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;

In fact, it is to put a lot of repeatedly used SQL statements in with as, take an alias, and use it for subsequent queries. In this way, it can optimize a large number of SQL statements, and it is clear.

To insert data into a table   With as usage:

insert into table2
with
    s1 as (select rownum c1 from dual connect by rownum <= 10),
    s2 as (select rownum c2 from dual connect by rownum <= 10)
select a.c1, b.c2 from s1 a, s2 b where...;

With as is equivalent to a virtual view.

The phrase with as, also known as subquery factoring, allows you to do many things and define an SQL fragment that will be used by the whole SQL statement.

Sometimes, it is to make the readability of SQL statements higher, or it may be in different parts of union all as the part of providing data. Especially useful for union all.

Because each part of the union all may be the same, but if each part is executed once, the cost is too high. Therefore, the phrase with as can be used, and it can be executed once.

If the table name defined by the with as phrase is called more than twice, the optimizer will automatically put the data obtained by the with as phrase into a temp table. If it is called only once, it will not.

The prompt materialize is to force the data in the with as phrase into a global temporary table.

Many queries can improve speed through this method.

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 sql1
union all
select * from sql2
union all
select 'no records' from dual
       where not exists (select s_name from sql1 where rownum=1)
       and not exists (select s_name from sql2 where rownum=1);

< strong > advantages of with as < / strong > it increases the readability of SQL. If multiple sub queries are constructed, the structure will be clearer; More importantly, “one analysis, multiple uses”, which is why it provides performance and achieves the goal of “less reading”

转载:https://www.cnblogs.com/mingforyou/p/8295239.html