使用with as 优化SQL
在一个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;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> CREATE INDEX i_t1_id ON t1(id);
Index created.
SQL> CREATE TABLE t2(id number, name varchar2(100), sex VARCHAR2(1));
Table created.
SQL> begin
2 FOR i IN 1 .. 1000 LOOP
3 INSERT INTO t2 VALUES(i,'motherfucker',CASE MOD(i,2) WHEN 1 THEN 'M' ELSE 'F' END);
4 END loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> CREATE INDEX i_t2_id ON t2(id);
Index created.
SQL> EXEC dbms_stats.gather_table_stats('ROBINSON','T1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> EXEC dbms_stats.gather_table_stats('ROBINSON','T2',cascade=>true);
PL/SQL procedure successfully completed.
比如有如下SQL语句：
SELECT a.sex, b.*
FROM (SELECT * FROM t2 WHERE sex='M') a inner join (SELECT * FROM t1 WHERE id<100) b ON a.id=b.id
UNION ALL
SELECT a.name, b.*
FROM (SELECT * FROM t2 WHERE sex='F') a inner join (SELECT * FROM t1 WHERE id<100) b ON a.id=b.id;
我们看他执行计划：
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 606540077
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 1960 | 13 (54)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | HASH JOIN | | 49 | 686 | 7 (15)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 50 | 300 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_T2_ID | 99 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 99 | 792 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_T1_ID | 99 | | 2 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 49 | 1274 | 7 (15)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| T2 | 50 | 900 | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | I_T2_ID | 99 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| T1 | 99 | 792 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | I_T1_ID | 99 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."ID"="T1"."ID")
3 - filter("SEX"='M')
4 - access("T2"."ID"<100)
6 - access("ID"<100)
7 - access("T2"."ID"="T1"."ID")
8 - filter("SEX"='F')
9 - access("T2"."ID"<100)
11 - access("ID"<100)
注意观察上面的SQL语句，t1,t2表都被访问了2次，而且t1表2次访问都是同一条SQL语句(子查询),t2表也被访问了2次，但是2次的SQL结构基本一样，所以这里可以用with as优化该SQL
WITH b AS (SELECT * FROM t1 WHERE id<100),
a as (select * from t2 where id<100)
SELECT a.sex, b.*
FROM a inner join b ON a.id=b.id and a.sex='M'
UNION ALL
SELECT a.name, b.*
FROM a inner join b ON a.id=b.id and a.sex='F';
经过优化的SQL的执行计划：
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2605773608
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1220 | 9 (56)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 99 | 792 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_T1_ID | 99 | | 2 (0)| 00:00:01 |
| 5 | LOAD AS SELECT | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 99 | 1782 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I_T2_ID | 99 | | 2 (0)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
|* 9 | HASH JOIN | | 10 | 350 | 5 (20)| 00:00:01 |
|* 10 | VIEW | | 99 | 1485 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_E78EA1 | 99 | 1782 | 2 (0)| 00:00:01 |
| 12 | VIEW | | 99 | 1980 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_E78EA1 | 99 | 792 | 2 (0)| 00:00:01 |
|* 14 | HASH JOIN | | 10 | 870 | 5 (20)| 00:00:01 |
|* 15 | VIEW | | 99 | 6633 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_E78EA1 | 99 | 1782 | 2 (0)| 00:00:01 |
| 17 | VIEW | | 99 | 1980 | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_E78EA1 | 99 | 792 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ID"<100)
7 - access("ID"<100)
9 - access("A"."ID"="B"."ID")
10 - filter("A"."SEX"='M')
14 - access("A"."ID"="B"."ID")
15 - filter("A"."SEX"='F')
35 rows selected.
