Shulou(Shulou.com)06/01 Report--

Usually we can use either the COMPRESS clause or the COMPRESS BASIC clause to enable the basic compression features of the table (the COMPRESS clause and the COMPRESS BASIC clause do the same). From a performance perspective, the main advantage of using basic compression is that if the data is compressed and then loaded, any subsequent I _ COMPRESS BASIC O operation will use fewer resources because fewer blocks of data need to be accessed when reading and writing data. In general, tables that store large amounts of character data are suitable for basic compression. The basic compression properties of Oracle have no effect on normal DML statements such as INSERT, UPDATE, MERGE, and DELETE. If you want to work on all DML statements, consider using OLTP compression. The COMPRESS FOR OLTP clause enables compression for all DML operations. OLTP compression does not compress immediately when data is inserted or modified, but is compressed in batches when modifications in the data block reach a certain threshold. When the threshold is reached, all uncompressed data rows are compressed at the same time. The threshold for compression is determined by an internal algorithm (which you can't control).

You can also specify basic compression at the partition and tablespace levels. Basic compression is enabled by default for all tables in a tablespace created with the COMPRESS clause.

You can query the compression of the corresponding DBA/ALL/USER_TABLES view to verify that compression is enabled for a table. Query the tab_compression and compress_for columns in the dba_tablespace view to check that the tablespace has compression enabled.

Create a compression table:

Create table regs (reg_id number,reg_name varchar2 (2000)) compress

Create table regs (reg_id number,reg_name varchar2 (2000)) compress for oltp

Compression table (later data enabled):

SQL > alter table regs_dss compress

SQL > alter table regs compress for oltp

Compress the data already in the table:

SQL > alter table regs_dss move compress

SQL > alter table regs_dss move compress for oltp

Unzip:

SQL > alter table regs_dss nocompress

SQL > alter table regs_dss nocompress for oltp

Decompress the data already in the table:

SQL > alter table regs_dss move nocompress

SQL > alter table regs_dss move nocompress for oltp

Create a compressed tablespace

CREATE TABLESPACE comp_data

DATAFILE 1/oraOl/dbf Ile/0liR2/comp_dataOl.dbf'

SIZE 500M

EXTENT MANAGEMENT LOCAL

UNIFORM SIZE 512K

SEGMENT SPACE MANAGEMENT AUTO

DEFAULT COMPRESS;/ DEFAULT COMPRESS for oltp

Modify the existing tablespace to set its default degree of compression:

SQL > alter tablespace comp_data default compress

1) create a test table and insert data

SQL > CREATE TABLE t (ID NUMBER,addtime DATE,mark varchar2 (10) default null)

SQL > BEGIN

FOR i IN 1.. 60000

LOOP

INSERT INTO t (id,addtime)

VALUES (I, sysdate)

COMMIT

-DBMS_LOCK.sleep (0.1)

END LOOP

END

/

2) View object size

SQL > select segment_name, bytes/1024/1024 from dba_segments where owner='ADMIN'

SEGMENT_NAME BYTES/1024/1024

--

CHAINED_ROWS. 0625

T 5.8125

3) create using the table compression feature

SQL > create table t_com compress as select * from t

SQL > select segment_name, bytes/1024/1024 from dba_segments where owner='ADMIN'

SEGMENT_NAME BYTES/1024/1024

--

T_COM 3

CHAINED_ROWS. 0625

T 5.8125

T_com enables compression to reduce the amount of data by nearly half that of t.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.