为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:V8
【操作系统】:centos
【CPU】:x86_84
【问题描述】*:COMPRESS_MODE 设置成1或0,创建测试表,占用的空间大小一样,没感受到压缩作用。
SQL> show parameter COMPRESS_MODE;
LINEID PARA_NAME PARA_VALUE
---------- ------------- ----------
1 COMPRESS_MODE 0
SQL> drop table computers;
executed successfully
used time: 27.505(ms). Execute id is 746.
SQL> drop table computers_ys;
executed successfully
used time: 19.859(ms). Execute id is 747.
SQL>
SQL> show parameter COMPRESS_MODE;
LINEID PARA_NAME PARA_VALUE
---------- ------------- ----------
1 COMPRESS_MODE 0
create table computers
(
comNo number(10) constraint PK_comp primary key,
compModel varchar2(64) constraint unique_comp unique,
buyTime date,
price number(12,2) constraint ch_price check(
price>0 and price<=3000000),
owner varchar2(32));
executed successfully
used time: 5.805(ms). Execute id is 749.
SQL> DECLARE
i number := 0;
BEGIN
for i in 1 .. 300000 loop
insert into computers
(comNo, compModel, buyTime, price, owner)
values
(i, '8' + i, to_date('' + sysdate, 'YYYY-MM'), i, 'zhangsan');
--dbms_output.put_line(i);
end loop;
commit;
END;
11 12 13 14 /
DMSQL executed successfully
used time: 00:00:06.334. Execute id is 750.
alter system set 'COMPRESS_MODE' = 1 both;
DMSQL executed successfully
used time: 7.697(ms). Execute id is 751.
SQL> show parameter COMPRESS_MODE;
LINEID PARA_NAME PARA_VALUE
---------- ------------- ----------
1 COMPRESS_MODE 1
create table computers_ys
(
comNo number(10) constraint PK_comp2 primary key,
compModel varchar2(64) constraint unique_comp2 unique,
buyTime date,
price number(12,2) constraint ch_price2 check(
price>0 and price<=3000000),
owner varchar2(32));
DECLARE
i number := 0;
BEGIN
for i in 1 .. 300000 loop
insert into computers_ys
executed successfully
used time: 6.225(ms). Execute id is 753.
SQL> (comNo, compModel, buyTime, price, owner)
values
(i, '8' + i, to_date('' + sysdate, 'YYYY-MM'), i, 'zhangsan');
--dbms_output.put_line(i);
end loop;
commit;
END;
10 11 12 13 14 /
DMSQL executed successfully
used time: 00:00:06.904. Execute id is 754.
SELECT
S2.NAME AS 模式名,
S1.NAME AS 表名,
TABLE_USED_SPACE(S2.NAME,S1.NAME) * PAGE /1024.0/1024.0 AS "表占用空间(MB)"
FROM
SYSOBJECTS S1,
SYSOBJECTS S2
WHERE
S1.SCHID = S2.ID
AND S1."SUBTYPE$" = 'UTAB'
AND S2."TYPE$" = 'SCH'
ORDER BY
2 3 4 5 6 7 8 9 10 11 12 13 3 DESC;
LINEID 模式名 表名 表占用空间(MB)
---------- --------- --------------------------- -------------------
1 SYD COMPUTERS_YS 36.875
2 SYD COMPUTERS 36.875
3 SYS AQ$_QUEUES 0.625
4 SYSDBA T1 0.375
5 SYS AQ$_QUEUE_TABLES 0.25
6 SYS DBMS_LOCK_ALLOCATED 0.125
7 SYS POLICIES 0.125
8 SYS POLICY_COLS 0.125
9 SYS POLICY_CONTEXTS 0.125
10 SYS POLICY_GROUPS 0.125
11 SYSDBA TEST 0.125
12 SYS REG$ 0.125
13 SYSDBA TEST_TABLE 0.125
14 SYS DBMS_ALERT_INFO 0.125
15 SYSDBA ##PLAN_TABLE 0
16 SYS ##TMP_TBL_FOR_DBMS_LOB_CLOB 0
17 SYSDBA ##HISTOGRAMS_TABLE 0
18 SYS ##TMP_TBL_FOR_DBMS_LOB_BLOB 0
18 rows got
used time: 1.523(ms). Execute id is 755.
SQL>
普通表数据压缩功能已经取消,只是语法层面的支持。
列存表可以支持压缩,相关参数仅对列存表有效。