使用with子句提高性能

       子查询因子在Oracle 9i中出现，有两个好处。一、使复杂的查询可读性增强；二、可以是优化器找到新的优化方案。

       语法如下：

with {subquery_name_1} as (
        select statement 1
),
{subquery_name_2} as (
        select statement 2
        possibly referencing object {subquery_name_1}
)
select ...
from
        subquery_name_1  sq1,
        subquery_name_2  sq2,
        some_table       st
where   ...

       然后执行以下查询两个查询：

create table t1
nologging              -- adjust as necessary
as
with generator as (
       select      /*+ materialize */
              rownum        id
       from all_objects
       where     rownum <= 3000
)
select
       /*+ ordered use_nl(v2) */
       rownum                id,
       lpad(rownum,10,'0')      small_vc,
       rpad('x',100)          padding
from
       generator v1,
       generator v2
where
       rownum <= 1000000
;

 

create table t1
nologging              -- adjust as necessary
as
 select
       /*+ ordered use_nl(v2) */
       rownum                id,
       lpad(rownum,10,'0')      small_vc,
       rpad('x',100)          padding
from
       all_objects v1,
       all_objects v2
where
       rownum <= 1000000
;

       你会发现这两个CTAS简直是天壤之别，第二个被执行了N分钟之后被我取消。在子查询中的/*+ materialize */（未文档化）提示强制Oracle创建一个全局临时表（可能会非常的有用），否则Oracle将会尝试执行all_objects的自我连接笛卡尔乘积。使用了这个提示后，将指挥在两个小的表上进行连接。如果希望阻塞创建临时表，可以使用/*+ inline */提示。

       但是使用全局临时表要考虑的问题是I/O负载。因为结果集会创建在临时表空间中。