某银行客户问题
测试结论
完成此任务需要你具备的技能
测试过程
COMPRESSION
| VARCHAR2(8) | Indicates whether table compression is enabled (ENABLED) or not (DISABLED); NULL for partitioned tables | |
COMPRESS_FOR | VARCHAR2(12) | Default compression for what kind of operations: |
table_compression
The table_compression clause is valid only for heap-organized tables. Use this clause to instruct the database whether to compress data segments to reduce disk use. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default.
-
When you enable table compression by specifying either COMPRESS or COMPRESS BASIC, you enable basic table compression. Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format.
Tables with COMPRESS or COMPRESS BASIC use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the physical_attributes_clause.
In earlier releases, this type of compression was called DSS table compression and was enabled using COMPRESS FOR DIRECT_LOAD OPERATIONS. This syntax has been deprecated.
See Also:
基于压缩表问题,经在11.2.0.1测试,结论如下:
1,正常情况下,采用insert /*+ append */ into t_test select * from 进行插入记录,表压缩是生效的
2,如果表以未压缩方式创建,可以采用alter table move compress变更为压缩表方式,此时数据库会压缩处理原有表记录
且表会变更为压缩表
3,经查MOS,发现文章
Basic Compression Is Not Applied When Inserting Into Remote Table (文档 ID 1390050.1)
大致意思为:insert /*+ append */ t_test@db_link select * from,远端库的压缩表t_test其压缩不会生效
MOS给出的解决方案有2个:
a,启用OLTP压缩,命令:alter table t_test compress for oltp;
b,对调SQL语句,即从远端库发起上述SQL,表压缩即可生效
4,关于压缩表相关操作限制条件:
- 不支持255个列以上表压缩
- 不支持对于LOB列的表压缩
- 如使用COMPESS BASIC模式,不用删除列,但却可以配置此列为不可用
- 不支持IOT表的表压缩
- 不支持外部表或集表的表压缩
- 不支持启用了闪回归档功能的表压缩
- 不支持含有LONG列的表压缩
相关阅读
个人简介
项目经验:
中国联通4G数据库性能分析与优化
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式:
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-1726705/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1726705/