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/