with as 详解

 WITH
LOC AS
  (SELECT L.LOCATION_ID FROM LOCATIONS L WHERE L.CITY = 'Seattle'),
DEPART AS
  (SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME
          FROM DEPARTMENTS D, LOC --引用上表LOC
         WHERE D.LOCATION_ID = LOC.LOCATION_ID),
EM AS
  (SELECT E.LAST_NAME, E.DEPARTMENT_ID FROM EMPLOYEES E)
SELECT LAST_NAME, DEPARTMENT_NAME
          FROM DEPART, EM
         WHERE DEPART.DEPARTMENT_ID = EM.DEPARTMENT_ID;

 

替代建表做测试，比如无建表权限

WITH DM_SUM_F AS
(
SELECT 201408 PERIOD,'东南亚' REGIONS,'小米4' PRODUCT,'CNY' CURRENCY FROM DUAL
UNION ALL
SELECT 201409 PERIOD,'中东' REGIONS,'Mate7' PRODUCT,'USD' CURRENCY FROM DUAL
UNION ALL
SELECT 201410 PERIOD,'非洲' REGIONS,'Find7' PRODUCT,'CNY' CURRENCY FROM DUAL
UNION ALL
SELECT 201411 PERIOD,'西欧' REGIONS,'F1' PRODUCT,'USD' CURRENCY FROM DUAL
)
SELECT PERIOD,REGIONS,PRODUCT,CURRENCY FROM DM_SUM_F

 

WITH AS： 独立子查询，可提高SQL语句的可读性，也可能提高SQL语句性能。            

                 某个表会被访问多次，可以将此表放入with as以提高性能。  

        警示：若 with as 短语没有被调用2次以上，CBO就不会讲这个短语获取的数据放入temp表，如果想要将数据放入temp表需要使用materialize hint  

                若 with as 短语被调用了2次以上，CBO会自动将 with as 短语的数据放入一个临时表，此时可免写materialize 

WITH
DEPART AS
  (SELECT /*+ materialize */   D.DEPARTMENT_ID, D.DEPARTMENT_NAME
          FROM DEPARTMENTS D
         WHERE D.DEPARTMENT_ID =100 ),
EM AS
  (SELECT E.LAST_NAME, E.DEPARTMENT_ID FROM EMPLOYEES E)
SELECT LAST_NAME, DEPARTMENT_NAME
          FROM DEPART, EM
         WHERE DEPART.DEPARTMENT_ID = EM.DEPARTMENT_ID;