原帖
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 编辑 ]
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/