原帖
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"<"MAX(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"<"MAX(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"<"MAX(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博客

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

最新文章