Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand Oracle table compression". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. The official document says:

As your database grows in size, consider using table compression. Compression saves disk space, reduces memory use in the database buffer cache, and can significantly speed query execution during reads. Compression has a cost in CPU overhead for data loading and DML. However, this cost might be offset by reduced I/O requirements

As databases grow, you can consider using table compression. Compression can save disk space, reduce database buffer cache memory usage, and speed up queries.

Compression has some CPU consumption for data loading and DML operations. However, these consumption can be offset by a decrease in Ithumb O.

Table compression is completely transparent to applications, and is beneficial to decision support systems (DSS), online transaction processing systems (OLTP), and archiving systems (Archive Systems).

We can compress tablespaces, tables and partitions. If the tablespace is compressed, all tables created on the tablespace will be compressed by default. Data compression is performed only when the table performs an insert, update, or bulk data load.

I. the method of table compression

Oracle provides four types of table compression methods:

1. Basic Compression: compression level High,CPU has less overhead

2. OLTP Compression: compression level High,CPU has less overhead

3. Warehouse Compression: compression level Higher,CPU is expensive (depending on whether the compression parameter is Low or High)

4. Archive Compression: compression level Highest,CPU is very expensive (depending on whether the compression parameter is Low or High)

When Basic Compression,warehouse Compression,Archive Compression type compression is used, compression is performed only when bulk data import occurs.

OLTP Compression is used in online transaction processing systems to perform data compression for arbitrary SQL operations.

Warehouse Compression and Archive Compression can achieve high compression levels because they use Hybrid Columnar (mixed column) compression technology, and Hybrid Columnar uses an improved column storage form instead of row-based storage. Hybird Columnar technology allows the same data to be stored together, which improves the efficiency of the compression algorithm. When using the hybrid column compression algorithm, it will result in more CPU overhead, so this compression technique is suitable for data that is updated infrequently.

Second, the basic compression characteristics:

1) using basic compression, compression occurs only if the data is directly inserted or updated into the record (direct-path insert and updated).

And supports wired data types and SQL operations.

Second, how to enable basic compression?

1) specify the compress condition in the create table statement.

2) through alter table.. Compress; to enable compression for existing tables

3) through alter table.. Nocompress; to disable table compression

Add and delete columns on the compressed table

Adding and deleting columns on a compressed table has the following restrictions:

1. For compression basic type compression, you cannot specify default values for newly added columns

-- testing process:

TEST@ R7 > select * from empd1;no rows selectedTEST@ R7 > select table_name,compression,compress_for from user_tables where table_name='EMPD1';TABLE_NAME COMPRESS COMPRESS_FOR-- EMPD1 DISABLEDTEST@ R7 > INSERT INTO EMPD1 SELECT * FROM SCOTT.EMP WHERE ROWNUMCOMMIT Commit complete.TEST@ R7 > alter table empd1 compress basic;Table altered.TEST@ R7 > select table_name,compression,compress_for from user_tables TABLE_NAME COMPRESS COMPRESS_FOR---TBUSINFLAG ENABLED OLTPTCOMBI ENABLED OLTPTCURRENTS ENABLED OLTPTENTRUSTDIRECTION ENABLED OLTPTINSTRUCTION ENABLED OLTPTINSTRUCTIONSTOCK ENABLED OLTPTOPERATOR ENABLED OLTPTOUTCONFIRM ENABLED OLTPTREALDEAL ENABLED OLTPTSTOCKINFO ENABLED OLTPTUNITSTOCK ENABLED OLTPCUSTOMERS ENABLED OLTPSYS_EXPORT_FULL_01 ENABLED OLTPA ENABLED OLTPB ENABLED OLTPEMPD1 ENABLED BASIC16 rows selected.TEST@ R7 > desc empd1 Name Null? Type-EMPNO NUMBER (4) ENAME VARCHAR2 (10) JOB VARCHAR2 (9) MGR NUMBER (4) HIREDATE DATE SAL NUMBER (7Magne2) COMM NUMBER (7Magne2) DEPTNO NUMBER (2)-add fields with default values: TEST@ R7 > alter table empd1 add (test varchar (10) default 'sysdate') is not supported Alter table empd1 add (test varchar (10) default 'sysdate') * ERROR at line 1:ORA-39726: unsupported add/drop column operation on compressed tables-- does not have a default value field: TEST@ R7 > alter table empd1 add (test varchar (10)) is supported; for compression basic compression, Table altered.-- does not support column deletion operation TEST@ R7 > alter table empd1 drop column test Alter table empd1 drop column test * ERROR at line 1:ORA-39726: unsupported add/drop column operation on compressed tables

2. For OLTP type compression, if you specify a default value for the newly added column, the column must be set to NOT NULL, and adding default values for columns that can be empty is not supported.

/ * the original text is as follows: 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. , /

For this, do a test. For OLTP type compression, the column null can also be added.

TEST@ R7 > alter table empd1 add (test1 varchar (10) default 'sysdate'); Table altered.

For OLTP type compression, column deletion can be supported, but columns are marked as unused state within the database to avoid long decompression and recompression operations.

TEST@ R7 > alter table empd1 drop column test;Table altered.SQL > ALTER TABLE EMPD1 DROP UNUSED COLUMNS;-- deleting the unused field "how to understand Oracle table compression" ends here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.