WITH AS 测试

最新推荐文章于 2023-07-23 19:04:22 发布
Laughing
最新推荐文章于 2023-07-23 19:04:22 发布
阅读量1k
点赞数
分类专栏： Oracle SQL 调优文章标签： table filter access sql transformation disk
本文链接：https://blog.csdn.net/aqszhuaihuai/article/details/6963472
版权
Oracle SQL 调优专栏收录该内容
30 篇文章 1 订阅
订阅专栏
WITH AS 子句定义SQL语句级的临时表、该临时表仅对本次执行的SQL有效。
SQL> select count(*) from t3 where table_name='test' and tablespace_name='tbs1';

  COUNT(*)
----------
    299999

SQL> select count(*) from t3 where table_name='test' and tablespace_name='tbs2';

  COUNT(*)
----------
    300000
SQL> select count(*) from t3 where table_name='test' and tablespace_name='tbs3';

  COUNT(*)
----------
    400000
SQL> select count(*) from t3 ;

  COUNT(*)
----------
   3233792

SQL> with tmp as
  2  (select blocks,num_rows,tablespace_name from t3 where table_name='test')
  3  select blocks,num_rows from tmp  where tablespace_name='tbs3'
  4  union all
  5  select blocks,num_rows from tmp  where tablespace_name='tbs2'
  6  union all
  7  select blocks,num_rows from tmp where tablespace_name='tbs1';

已选择999999行。

已用时间:  00: 00: 24.51

执行计划
----------------------------------------------------------
Plan hash value: 2052080865

-------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                          |     3 |   129 |     6  (67)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                          |       |       |            |          |
|   2 |   LOAD AS SELECT           |                          |       |       |            |          |
|*  3 |    TABLE ACCESS FULL       | T3                       |     1 |    32 |    43   (0)| 00:00:01 |
|   4 |   UNION-ALL                |                          |       |       |            |          |
|*  5 |    VIEW                    |                          |     1 |    43 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6604_CF1A0 |     1 |    13 |     2   (0)| 00:00:01 |
|*  7 |    VIEW                    |                          |     1 |    43 |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6604_CF1A0 |     1 |    13 |     2   (0)| 00:00:01 |
|*  9 |    VIEW                    |                          |     1 |    43 |     2   (0)| 00:00:01 |
|  10 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6604_CF1A0 |     1 |    13 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("TABLE_NAME"='test')
   5 - filter("TABLESPACE_NAME"='tbs3')
   7 - filter("TABLESPACE_NAME"='tbs2')
   9 - filter("TABLESPACE_NAME"='tbs1')


统计信息
----------------------------------------------------------
        912  recursive calls
       4405  db block gets
     172745  consistent gets
      86161  physical reads
       1476  redo size
   15545728  bytes sent via SQL*Net to client
     733711  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
         23  sorts (memory)
          0  sorts (disk)
     999999  rows processed

SQL> select blocks,num_rows from t3 where table_name='test' and tablespace_name='tbs3'
  2  union all
  3  select blocks,num_rows from t3 where table_name='test' and tablespace_name='tbs2'
  4  union all
  5  select blocks,num_rows from t3 where table_name='test' and tablespace_name='tbs1';

已选择999999行。

已用时间:  00: 00: 42.96

执行计划
----------------------------------------------------------
Plan hash value: 2294035736

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    96 |   130  (67)| 00:00:02 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T3   |     1 |    32 |    43   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T3   |     1 |    32 |    43   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| T3   |     1 |    32 |    43   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TABLESPACE_NAME"='tbs3' AND "TABLE_NAME"='test')
   3 - filter("TABLESPACE_NAME"='tbs2' AND "TABLE_NAME"='test')
   4 - filter("TABLESPACE_NAME"='tbs1' AND "TABLE_NAME"='test')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     343125  consistent gets
     245980  physical reads
          0  redo size
   15545728  bytes sent via SQL*Net to client
     733711  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     999999  rows processed

SQL> with tmp as
  2  (select /*+ materialize */  blocks,num_rows,tablespace_name from t3 where table_name='test')
  3  select blocks,num_rows from tmp  where tablespace_name='tbs3'
  4  union all
  5  select blocks,num_rows from tmp  where tablespace_name='tbs2'
  6  union all
  7  select blocks,num_rows from tmp where tablespace_name='tbs1';

已选择999999行。

已用时间:  00: 00: 24.60

执行计划
----------------------------------------------------------
Plan hash value: 94255643

-------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                          |     3 |   129 |     6  (67)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                          |       |       |            |          |
|   2 |   LOAD AS SELECT           |                          |       |       |            |          |
|*  3 |    TABLE ACCESS FULL       | T3                       |     1 |    32 |    43   (0)| 00:00:01 |
|   4 |   UNION-ALL                |                          |       |       |            |          |
|*  5 |    VIEW                    |                          |     1 |    43 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6606_CF1A0 |     1 |    13 |     2   (0)| 00:00:01 |
|*  7 |    VIEW                    |                          |     1 |    43 |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6606_CF1A0 |     1 |    13 |     2   (0)| 00:00:01 |
|*  9 |    VIEW                    |                          |     1 |    43 |     2   (0)| 00:00:01 |
|  10 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6606_CF1A0 |     1 |    13 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("TABLE_NAME"='test')
   5 - filter("TABLESPACE_NAME"='tbs3')
   7 - filter("TABLESPACE_NAME"='tbs2')
   9 - filter("TABLESPACE_NAME"='tbs1')


统计信息
----------------------------------------------------------
        153  recursive calls
       4405  db block gets
     172346  consistent gets
      86157  physical reads
       1532  redo size
   15545728  bytes sent via SQL*Net to client
     733711  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     999999  rows processed

--SYS_TEMP_0FD9D6606_CF1A0为系统产生的临时表。

--一般写法
SQL> select blocks,num_rows from t3 where table_name='test'
  2  and (tablespace_name='tbs3' or tablespace_name='tbs2' or tablespace_name='tbs1');

已选择999999行。

已用时间:  00: 00: 22.94

执行计划
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    32 |    43   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    32 |    43   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TABLE_NAME"='test' AND ("TABLESPACE_NAME"='tbs1' OR
              "TABLESPACE_NAME"='tbs2' OR "TABLESPACE_NAME"='tbs3'))


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     157529  consistent gets
      81835  physical reads
          0  redo size
   14157443  bytes sent via SQL*Net to client
     733711  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     999999  rows processed
某些时候，with as 的性能要比union all好些，物理读和逻辑读更少。一般写法因为对表只扫描一次，物理读和逻辑读最少。在一些情况下根据业务使用with as 改写union all，也许可能提升性能。