with as 查询性能记载

With as短语也叫做子查询部分，定义一个sql片断，该sql片断会被整个sql语句所用到。

With as 查询在有的时候，是为了让SQL语句的可读性更高些，有的时候也可以大幅提高sql的查询时间。

对于sql中需要重复查询的表可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。

而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

with as查询特别对于UNION ALL比较有用。做报表时很有可能需要union all同一个表来分列显示数据信息，而union all 表的名称又相同，如果不使用with as，oracle会重复扫描相同的表，而对于sql的调整方面减少查询明显可以提高sql的执行效率。

做个测试来看看实际执行效果：

create table t (x number(10), y number(10));

创建一个t_sequence序列

create sequence t_sequence

increment by 1

start with 1

maxvalue 9999

cycle

nocache

往t表中插入1W条数据

declare

i number;

begin

for i in 1..10000 loop

insert into t values(t_sequence.currval,t_sequence.nextval);

commit;

end loop;

end;

例如此时需要查找按x分组的y的总和在y的总数的1/3以上和1/2以下的分组信息

Select x,sum(y) cn from t

Group by x

Having sum(y)>(select 1/3*sum(y) from t) and sum(y)

可能一般都会这么去写，实际可以看出对表执行了三个全表扫描

With s as

(select x,sum(y) cn from t

group by x)

Select x,sum(y) from s

Where cn> (select 1/3*sum(cn) from s) and cn

看看两个sql的执行计划吧。

SQL> Select x,sum(y) cn from t

2 Group by x

3 Having sum(y)>(select 1/3*sum(y) from t) and sum(y)

4 ;

已用时间: 00: 00: 00.56

执行计划

----------------------------------------------------------

Plan hash value: 3810713582

------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 6 | 156 | 4 (25)| 00:00:01 |

|* 1 | FILTER | | | | | |

| 2 | HASH GROUP BY | | 6 | 156 | 4 (25)| 00:00:01 |

| 3 | TABLE ACCESS FULL | T | 6 | 156 | 3 (0)| 00:00:01 |

| 4 | SORT AGGREGATE | | 1 | 13 | | |

| 5 | TABLE ACCESS FULL | T | 6 | 78 | 3 (0)| 00:00:01 |

| 6 | SORT AGGREGATE | | 1 | 13 | | |

| 7 | TABLE ACCESS FULL| T | 6 | 78 | 3 (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(SUM("Y")> (SELECT .333333333333333333333333333333333333333

3*SUM("Y") FROM "T" "T") AND SUM("Y")< (SELECT .5*SUM("Y") FROM "T

"

"T"))

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

315 recursive calls

0 db block gets

107 consistent gets

14 physical reads

0 redo size

499 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

2 rows processed

SQL> alter system flush buffer_cache;

系统已更改。

已用时间: 00: 00: 00.01

SQL> alter system flush shared_pool;

系统已更改。

已用时间: 00: 00: 00.04

SQL> With s as

2 (select x,sum(y) cn from t

3 group by x)

4 Select x,cn from s

5 Where cn> (select 1/3*sum(cn) from s) and cn

已用时间: 00: 00: 02.26

执行计划

----------------------------------------------------------

Plan hash value: 2829026223

--------------------------------------------------------------------------------

------------------------

| Id | Operation | Name | Rows | Bytes |

Cost (%CPU)| Time |

--------------------------------------------------------------------------------

------------------------

| 0 | SELECT STATEMENT | | 6 | 156 |

10 (10)| 00:00:01 |

| 1 | TEMP TABLE TRANSFORMATION | | | |

| |

| 2 | LOAD AS SELECT | | | |

| |

| 3 | HASH GROUP BY | | 6 | 156 |

4 (25)| 00:00:01 |

| 4 | TABLE ACCESS FULL | T | 6 | 156 |

3 (0)| 00:00:01 |

|* 5 | VIEW | | 6 | 156 |

2 (0)| 00:00:01 |

| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_2C190C | 6 | 156 |

2 (0)| 00:00:01 |

| 7 | SORT AGGREGATE | | 1 | 13 |

| |

| 8 | VIEW | | 6 | 78 |

2 (0)| 00:00:01 |

| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_2C190C | 6 | 156 |

2 (0)| 00:00:01 |

| 10 | SORT AGGREGATE | | 1 | 13 |

| |

| 11 | VIEW | | 6 | 78 |

2 (0)| 00:00:01 |

| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_2C190C | 6 | 156 |

2 (0)| 00:00:01 |

--------------------------------------------------------------------------------

------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - filter("CN"> (SELECT .3333333333333333333333333333333333333333*SUM("CN")

FROM (SELECT

/*+ CACHE_TEMP_TABLE ("T1") */ "C0" "X","C1" "CN" FROM "SYS"."SYS_

TEMP_0FD9D660B_2C190C" "T1")

"S") AND "CN"< (SELECT .5*SUM("CN") FROM (SELECT /*+ CACHE_TEMP_T

ABLE ("T1") */ "C0" "X","C1"

"CN" FROM "SYS"."SYS_TEMP_0FD9D660B_2C190C" "T1") "S"))

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

2819 recursive calls

11 db block gets

1136 consistent gets

143 physical reads

1428 redo size

499 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

49 sorts (memory)

0 sorts (disk)

2 rows processed

往t表插入40W数据

declare

i number;

begin

for i in 1..400000 loop

insert into t values(t_sequence.currval,t_sequence.nextval);

commit;

end loop;

end;

T表的数据量有40w左右

SQL> select x,sum(y) from t group by x

2 having sum(y)>(select sum(y)*1/3 from t) and sum(y)

3 ;

未选定行

已用时间: 00: 00: 02.69

执行计划

----------

统计信息

----------------------------------------------------------

336 recursive calls

1 db block gets

1774 consistent gets

766 physical reads

176 redo size

323 bytes sent via SQL*Net to client

374 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

0 rows processed

SQL> alter system flush buffer_cache;

系统已更改。

已用时间: 00: 00: 00.07

SQL> With s as

2 (select x,sum(y) cn from t

3 group by x)

4 Select x,cn from s

5 Where cn> (select 1/3*sum(cn) from s) and cn

未选定行

已用时间: 00: 00: 01.26

执行计划

---------

711 recursive calls

30 db block gets

1267 consistent gets

806 physical reads

1428 redo size

319 bytes sent via SQL*Net to client

374 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

11 sorts (memory)

0 sorts (disk)

0 rows processed

t表数据量第一种查询对表T进行了三次全扫描，由于t表数据量比较小，而全表扫描的影响对这个查询效率影响不大，而常规的生产库中数据量肯定不是这个数量级的，一旦数据量增大，全表扫描消耗对整个系统的消耗那是很可怕的。

（其实查看user_segments中关于T表段的bytes也只有6M），常规的查询对表的扫描所消耗的资源已经不可忽视，采取with as查询灵活运用with as创建临时表，这也是为什么在大型数据仓库系统中推荐使用WITH Clause方法进行查询统计的原因，这样可以大大的提高数据分析和查询的效率。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1055856/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1055856/