分区表、分区索引3(海量数据测试)-白红宇

分区表、分区索引3(海量数据测试)

阅读量：7049 次

发布时间：2019-06-28

本文共 5137 字，大约阅读时间需要 17 分钟。

建立分区表

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_DY32     pctfree 1033     initrans 134     maxtrans 25535     storage36     (37       initial 64K38       minextents 139       maxextents unlimited40     ),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_DY43     pctfree 1044     initrans 145     maxtrans 25546     storage47     (48       initial 64K49       minextents 150       maxextents unlimited51     )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_TEST23 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 synchro2 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的解析是从后向前的

转载地址：http://bxdol.baihongyu.com/

你可能感兴趣的文章