1. 大型表/索引数据压缩设计与维护
1.1 如何对普通表进行压缩
如果对一些较为固定历史数据采取压缩方法存储,即可以节省存储空间,也可提高处理速度。表压缩技术方面,oracle
11g/12c给出新压缩方法,用create table带compress子句完成对表压缩描述。
scott@ORCL> create table sales_history_comp(part_id
varchar2(50) not null,store_id varchar2(50) not null,sale_date date
not null,quantity number(10,2) not null) compress;
Table created.
Elapsed: 00:00:00.75
scott@ORCL> select table_name,compression from
user_tables;
TABLE_NAME COMPRESSION
------------------------------------------------------------
----------------
DEPT DISABLED
EMP DISABLED
BONUS DISABLED
SALGRADE DISABLED
NO_EMP DISABLED
SPERRORLOG DISABLED
INSURED_AUTOS DISABLED
UNIT_INF DISABLED
PAY_LST_DET DISABLED
T_DEPT DISABLED
T_EMP DISABLED
EMP_COPY DISABLED
DEPT_COPY DISABLED
SALES_HISTORY_COMP ENABLED
TMP_EMP DISABLED
15 rows selected.
Elapsed: 00:00:00.33
scott@ORCL>
1.2 如何对分区表进行行压缩
如果分区表的数据量仍然很大,可以考虑对分区表进行压缩存储,oracle使用create
table带compress或alter table add partition带compress子句实现
--create table
scott@ORCL> create table t1(c01 number,c02 varchar2(200))
partition by range(c01)(partition p1 values less than(100)
compress,partition p2 values less than(200) compress);
Table created.
Elapsed: 00:00:00.21
--alter table add partition
scott@ORCL> alter table t1 add partition p3 values less
than(300) compress;
Table altered.
Elapsed: 00:00:00.08
--此外可以通过alter table实现分区表的压缩或者不压缩,比如
scott@ORCL> alter table t1 modify partition p3
nocompress;
Table altered.
Elapsed: 00:00:00.03
scott@ORCL>
1.4 如何创建一个压缩索引
如果某个索引的数据存储量很大,采用压缩方法也是一个很好的选择。可以用create
index带compress子句或者采用alter带compresss子句实现压缩
语法:
--create index语句
create index on (col1,col2,...coln) compresss [n];
--alter index语句
alter index rebulid compress [n];
create index ix_test on test(object_name,object_type) pctfree
0 compress 1,
1.5 如何进行分区索引数据压缩
如果某个索引的数据量较大,设计时可以考虑索引分区和压缩。但是要在已经存在的索引上通过alter
index实现分区索引的压缩是不允许的,必须先删除原来索引,然后再用create index语句创建。
下面是错误的:
alter index IDX_RANG_SALES_PROD_ID rebuild partition
SALES_Q1_2007 compress;
下面做法是正确的
drop index IDX_RANGE_SALES_PROD_ID;
create index IDX_RANGE_SALES_PROD_ID ON RANGE_SALES(PROD_ID)
LOCAL COMPRESS;
1.6 如何创建OLTP压缩表
如果你的应用表属于OLTP应用,相关的表的数据可采取OLTP压缩方法存储。
scott@ORCL> create table emp1(emp_id number,first_name
varchar2(128),last_name varchar2(128)) compress for OLTP;
Table created.
Elapsed: 00:00:00.05
sys@ORCL> create table tab_oltp compress for OLTP as select
* from dba_objects;
Table created.
Elapsed: 00:00:00.33
sys@ORCL>
alter table...move compress for OLTP;
1.7如何创建压缩实体视图
create materialized view mv_sales_comp compress as
select.........
说明:
实体化视图压缩是否有必要,关键要看产生的结果集是否很大,如果数据量很大,考虑用压缩式必要的,否则,就不要采用压缩创建。
1.8 如何建立ILM压缩表
ILM是信息生命期(information lifecycle management),是oracle
12c版本提供的新功能之一。采用ILM可以对信息生命周期进行有效和完善的数据存储。如果oracle12c的数据库数据量很大,并且有部分的数据总是处于“热数据、温数据和冷数据”几个阶段。这样可以考虑将那些“冷”数据采用数据压缩或分区数据压缩处理。oracle用create
table时增加ILM ADD POLICY COMPRESS子句实现
--适用版本:12c(非CDB)
--语句1:create table 上加 ILM ADD POLICY 子句
create tabble sales
(PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
QUANTITY_SOLD BINARY_DOUBLE NOT NULL,
AMOUNT_SOLD BINARY_DOUBLE NOT NULL)
ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH
SEGMENT
AFTER 6 MONTHS OF NO ACCESS
TABLESPACE USERS STORAGE(INITIAL 4M NEXT 2M);
---以实现将sales表定义为6改月后不被