事务压缩 对表的影响 compress for oltp

1 创建测试表并对添加数据

   ………...省略

2 修改表的属性为事务型压缩

SQL> alter table TH_VEHICLE_ALARM_comp compress for oltp;

Table altered.

3 查询速度的对比

非压缩表

275 rows selected. Elapsed: 00:00:00.17 Execution Plan ---------------------------------------------------------- Plan hash value: 1253497679 ----------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                   |                        |     1 |  2179 | 23244   (1)| 00:04:39 |       |       | |   1 |  PARTITION RANGE SINGLE            |                        |     1 |  2179 | 23244   (1)| 00:04:39 |   185 |   185 | |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TH_VEHICLE_ALARM_NOMAL |     1 |  2179 | 23244   (1)| 00:04:39 |   185 |   185 | |*  3 |    INDEX RANGE SCAN                | IND_VEHICLE_ALARM_UTC2 |    26 |       | 23234   (1)| 00:04:39 |   185 |   185 | ----------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("UTC">=1446220800000 AND "ALARM_CODE"='47' AND "VID"='22756977660952498395' AND "UTC"<1446305200000)        filter("VID"='22756977660952498395' AND "ALARM_CODE"='47') Note -----    - dynamic sampling used for this statement (level=2) Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets       11910  consistent gets           0  physical reads           0  redo size       38961  bytes sent via SQL*Net to client         718  bytes received via SQL*Net from client          20  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         275  rows processed

713 rows selected. Elapsed: 00:00:00.46 Execution Plan ---------------------------------------------------------- Plan hash value: 923165998 ----------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                   |                        |     8 | 17432 | 30349   (1)| 00:06:05 |       |       | |   1 |  PARTITION RANGE ITERATOR          |                        |     8 | 17432 | 30349   (1)| 00:06:05 |   185 |   187 | |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TH_VEHICLE_ALARM_NOMAL |     8 | 17432 | 30349   (1)| 00:06:05 |   185 |   187 | |*  3 |    INDEX RANGE SCAN                | IND_VEHICLE_ALARM_UTC2 |   249 |       | 30349   (1)| 00:06:05 |   185 |   187 | ----------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("UTC">=1446220800000 AND "ALARM_CODE"='47' AND "VID"='22756977660952498395' AND "UTC"<1446480000000)        filter("VID"='22756977660952498395' AND "ALARM_CODE"='47') Note -----    - dynamic sampling used for this statement (level=2) Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets       35524  consistent gets           0  physical reads           0  redo size       92398  bytes sent via SQL*Net to client        1037  bytes received via SQL*Net from client          49  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         713  rows processed

压缩表

275 rows selected. Elapsed: 00:00:00.17 Execution Plan ---------------------------------------------------------- Plan hash value: 756877945 ----------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                   |                        |     1 |  2179 | 23274   (1)| 00:04:40 |       |       | |   1 |  PARTITION RANGE SINGLE            |                        |     1 |  2179 | 23274   (1)| 00:04:40 |   185 |   185 | |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TH_VEHICLE_ALARM_COMP  |     1 |  2179 | 23274   (1)| 00:04:40 |   185 |   185 | |*  3 |    INDEX RANGE SCAN                | IND_VEHICLE_ALARM_UTC3 |    27 |       | 23266   (1)| 00:04:40 |   185 |   185 | ----------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("UTC">=1446220800000 AND "ALARM_CODE"='47' AND "VID"='22756977660952498395' AND "UTC"<1446305200000)        filter("VID"='22756977660952498395' AND "ALARM_CODE"='47') Note -----    - dynamic sampling used for this statement (level=2) Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets       11910  consistent gets           0  physical reads           0  redo size       38961  bytes sent via SQL*Net to client         718  bytes received via SQL*Net from client          20  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         275  rows processed

713 rows selected. Elapsed: 00:00:00.47 Execution Plan ---------------------------------------------------------- Plan hash value: 4121871953 ----------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                   |                        |  5550 |    11M| 30349   (1)| 00:06:05 |       |       | |   1 |  PARTITION RANGE ITERATOR          |                        |  5550 |    11M| 30349   (1)| 00:06:05 |   185 |   187 | |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TH_VEHICLE_ALARM_COMP  |  5550 |    11M| 30349   (1)| 00:06:05 |   185 |   187 | |*  3 |    INDEX RANGE SCAN                | IND_VEHICLE_ALARM_UTC3 |   226 |       | 30349   (1)| 00:06:05 |   185 |   187 | ----------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("UTC">=1446220800000 AND "ALARM_CODE"='47' AND "VID"='22756977660952498395' AND "UTC"<1446480000000)        filter("VID"='22756977660952498395' AND "ALARM_CODE"='47') Note -----    - dynamic sampling used for this statement (level=2) Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets       35523  consistent gets           0  physical reads           0  redo size       92398  bytes sent via SQL*Net to client        1037  bytes received via SQL*Net from client          49  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         713  rows processed

在查询上面基本没有什么较大的差距

4 插入速度的对比

非压缩表

[lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ ./dataWrite.sh start   /opt/soft/jdk1.6.0_22/bin/java -Xmx2048m -Xms2048m -XX:NewRatio=2 -XX:PermSize=64m -XX:MaxPermSize=128m -XX:+UseConcMarkSweepGC -XX:+UseCMSCompactAtFullCollection -XX:CMSMaxAbortablePrecleanTime=50 -XX:+CMSClassUnloadingEnabled -XX:+PrintGC -XX:+PrintGCDateStamps -Xloggc:/logs/dataWrite/jvmlog/jvm-2016-08-19-15-29.log -cp /home/lbs/dataWrite/lib/dataWrite.jar:/home/lbs/dataWrite/lib/dom4j-1.7-20060614.jar:/home/lbs/dataWrite/lib/log4j-1.2.14.jar:/home/lbs/dataWrite/lib/ojdbc14.jar:/home/lbs/dataWrite/lib/old2NewTable.jar:.:/opt/soft/jdk1.6.0_22/lib/dt.jar:/opt/soft/jdk1.6.0_22/lib/tools.jar:/opt/tomcat1/lib/:/opt/tomcat4:/home/lbs/dataWrite com.ctfo.core.DataWriteCore start JVM_LOG_DIR=/logs/dataWrite/jvmlog/ Create JVM_LOG /logs/dataWrite/jvmlog/jvm-2016-08-19-15-29.log Starting dataWrite server... dataWrite initialized! [lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ [2016-08-19 15:29:35 113] - [INFO ] - (DataWriteCore.java:37)     - 开始... insert into TH_VEHICLE_ALARM_nomal (alarm_id, vid, utc, lat, lon, maplat, maplon, elevation, direction, gps_speed, alarm_code, sysutc, alarm_status, alarm_hperson, alarm_htime, alarm_mem, alarm_start_utc, alarm_end_utc, alarm_handler_status, alarm_driver, mileage, oil_total, end_lat, end_lon, end_maplon, end_maplat, end_elevation, end_direction, end_gps_speed, end_mileage, end_oil_total, vehicle_no, alarm_todo, alarm_src, alarm_handler_status_type, bglevel, signstatus, extendstatus, basestatus, alarm_add_info_start, alarm_add_info_end, overspeed_info_st, entering_area_st, out_route_st, overspeed_info_ed, entering_area_ed, out_route_ed, alarm_add_info, area_id, max_rpm, speed_threshold, handler_methods, max_speed, avg_speed, team_id, team_name, corp_id, corp_name, media_uri, driver_name, driver_id, driver_src) values (?, '6142866312156124479308', ?, 23944595, 69840789, 23945442, 69844536, 0, 0, 0, '7', 1467874919154, '0', null, null, null, 1452409421000, 1452655541000, 0, null, 100, -1, 23944543, 69840609, 69844360, 23945387, 0, 0, 16, 100, -1, '京B12984', null, 1, -1, null, null, null, '524291', null, null, null, null, null, null, null, null, null, null, 0, null, null, 16, 0, '72919763307600711121', '未分队车辆', '614889763307527741121', '胎压测试转组', null, null, null, '-1') 50000 1000 [2016-08-19 15:29:52 127] - [INFO ] - (DataWriteCore.java:45)     - 共耗时 17007 毫秒 [2016-08-19 15:29:52 128] - [INFO ] - (DataWriteCore.java:46)     - 结束

[lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ ./dataWrite.sh start /opt/soft/jdk1.6.0_22/bin/java -Xmx2048m -Xms2048m -XX:NewRatio=2 -XX:PermSize=64m -XX:MaxPermSize=128m -XX:+UseConcMarkSweepGC -XX:+UseCMSCompactAtFullCollection -XX:CMSMaxAbortablePrecleanTime=50 -XX:+CMSClassUnloadingEnabled -XX:+PrintGC -XX:+PrintGCDateStamps -Xloggc:/logs/dataWrite/jvmlog/jvm-2016-08-19-15-40.log -cp /home/lbs/dataWrite/lib/dataWrite.jar:/home/lbs/dataWrite/lib/dom4j-1.7-20060614.jar:/home/lbs/dataWrite/lib/log4j-1.2.14.jar:/home/lbs/dataWrite/lib/ojdbc14.jar:/home/lbs/dataWrite/lib/old2NewTable.jar:.:/opt/soft/jdk1.6.0_22/lib/dt.jar:/opt/soft/jdk1.6.0_22/lib/tools.jar:/opt/tomcat1/lib/:/opt/tomcat4:/home/lbs/dataWrite com.ctfo.core.DataWriteCore start JVM_LOG_DIR=/logs/dataWrite/jvmlog/ Create JVM_LOG /logs/dataWrite/jvmlog/jvm-2016-08-19-15-40.log Starting dataWrite server... dataWrite initialized! [lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ [2016-08-19 15:40:13 155] - [INFO ] - (DataWriteCore.java:37)     - 开始... insert into TH_VEHICLE_ALARM_nomal (alarm_id, vid, utc, lat, lon, maplat, maplon, elevation, direction, gps_speed, alarm_code, sysutc, alarm_status, alarm_hperson, alarm_htime, alarm_mem, alarm_start_utc, alarm_end_utc, alarm_handler_status, alarm_driver, mileage, oil_total, end_lat, end_lon, end_maplon, end_maplat, end_elevation, end_direction, end_gps_speed, end_mileage, end_oil_total, vehicle_no, alarm_todo, alarm_src, alarm_handler_status_type, bglevel, signstatus, extendstatus, basestatus, alarm_add_info_start, alarm_add_info_end, overspeed_info_st, entering_area_st, out_route_st, overspeed_info_ed, entering_area_ed, out_route_ed, alarm_add_info, area_id, max_rpm, speed_threshold, handler_methods, max_speed, avg_speed, team_id, team_name, corp_id, corp_name, media_uri, driver_name, driver_id, driver_src) values (?, '6142866312156124479308', ?, 23944595, 69840789, 23945442, 69844536, 0, 0, 0, '7', 1467874919154, '0', null, null, null, 1452409421000, 1452655541000, 0, null, 100, -1, 23944543, 69840609, 69844360, 23945387, 0, 0, 16, 100, -1, '京B12984', null, 1, -1, null, null, null, '524291', null, null, null, null, null, null, null, null, null, null, 0, null, null, 16, 0, '72919763307600711121', '未分队车辆', '614889763307527741121', '胎压测试转组', null, null, null, '-1') 50000 1000 [2016-08-19 15:41:17 154] - [INFO ] - (DataWriteCore.java:45)     - 共耗时 63992 毫秒 [2016-08-19 15:41:17 154] - [INFO ] - (DataWriteCore.java:46)     - 结束

[lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ ./dataWrite.sh start /opt/soft/jdk1.6.0_22/bin/java -Xmx2048m -Xms2048m -XX:NewRatio=2 -XX:PermSize=64m -XX:MaxPermSize=128m -XX:+UseConcMarkSweepGC -XX:+UseCMSCompactAtFullCollection -XX:CMSMaxAbortablePrecleanTime=50 -XX:+CMSClassUnloadingEnabled -XX:+PrintGC -XX:+PrintGCDateStamps -Xloggc:/logs/dataWrite/jvmlog/jvm-2016-08-19-15-45.log -cp /home/lbs/dataWrite/lib/dataWrite.jar:/home/lbs/dataWrite/lib/dom4j-1.7-20060614.jar:/home/lbs/dataWrite/lib/log4j-1.2.14.jar:/home/lbs/dataWrite/lib/ojdbc14.jar:/home/lbs/dataWrite/lib/old2NewTable.jar:.:/opt/soft/jdk1.6.0_22/lib/dt.jar:/opt/soft/jdk1.6.0_22/lib/tools.jar:/opt/tomcat1/lib/:/opt/tomcat4:/home/lbs/dataWrite com.ctfo.core.DataWriteCore start JVM_LOG_DIR=/logs/dataWrite/jvmlog/ Create JVM_LOG /logs/dataWrite/jvmlog/jvm-2016-08-19-15-45.log Starting dataWrite server... dataWrite initialized! [lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ [2016-08-19 15:45:49 946] - [INFO ] - (DataWriteCore.java:37)     - 开始... insert into TH_VEHICLE_ALARM_nomal (alarm_id, vid, utc, lat, lon, maplat, maplon, elevation, direction, gps_speed, alarm_code, sysutc, alarm_status, alarm_hperson, alarm_htime, alarm_mem, alarm_start_utc, alarm_end_utc, alarm_handler_status, alarm_driver, mileage, oil_total, end_lat, end_lon, end_maplon, end_maplat, end_elevation, end_direction, end_gps_speed, end_mileage, end_oil_total, vehicle_no, alarm_todo, alarm_src, alarm_handler_status_type, bglevel, signstatus, extendstatus, basestatus, alarm_add_info_start, alarm_add_info_end, overspeed_info_st, entering_area_st, out_route_st, overspeed_info_ed, entering_area_ed, out_route_ed, alarm_add_info, area_id, max_rpm, speed_threshold, handler_methods, max_speed, avg_speed, team_id, team_name, corp_id, corp_name, media_uri, driver_name, driver_id, driver_src) values (?, '6142866312156124479308', ?, 23944595, 69840789, 23945442, 69844536, 0, 0, 0, '7', 1467874919154, '0', null, null, null, 1452409421000, 1452655541000, 0, null, 100, -1, 23944543, 69840609, 69844360, 23945387, 0, 0, 16, 100, -1, '京B12984', null, 1, -1, null, null, null, '524291', null, null, null, null, null, null, null, null, null, null, 0, null, null, 16, 0, '72919763307600711121', '未分队车辆', '614889763307527741121', '胎压测试转组', null, null, null, '-1') 50000 1000 [2016-08-19 15:46:21 049] - [INFO ] - (DataWriteCore.java:45)     - 共耗时 31096 毫秒 [2016-08-19 15:46:21 050] - [INFO ] - (DataWriteCore.java:46)     - 结束

压缩表

[lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ ./dataWrite.sh start /opt/soft/jdk1.6.0_22/bin/java -Xmx2048m -Xms2048m -XX:NewRatio=2 -XX:PermSize=64m -XX:MaxPermSize=128m -XX:+UseConcMarkSweepGC -XX:+UseCMSCompactAtFullCollection -XX:CMSMaxAbortablePrecleanTime=50 -XX:+CMSClassUnloadingEnabled -XX:+PrintGC -XX:+PrintGCDateStamps -Xloggc:/logs/dataWrite/jvmlog/jvm-2016-08-19-15-32.log -cp /home/lbs/dataWrite/lib/dataWrite.jar:/home/lbs/dataWrite/lib/dom4j-1.7-20060614.jar:/home/lbs/dataWrite/lib/log4j-1.2.14.jar:/home/lbs/dataWrite/lib/ojdbc14.jar:/home/lbs/dataWrite/lib/old2NewTable.jar:.:/opt/soft/jdk1.6.0_22/lib/dt.jar:/opt/soft/jdk1.6.0_22/lib/tools.jar:/opt/tomcat1/lib/:/opt/tomcat4:/home/lbs/dataWrite com.ctfo.core.DataWriteCore start JVM_LOG_DIR=/logs/dataWrite/jvmlog/ Create JVM_LOG /logs/dataWrite/jvmlog/jvm-2016-08-19-15-32.log Starting dataWrite server... dataWrite initialized! [lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ [2016-08-19 15:32:32 025] - [INFO ] - (DataWriteCore.java:37)     - 开始... insert into TH_VEHICLE_ALARM_comp (alarm_id, vid, utc, lat, lon, maplat, maplon, elevation, direction, gps_speed, alarm_code, sysutc, alarm_status, alarm_hperson, alarm_htime, alarm_mem, alarm_start_utc, alarm_end_utc, alarm_handler_status, alarm_driver, mileage, oil_total, end_lat, end_lon, end_maplon, end_maplat, end_elevation, end_direction, end_gps_speed, end_mileage, end_oil_total, vehicle_no, alarm_todo, alarm_src, alarm_handler_status_type, bglevel, signstatus, extendstatus, basestatus, alarm_add_info_start, alarm_add_info_end, overspeed_info_st, entering_area_st, out_route_st, overspeed_info_ed, entering_area_ed, out_route_ed, alarm_add_info, area_id, max_rpm, speed_threshold, handler_methods, max_speed, avg_speed, team_id, team_name, corp_id, corp_name, media_uri, driver_name, driver_id, driver_src) values (?, '6142866312156124479308', ?, 23944595, 69840789, 23945442, 69844536, 0, 0, 0, '7', 1467874919154, '0', null, null, null, 1452409421000, 1452655541000, 0, null, 100, -1, 23944543, 69840609, 69844360, 23945387, 0, 0, 16, 100, -1, '京B12984', null, 1, -1, null, null, null, '524291', null, null, null, null, null, null, null, null, null, null, 0, null, null, 16, 0, '72919763307600711121', '未分队车辆', '614889763307527741121', '胎压测试转组', null, null, null, '-1') 50000 1000 [2016-08-19 15:35:02 848] - [INFO ] - (DataWriteCore.java:45)     - 共耗时 150820 毫秒 [2016-08-19 15:35:02 849] - [INFO ] - (DataWriteCore.java:46)     - 结束

[lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ ./dataWrite.sh start /opt/soft/jdk1.6.0_22/bin/java -Xmx2048m -Xms2048m -XX:NewRatio=2 -XX:PermSize=64m -XX:MaxPermSize=128m -XX:+UseConcMarkSweepGC -XX:+UseCMSCompactAtFullCollection -XX:CMSMaxAbortablePrecleanTime=50 -XX:+CMSClassUnloadingEnabled -XX:+PrintGC -XX:+PrintGCDateStamps -Xloggc:/logs/dataWrite/jvmlog/jvm-2016-08-19-15-36.log -cp /home/lbs/dataWrite/lib/dataWrite.jar:/home/lbs/dataWrite/lib/dom4j-1.7-20060614.jar:/home/lbs/dataWrite/lib/log4j-1.2.14.jar:/home/lbs/dataWrite/lib/ojdbc14.jar:/home/lbs/dataWrite/lib/old2NewTable.jar:.:/opt/soft/jdk1.6.0_22/lib/dt.jar:/opt/soft/jdk1.6.0_22/lib/tools.jar:/opt/tomcat1/lib/:/opt/tomcat4:/home/lbs/dataWrite com.ctfo.core.DataWriteCore start JVM_LOG_DIR=/logs/dataWrite/jvmlog/ Create JVM_LOG /logs/dataWrite/jvmlog/jvm-2016-08-19-15-36.log Starting dataWrite server... dataWrite initialized! [lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ [2016-08-19 15:37:00 031] - [INFO ] - (DataWriteCore.java:37)     - 开始... insert into TH_VEHICLE_ALARM_comp (alarm_id, vid, utc, lat, lon, maplat, maplon, elevation, direction, gps_speed, alarm_code, sysutc, alarm_status, alarm_hperson, alarm_htime, alarm_mem, alarm_start_utc, alarm_end_utc, alarm_handler_status, alarm_driver, mileage, oil_total, end_lat, end_lon, end_maplon, end_maplat, end_elevation, end_direction, end_gps_speed, end_mileage, end_oil_total, vehicle_no, alarm_todo, alarm_src, alarm_handler_status_type, bglevel, signstatus, extendstatus, basestatus, alarm_add_info_start, alarm_add_info_end, overspeed_info_st, entering_area_st, out_route_st, overspeed_info_ed, entering_area_ed, out_route_ed, alarm_add_info, area_id, max_rpm, speed_threshold, handler_methods, max_speed, avg_speed, team_id, team_name, corp_id, corp_name, media_uri, driver_name, driver_id, driver_src) values (?, '6142866312156124479308', ?, 23944595, 69840789, 23945442, 69844536, 0, 0, 0, '7', 1467874919154, '0', null, null, null, 1452409421000, 1452655541000, 0, null, 100, -1, 23944543, 69840609, 69844360, 23945387, 0, 0, 16, 100, -1, '京B12984', null, 1, -1, null, null, null, '524291', null, null, null, null, null, null, null, null, null, null, 0, null, null, 16, 0, '72919763307600711121', '未分队车辆', '614889763307527741121', '胎压测试转组', null, null, null, '-1') 50000 1000 [2016-08-19 15:39:20 389] - [INFO ] - (DataWriteCore.java:45)     - 共耗时 140350 毫秒 [2016-08-19 15:39:20 390] - [INFO ] - (DataWriteCore.java:46)     - 结束

[lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ ./dataWrite.sh start /opt/soft/jdk1.6.0_22/bin/java -Xmx2048m -Xms2048m -XX:NewRatio=2 -XX:PermSize=64m -XX:MaxPermSize=128m -XX:+UseConcMarkSweepGC -XX:+UseCMSCompactAtFullCollection -XX:CMSMaxAbortablePrecleanTime=50 -XX:+CMSClassUnloadingEnabled -XX:+PrintGC -XX:+PrintGCDateStamps -Xloggc:/logs/dataWrite/jvmlog/jvm-2016-08-19-15-47.log -cp /home/lbs/dataWrite/lib/dataWrite.jar:/home/lbs/dataWrite/lib/dom4j-1.7-20060614.jar:/home/lbs/dataWrite/lib/log4j-1.2.14.jar:/home/lbs/dataWrite/lib/ojdbc14.jar:/home/lbs/dataWrite/lib/old2NewTable.jar:.:/opt/soft/jdk1.6.0_22/lib/dt.jar:/opt/soft/jdk1.6.0_22/lib/tools.jar:/opt/tomcat1/lib/:/opt/tomcat4:/home/lbs/dataWrite com.ctfo.core.DataWriteCore start JVM_LOG_DIR=/logs/dataWrite/jvmlog/ Create JVM_LOG /logs/dataWrite/jvmlog/jvm-2016-08-19-15-47.log Starting dataWrite server... dataWrite initialized! [lbs@CnBJYwz01QkcptsuppL01 dataWrite]$ [2016-08-19 15:47:09 076] - [INFO ] - (DataWriteCore.java:37)     - 开始... insert into TH_VEHICLE_ALARM_comp (alarm_id, vid, utc, lat, lon, maplat, maplon, elevation, direction, gps_speed, alarm_code, sysutc, alarm_status, alarm_hperson, alarm_htime, alarm_mem, alarm_start_utc, alarm_end_utc, alarm_handler_status, alarm_driver, mileage, oil_total, end_lat, end_lon, end_maplon, end_maplat, end_elevation, end_direction, end_gps_speed, end_mileage, end_oil_total, vehicle_no, alarm_todo, alarm_src, alarm_handler_status_type, bglevel, signstatus, extendstatus, basestatus, alarm_add_info_start, alarm_add_info_end, overspeed_info_st, entering_area_st, out_route_st, overspeed_info_ed, entering_area_ed, out_route_ed, alarm_add_info, area_id, max_rpm, speed_threshold, handler_methods, max_speed, avg_speed, team_id, team_name, corp_id, corp_name, media_uri, driver_name, driver_id, driver_src) values (?, '6142866312156124479308', ?, 23944595, 69840789, 23945442, 69844536, 0, 0, 0, '7', 1467874919154, '0', null, null, null, 1452409421000, 1452655541000, 0, null, 100, -1, 23944543, 69840609, 69844360, 23945387, 0, 0, 16, 100, -1, '京B12984', null, 1, -1, null, null, null, '524291', null, null, null, null, null, null, null, null, null, null, 0, null, null, 16, 0, '72919763307600711121', '未分队车辆', '614889763307527741121', '胎压测试转组', null, null, null, '-1') 50000 1000 [2016-08-19 15:49:01 323] - [INFO ] - (DataWriteCore.java:45)     - 共耗时 112241 毫秒 [2016-08-19 15:49:01 324] - [INFO ] - (DataWriteCore.java:46)     - 结束

在对插入速度的对比中，压缩表的插入速度为非压缩表的2倍左右。插入为java程序，可能存在误差。

5 占用空间对比

SQL> select sum(bytes/1024/1024) M,segment_name from user_segments where segment_name like 'TH_VEHICLE_ALARM_%' group by segment_name;          M SEGMENT_NAME ---------- ---------------------------------------------------------------------------------       2280 TH_VEHICLE_ALARM_COMP       2944 TH_VEHICLE_ALARM_NOMAL

其他：

1 对于OLTP类型的压缩，如果为新添加的列指定默认值，则该列必须被设置为 NOT NULL，为可以为空的列添加默认值不被支持。对于这一点，我认为文档的描述是错误的，原文是这样的： OLTP compression - If a default value is specified for an added column, then the column must be NOT NULL . Added nullable columns with default values are not supported.   下面通过一个实验来验证 ：

SQL> alter table employees compress for oltp;

Table altered.

SQL> alter table employees add (c int default 10);

Table altered.

2 对于compression basic类型的压缩，不支持列的删除操作，例如：

SQL> alter table employees compress basic;

Table altered.

SQL> alter table employees drop column c;

alter table employees drop column c

                                  *

ERROR at line 1:

ORA-39726: unsupported add/drop column operation on compressed tables

3 对于OLTP类型的压缩，能够支持列的删除操作，但是在数据库内部将列标记为unused状态，避免长时间的解压和重新压缩的操作。

SQL> alter table employees compress for oltp;

Table altered.

SQL> alter table employees drop column c;

Table altered

注： 其他的内容载录自http://blog.itpub.net/29515435/viewspace-1128770/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28572479/viewspace-2123940/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28572479/viewspace-2123940/