我拿到sql语句。是一个简单的delete语句，这个表是一个分区表，表中的数据大约有6亿条，要删除的数据大概有900多万条。
delete event
where cycle_code = 25
and cycle_month = 2
and cycle_year = 2015
and customer_id = 5289835;
先来看看执行计划
Plan hash value: 2439880320
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 3238K| 135M| 404K (1)| 01:20:52 | | |
| 1 | DELETE | EVENT | | | | | | |
| 2 | PARTITION RANGE ITERATOR | | 3238K| 135M| 404K (1)| 01:20:52 | 241 | 261 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT | 3238K| 135M| 404K (1)| 01:20:52 | 241 | 261 |
|* 4 | INDEX RANGE SCAN | EVENT_1UQ | 1370K| | 40255 (1)| 00:08:04 | 241 | 261 |
-----------------------------------------------------------------------------------------------------------------------
发现走了索引扫描，看起来性能也不会差到哪去啊？
从整体来看，从6亿条记录中删除900多万条数据，走索引扫描感觉感觉确实是不错的选择。
首先查看了表的分区规则和基本的数据分布情况，
分区规则是基于cycle_code,cycle_month,sub_partition_id这三个字段，从查询条件来看，cycle_code，cycle_month刚好就是分区字段。
TABLE_NAME PARTITION PARTITION_COUNT COLUMN_LIST PART_COUNTS SUBPAR_COUNT STATUS
-------------------- --------- --------------- ------------------------------ ----------- ------------ ------
EVENT RANGE 721 CYCLE_CODE,CYCLE_MONTH,SUB_PAR TITION_ID 3 0 VALID
数据分布的情况如下，根据分区逻辑，数据只可能在这20个分区中。
partition_name high_value tablespace_name num_rows
C25_M2_S1 25, 2, 5 DATAH01 84246910
C25_M2_S2 25, 2, 10 DATAH01 3427570
C25_M2_S3 25, 2, 15 DATAH01 3523431
C25_M2_S4 25, 2, 20 DATAH01 3988140
C25_M2_S5 25, 2, 25 DATAH01 2700687
C25_M2_S6 25, 2, 30 DATAH01 2477792
C25_M2_S7 25, 2, 35 DATAH01 2490349
C25_M2_S8 25, 2, 40 DATAH01 11755212
C25_M2_S9 25, 2, 45 DATAH01 3184953
C25_M2_S10 25, 2, 50 DATAH01 2656802
C25_M2_S11 25, 2, 55 DATAH01 4434668
C25_M2_S12 25, 2, 60 DATAH01 2776079
C25_M2_S13 25, 2, 65 DATAH01 2949885
C25_M2_S14 25, 2, 70 DATAH01 2837790
C25_M2_S15 25, 2, 75 DATAH01 6285172
C25_M2_S16 25, 2, 80 DATAH01 2743439
C25_M2_S17 25, 2, 85 DATAH01 3574228
C25_M2_S18 25, 2, 90 DATAH01 3600820
C25_M2_S19 25, 2, 95 DATAH01 7415434
C25_M2_S20 25, 2, 100 DATAH01 3446285
分区 C25_M2_S8 中的要删除的数据有9百多万，其它分区都没有匹配的数据，从数据层面，我是没法确定这些分区一定没有可能插入新数据的。
所以分析了上面的情况，我对分区C25_M2_S1做了特殊处理，按照执行计划是走索引扫描的，因为查询条件的范围有点大，还没有匹配的数据，所以我尝试走全表扫描，开启了并行，经过测试，发现速度还是很快的，基本在1分钟左右就能够很快过滤出数据来。
所以从数据层面我提供的语句如下，把最大的分区放在了最后处理。
set linesize 200
set timing on
set time on
alter session force parallel dml parallel 16;
delete event partition(C25_M2_S2) where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;
delete event partition(C25_M2_S3) where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;
delete event partition(C25_M2_S4) where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;
。。。。。。。
delete event partition(C25_M2_S20) where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;
delete /*+ full(rated_event) parallel(rated_event,16) */ event partition(C25_M2_S1) where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;
事情到此一般就结束了，开发找到我，我们做了进一步的沟通，她根据我提供的脚本提出了一些问题，她从业务层面来做了确认，说数据只会在 C25_M2_S8 这个分区上，有了业务确认，调优的语句就更加简化了。
set linesize 200
set timing on
set time on
alter session force parallel dml parallel 16;
delete event partition(C25_M2_S8) where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;
查看执行计划，合理的走了全表扫描，因为分区中有1千多万的记录，删除900多万的数据，走全表扫描还是情理之中的。
Plan hash value: 1742190108
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 9115K| 382M| 19351 (1)| 00:03:53 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 9115K| 382M| 19351 (1)| 00:03:53 | | | Q1,00 | P->S | QC (RAND) |
| 3 | DELETE | EVENT | | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 9115K| 382M| 19351 (1)| 00:03:53 | 248 | 248 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| EVENT | 9115K| 382M| 19351 (1)| 00:03:53 | 248 | 248 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------
