SQL写法对执行效率的影响，案例一则

原帖
http://www.itpub.net/viewthread. ... &extra=page%3D1


构建测试环境：

create table test_filter_hash as select * from dba_objects ;

--插入重复数据
insert into test_filter_hash  select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
  DATA_OBJECT_ID ,OBJECT_TYPE,
  add_months( CREATED,-1) ,
  LAST_DDL_TIME,TIMESTAMP ,STATUS,TEMPORARY ,GENERATED ,
  SECONDARY,NAMESPACE , EDITION_NAME
  from dba_objects ;

insert into test_filter_hash  select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
  DATA_OBJECT_ID ,OBJECT_TYPE,
  add_months( CREATED,-2) ,
  LAST_DDL_TIME,TIMESTAMP ,STATUS,TEMPORARY ,GENERATED ,
  SECONDARY,NAMESPACE , EDITION_NAME
  from test_filter_hash where rownum <1000;
  
insert into test_filter_hash  select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
  DATA_OBJECT_ID ,OBJECT_TYPE,
  add_months( CREATED,-3) ,
  LAST_DDL_TIME,TIMESTAMP ,STATUS,TEMPORARY ,GENERATED ,
  SECONDARY,NAMESPACE , EDITION_NAME
  from test_filter_hash where rownum <100;
  
   insert into test_filter_hash  select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
  DATA_OBJECT_ID ,OBJECT_TYPE,
  add_months( CREATED,-4) ,
  LAST_DDL_TIME,TIMESTAMP ,STATUS,TEMPORARY ,GENERATED ,
  SECONDARY,NAMESPACE , EDITION_NAME
  from test_filter_hash where rownum <10;

开始测试
完全没有索引的情况：
SQL>  delete from test_filter_hash t1
  2   where created < (select  max(created) from test_filter_hash t2 where t2.object_id=t1.object_id
  3   GROUP BY t2.object_id
  4   );

已删除14245行。

已用时间:  00: 01: 41.52                全表2、3万的数据，执行了 比较恐怖的近2分钟，期间CPU100%

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

-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |                  |     1 |    13 |  1543K  (1)| 05:08:42 |
|   1 |  DELETE                | TEST_FILTER_HASH |       |       |            |          |
|*  2 |   FILTER               |                  |       |       |            |          |
|   3 |    TABLE ACCESS FULL   | TEST_FILTER_HASH | 27383 |   347K|    70   (0)| 00:00:01 |
|   4 |    SORT GROUP BY NOSORT|                  |     1 |    13 |    70   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL  | TEST_FILTER_HASH |     2 |    26 |    70   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - filter("CREATED"< (SELECT MAX("CREATED") FROM "TEST_FILTER_HASH" "T2" WHERE
              "T2"."OBJECT_ID"=:B1 GROUP BY "T2"."OBJECT_ID"))
   5 - filter("T2"."OBJECT_ID"=:B1)


统计信息
----------------------------------------------------------
         27  recursive calls
      15815  db block gets
    9576656  consistent gets
          0  physical reads
    5138072  redo size
        682  bytes sent via SQL*Net to client
        723  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      14245  rows processed

去掉group by ，oracle 成功做unnest ，选择了hash join，效率大为提高：
SQL> delete from test_filter_hash t1
  2  where created < (select  max(created) from test_filter_hash t2 where t2.object_id=t1.object_id);

已删除14245行。

已用时间:  00: 00: 00.25

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

--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |                  |  1369 | 47915 |       |   232   (2)| 00:00:03 |
|   1 |  DELETE               | TEST_FILTER_HASH |       |       |       |            |          |
|*  2 |   HASH JOIN           |                  |  1369 | 47915 |       |   232   (2)| 00:00:03 |
|   3 |    VIEW               | VW_SQ_1          | 13138 |   282K|       |   161   (2)| 00:00:02 |
|   4 |     SORT GROUP BY     |                  | 13138 |   166K|   656K|   161   (2)| 00:00:02 |
|   5 |      TABLE ACCESS FULL| TEST_FILTER_HASH | 27383 |   347K|       |    70   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL  | TEST_FILTER_HASH | 27383 |   347K|       |    70   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("ITEM_1"="T1"."OBJECT_ID")
       filter("CREATED"

统计信息
----------------------------------------------------------
         19  recursive calls
      15803  db block gets
        756  consistent gets
          0  physical reads
    5137380  redo size
        682  bytes sent via SQL*Net to client
        696  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      14245  rows processed

consistent gets 对比也非常明显，9576656  =》756  


创建索引
create index i_test_fil1 on test_filter_hash (object_id);
显然这时filter的子表会通过索引扫描，应该会有较大提升，实际如下：

SQL> delete from test_filter_hash t1
  2  where created < (select  max(created) from test_filter_hash t2 where t2.object_id=t1.object_id
  3  GROUP BY t2.object_id
  4  );

已删除14245行。

已用时间:  00: 00: 00.59

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

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT               |                  |     1 |    13 | 87773   (1)| 00:17:34 |
|   1 |  DELETE                        | TEST_FILTER_HASH |       |       |            |          |
|*  2 |   FILTER                       |                  |       |       |            |          |
|   3 |    TABLE ACCESS FULL           | TEST_FILTER_HASH | 27384 |   347K|    70   (0)| 00:00:01 |
|   4 |    SORT GROUP BY NOSORT        |                  |     1 |    13 |     4   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| TEST_FILTER_HASH |     2 |    26 |     4   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN          | I_TEST_FIL1      |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   2 - filter("CREATED"< (SELECT MAX("CREATED") FROM "TEST_FILTER_HASH" "T2" WHERE
              "T2"."OBJECT_ID"=:B1 GROUP BY "T2"."OBJECT_ID"))
   6 - access("T2"."OBJECT_ID"=:B1)


统计信息
----------------------------------------------------------
         19  recursive calls
      44580  db block gets
      55451  consistent gets
          0  physical reads
    8013384  redo size
        682  bytes sent via SQL*Net to client
        719  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      14245  rows processed



没有group by的情况下索引没有意义，这很正常，执行情况没变化如下：
SQL>  delete from test_filter_hash t1
  2   where created < (select  max(created) from test_filter_hash t2 where t2.object_id=t1.object_id);

已删除14245行。

已用时间:  00: 00: 00.37

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

--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |                  |  1369 | 47915 |       |   232   (2)| 00:00:03 |
|   1 |  DELETE               | TEST_FILTER_HASH |       |       |       |            |          |
|*  2 |   HASH JOIN           |                  |  1369 | 47915 |       |   232   (2)| 00:00:03 |
|   3 |    VIEW               | VW_SQ_1          | 13139 |   282K|       |   161   (2)| 00:00:02 |
|   4 |     SORT GROUP BY     |                  | 13139 |   166K|   656K|   161   (2)| 00:00:02 |
|   5 |      TABLE ACCESS FULL| TEST_FILTER_HASH | 27384 |   347K|       |    70   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL  | TEST_FILTER_HASH | 27384 |   347K|       |    70   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("ITEM_1"="T1"."OBJECT_ID")
       filter("CREATED"

统计信息
----------------------------------------------------------
         36  recursive calls
      44607  db block gets
        764  consistent gets
          0  physical reads
    8001716  redo size
        682  bytes sent via SQL*Net to client
        698  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      14245  rows processed


因此尽管sql意义完全没有区别但，对于有group by的情况，oracle 不考虑unnest因而只好选择filter，其实际执行效果差别还是非常巨大的
上面的问题分析起来似乎很简单，但实际生产环境中SQL及其执行计划往往要远比这个要复杂，若要很快定位到问题的关键，还是需要具备相当的功底的。

再举一个经常出现的比较隐蔽的问题 

SQL> delete from test_filter_hash t1
  2  where created < (select  max(created) from test_filter_hash t2
  3  where t2.object_id=t1.object_id
  4  and t2.object_id=60
  5  )
  6  ;

已删除3行。

已用时间:  00: 00: 00.07

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

-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                 |                  |     1 |    35 |     8   (0)| 00:00:01 |
|   1 |  DELETE                          | TEST_FILTER_HASH |       |       |            |          |
|   2 |   NESTED LOOPS                   |                  |       |       |            |          |
|   3 |    NESTED LOOPS                  |                  |     1 |    35 |     8   (0)| 00:00:01 |
|   4 |     VIEW                         | VW_SQ_1          |     1 |    22 |     4   (0)| 00:00:01 |
|   5 |      SORT GROUP BY               |                  |     1 |    13 |     4   (0)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID| TEST_FILTER_HASH |     2 |    26 |     4   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | I_TEST_FIL1      |     2 |       |     1   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN             | I_TEST_FIL1      |     2 |       |     1   (0)| 00:00:01 |
|*  9 |    TABLE ACCESS BY INDEX ROWID   | TEST_FILTER_HASH |     1 |    13 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   7 - access("T2"."OBJECT_ID"=60)
   8 - access("ITEM_1"="T1"."OBJECT_ID")
   9 - filter("CREATED"

统计信息
----------------------------------------------------------
          1  recursive calls
          7  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        683  bytes sent via SQL*Net to client
        718  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          3  rows processed
实际中可能出现开发人员比较随意，不小心加错位子了，于是unnest 再度失效：

SQL> delete from test_filter_hash t1
  2  where created < (select  max(created) from test_filter_hash t2
  3  where t2.object_id=t1.object_id
  4 and t1.object_id=60                    一字之差
  5  );

已删除3行。

已用时间:  00: 00: 00.04

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

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                |                  |     1 |    13 | 87773   (1)| 00:17:34 |
|   1 |  DELETE                         | TEST_FILTER_HASH |       |       |            |          |
|*  2 |   FILTER                        |                  |       |       |            |          |
|   3 |    TABLE ACCESS FULL            | TEST_FILTER_HASH | 27384 |   347K|    70   (0)| 00:00:01 |
|   4 |    SORT AGGREGATE               |                  |     1 |    13 |            |          |
|*  5 |     FILTER                      |                  |       |       |            |          |
|   6 |      TABLE ACCESS BY INDEX ROWID| TEST_FILTER_HASH |     2 |    26 |     4   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          | I_TEST_FIL1      |     2 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - filter("CREATED"< (SELECT MAX("CREATED") FROM "TEST_FILTER_HASH" "T2" WHERE :B1=60
              AND "T2"."OBJECT_ID"=:B2))
   5 - filter(:B1=60)
   7 - access("T2"."OBJECT_ID"=:B1)


统计信息
----------------------------------------------------------
          0  recursive calls
         11  db block gets
        380  consistent gets
          0  physical reads
          0  redo size
        684  bytes sent via SQL*Net to client
        717  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed


[ 本帖最后由 anlinew 于 2010-8-5 15:54 编辑 ]

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

转载于:http://blog.itpub.net/27378/viewspace-670362/