About Oracle with AS usage

With AS syntax
– For an alias
With TMP as (SELECT * from Tb_name)

– For multiple aliases
With
TMP as (SELECT * from Tb_name),
TMP2 as (SELECT * from Tb_name2),
Tmp3 as (SELECT * from Tb_name3),
...

123456789

--相当于建了个e临时表withe as(select* fromscott.emp e where e.empno=7499)select* frome;--相当于建了e、d临时表with     e as(select* fromscott.emp),     d as(select* fromscott.dept)select* frome, d wheree.deptno = d.deptno;

In fact, it is to put a lot of repeated SQL statements in with as inside, take an alias, the following query can use it, so for a large number of SQL statements to play an optimization role, and clearly clear.

Insert data to a table with as usage

12345

insertintotable2with    s1 as(selectrownum c1 fromdual connectbyrownum <= 10),    s2 as(selectrownum c2 fromdual connectbyrownum <= 10)selecta.c1, b.c2 froms1 a, s2 b where...;

Select S1.sid, s2.sid from S1, S2 need the associated condition, otherwise the result will be a Cartesian product.
With as corresponds to a virtual view.

The with as phrase, also called the subquery section (subquery factoring), allows you to do many things, defining a SQL fragment that will be used by the entire SQL statement. Sometimes it is to make the SQL statement more readable, or it may be in different parts of union all as part of providing data.
　　
Especially useful for union all. Because each part of union all may be the same, but if each part goes through it, the cost is too high, so you can use the with as phrase, as long as you execute it again. If the table name defined by the with as phrase is called more than two times, the optimizer automatically places the data obtained from the with as phrase into a temp table, if it is called only once. The hint materialize, however, is to force the data in the with as phrase into a global temporary table. Many queries can improve speed in this way.

12345678910

with    sql1 as(selectto_char(a) s_name fromtest_tempa),    sql2 as(selectto_char(b) s_name fromtest_tempb wherenotexists (selects_name from sql1 whererownum=1))select* fromsql1unionallselect* fromsql2unionallselect ‘no records‘fromdual       wherenotexists (selects_name fromsql1 whererownum=1)       and notexists (selects_name fromsql2 whererownum=1);

With AS advantages
The readability of SQL is increased, and if many sub-queries are constructed, the structure will be clearer;
More importantly: "One analysis, multiple use", which is why the performance of the place, to achieve the "less read" goal

About Oracle with AS usage