Oracle wiht as usage

1. with table as is equivalent to creating a temporary table (for SQL statements that place some intermediate results in a statement in a temporary tablespace). Oracle 9i adds the WITH syntax to name subqueries in the query, put it at the beginning of the SELECT statement.

The syntax is
With tempname as (select ....)
Select...

Example:
With t as (select * from emp where depno = 10)
Select * from t where empno = xxx

With
Wd as (select did, arg (salary) average salary from work group by did ),
Em as (select emp. *, w. salary from emp left join work w on emp. eid = w. eid)
Select * from wd, em where wd. did = em. did and wd. Average salary> em. salary;



2. When is it cleared?
Temporary tables are automatically cleared by PGA when the session ends! However, the with as temporary table is cleared after the query is complete!
At 23:48:58 SCOTT @ orcl> with aa as (select * from dept)
23:57:58 2 select * from aa;

DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Used time: 00: 00: 00.12
23:58:06 SCOTT @ orcl> select * from aa;
Select * from aa
*
Row 3 has an error:
ORA-00942: Table or view does not exist


Used time: 00: 00: 00.02
At 23:58:14 SCOTT @ orcl>

3. For this function, subqueries can be achieved. Why use? What are the advantages of using?
But the execution plan is different. When there are multiple similar subqueries, use with to write the public part, because the subquery results are in the temporary memory table, the execution efficiency is of course high ~

4. Questions:
There is a table with the following data:
Aaa high
Low bbb
Low aaa
Aaa high
Low bbb
Bbb height
You need to obtain the following results,
High and low
Aaa 2 1
Bbb 1 2
How to Write SQL statements ??

Answer:
With tt (
Select 'aaa' id, 'high' value from dual union all
Select 'bbb 'id, 'low' value from dual union all
Select 'aaa' id, 'low' value from dual union all
Select 'aaa' id, 'high' value from dual union all
Select 'bbb 'id, 'low' value from dual union all
Select 'bbb 'id, 'high' value from dual)
SELECT id,
COUNT (decode (VALUE, 'high', 1) high,
COUNT (decode (VALUE, 'low', 1) low
FROM tt
Group by id;
========================================================== ======================================
Extension:
The WITH syntax is added to Oracle9i to name the subquery in the query and put it at the beginning of the SELECT statement.

A simple example:

SQL>
2 seg as (SELECT SEGMENT_NAME, SUM (BYTES)/1024 k from USER_SEGMENTS group by SEGMENT_NAME ),
3 obj as (SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS)
4 select o. OBJECT_NAME, OBJECT_TYPE, NVL (S. K, 0) SIZE_K
5 from obj o, SEG S
6 where o. OBJECT_NAME = S. SEGMENT_NAME (+)
7;
OBJECT_NAME OBJECT_TYPE SIZE_K
-----------------------------------------------------------
DAIJC_TEST TABLE 128
P_TEST PROCEDURE 0
IND_DAIJC_TEST_C1 INDEX 128

The WITH statement defines two subqueries, SEG and OBJ. In the subsequent SELECT statement, you can directly query the predefined subqueries. As shown in the preceding example, The WITH statement is used to clearly display an operation SQL statement including aggregation and external connections.

Subqueries defined by WITH not only make the query statement simpler and clearer, but also have the characteristics that can be seen at any layer of the SELECT statement.

Even in the definition layer of WITH, subqueries defined later can all use the previously defined subqueries:

SQL>
2 Q1 AS (SELECT 3 + 5 s from dual ),
3 Q2 AS (SELECT 3*5 m from dual ),
4 Q3 AS (select s, M, S + M, S * m from Q1, Q2)
5 SELECT * FROM Q3;
S m s + m s * M
----------------------------------------
8 15 23 120

You can use WITH to define the performance of subqueries that appear multiple times in a query. Oracle will optimize the performance of WITH. When you need to access the WITH-defined subquery multiple times, Oracle will put the subquery results in a temporary table, this avoids multiple executions of the same subquery, effectively reducing the number of I/O queries.

The WITH statement can be used in SELECT statements. The UPDATE and DELETE statements also support the WITH syntax, but they only need version support:
Http://www.oracle.com.cn/viewthread.php? Tid = 83530

========================================================== ============================================
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 );

Here is a simple example.

With a as (select * from test)

Select * from;

In fact, it is to put a lot of SQL statements that are used repeatedly in with as, take an alias, and then use it for subsequent queries.

This optimizes a large number of SQL statements and makes it clear