分区表、分区索引3(海量数据测试)
建立分区表
1 create table F_IMEI_CELL_DY_20120508BAK 2 ( 3 IMEI_KEY VARCHAR2(32), 4 DAY_KEY NUMBER(22), 5 TACTYPE_KEY NUMBER(22), 6 CELL_KEY NUMBER(22), 7 USER_ID VARCHAR2(32), 8 CELL_ENAME VARCHAR2(32), 9 SCAN_START_TIME DATE, 10 SCAN_STOP_TIME DATE, 11 MNS_TYPE NUMBER(22), 12 GRID_KEY NUMBER(22), 13 CITY_KEY NUMBER(22), 14 GSM_ID VARCHAR2(32), 15 CHRG NUMBER(22,4) default 0, 16 COMM NUMBER(22,4) default 0, 17 NP_MOBILE_SYS VARCHAR2(32), 18 IMEICELL_NUM NUMBER(22), 19 LOCATION_CELLNUM NUMBER(22), 20 BTS_KEY NUMBER(22), 21 BTS_ENAME VARCHAR2(64), 22 LOCATION_KEY NUMBER(22), 23 LOCATION_NAME VARCHAR2(128), 24 IF_TD_COVER NUMBER(22), 25 BELONG_CITY_KEY NUMBER(22), 26 PHONESYS_KEY NUMBER(22) 27 ) 28 partition by range (SCAN_START_TIME) 29 ( 30 partition P_1D_20120401 values less than (TO_DATE(' 2012-04-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 31 tablespace F_TEWMTYPE_CITY_DY 32 pctfree 10 33 initrans 1 34 maxtrans 255 35 storage 36 ( 37 initial 64K 38 minextents 1 39 maxextents unlimited 40 ), 41 partition P_1D_20120402 values less than (TO_DATE(' 2012-04-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 42 tablespace F_TEWMTYPE_CITY_DY 43 pctfree 10 44 initrans 1 45 maxtrans 255 46 storage 47 ( 48 initial 64K 49 minextents 1 50 maxextents unlimited 51 ) 52 ................ 53 );
汇总测试数据
1 INSERT INTO casdw.F_IMEI_CELL_DY_20120508bak (IMEI_KEY,USER_ID,CELL_KEY,scan_start_time,scan_stop_time,DAY_KEY,TACTYPE_KEY, 2 CELL_ENAME,BTS_KEY,BTS_ENAME,MNS_TYPE,GRID_KEY,GSM_ID,CITY_KEY,COMM,IMEICELL_NUM, 3 IF_TD_COVER,belong_city_key,phonesys_key ) 4 SELECT V_IMEI_CELL_GPRS_CS_TEST.IMEI, 5 V_IMEI_CELL_GPRS_CS_TEST.USR_ID, 6 V_IMEI_CELL_GPRS_CS_TEST.CELL_KEY, 7 V_IMEI_CELL_GPRS_CS_TEST.scan_start_time, 8 max(substr(V_IMEI_CELL_GPRS_CS_TEST.scan_start_time + interval '1' day,1,10)), 9 max(V_IMEI_CELL_GPRS_CS_TEST.DAY_KEY), 10 max(V_IMEI_CELL_GPRS_CS_TEST.TACTYPE_KEY), 11 max(V_IMEI_CELL_GPRS_CS_TEST.CELL_ENAME), 12 max(V_IMEI_CELL_GPRS_CS_TEST.BTS_KEY), 13 max(V_IMEI_CELL_GPRS_CS_TEST.BTS_ENAME), 14 max(V_IMEI_CELL_GPRS_CS_TEST.MNS_TYPE), 15 max(V_IMEI_CELL_GPRS_CS_TEST.GRID_KEY), 16 max(V_IMEI_CELL_GPRS_CS_TEST.GSM_ID), 17 max(V_IMEI_CELL_GPRS_CS_TEST.CITY_KEY), 18 sum(NVL(V_IMEI_CELL_GPRS_CS_TEST.COMM,0))/1024/1024,1, 19 max(V_IMEI_CELL_GPRS_CS_TEST.IF_TD_COVER), 20 max(V_IMEI_CELL_GPRS_CS_TEST.belong_city_key), 21 max(V_IMEI_CELL_GPRS_CS_TEST.phonesys_key) 22 FROM casdw.V_IMEI_CELL_GPRS_CS_TEST 23 WHERE scan_start_time = to_date('&1','yyyy-mm-dd') 24 group by V_IMEI_CELL_GPRS_CS_TEST.scan_start_time, V_IMEI_CELL_GPRS_CS_TEST.CELL_KEY, V_IMEI_CELL_GPRS_CS_TEST.USR_ID, V_IMEI_CELL_GPRS_CS_TEST.IMEI;
汇总语句中select的执行计划
未建索引时汇总入库耗时：48分14秒
1 [tmn@zhfx1 zhaoxj]$ tail -f 20120410.log 2 (12-05-09 09:14:00) Start 3 4 old 23: WHERE scan_start_time = to_date('&1','yyyy-mm-dd') 5 new 23: WHERE scan_start_time = to_date('2012-04-10','yyyy-mm-dd') 6 7 12177142 rows created. 8 9 Commit complete. 10 (12-05-09 10:02:14) Over
清空partition (P_1D_20120410)数据
1 alter table F_IMEI_CELL_DY_20120508bak truncate partition P_1D_20120410
创建local索引
1 CREATE UNIQUE INDEX F_IMEI_CELL_DY_0508BAK_IDX 2 ON F_IMEI_CELL_DY_20120508BAK (SCAN_START_TIME,DAY_KEY, IMEI_KEY, TACTYPE_KEY, CELL_KEY, USER_ID) 3 TABLESPACE F_TEWMTYPE_CITY_DY_IDX LOCAL
创建索引后汇总入库耗时:109分26秒
1 (12-05-09 10:49:03) Start synchro 2 3 old 23: WHERE scan_start_time = to_date('&1','yyyy-mm-dd') 4 new 23: WHERE scan_start_time = to_date('2012-04-10','yyyy-mm-dd') 5 6 12177142 rows created. 7 8 Commit complete. 9 (12-05-09 12:38:29) Over synchro
INSERT 12177142 条数据时有索引比无索引多用接近一倍时间。注：服务器性能对测试结果有一定影响
数据数量：长沙分区总数(12177142);全表分区总数(25844518)
有索引时查询结果为一条数据时的速度：0.453secends
1 select cell_ename,grid_key from F_IMEI_CELL_DY_20120508bak partition (P_1D_20120410) 2 where scan_start_time = to_date('2012-04-10','yyyy-mm-dd') 3 and DAY_key='20120410' 4 and IMEI_KEY='5305215221089010' 5 and TACTYPE_KEY='27237' 6 and CELL_KEY='329312' 7 and USER_ID='3110071623961137'
执行计划
有索引时查询结果为一条数据时的速度：328.172secends
1 select cell_ename,grid_key from F_IMEI_CELL_DY_20120508bak partition (P_1D_20120410) 2 where scan_start_time = to_date('2012-04-10','yyyy-mm-dd') 3 and DAY_key='20120410' 4 and CELL_KEY='329312' 5 and USER_ID='3110071623961137'
执行计划
表的组合索引顺序为：SCAN_START_TIME,DAY_KEY, IMEI_KEY, TACTYPE_KEY, CELL_KEY, USER_ID。
时间为什么相差这么大？
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. 即:最大地减少数据范围的条件写在后面,oracle对sql的解析是从后向前的
