SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name
= ‘SALES’;TABLE_NAME COMPRESS COMPRESS_FOR
—————————— ——– ————
SALES ENABLED DIRECT LOAD ONLY

— 1. 创建高级行压缩表
CREATE TABLE sales_advanced_compress (
sale_id NUMBER,
sale_date DATE,
customer_id NUMBER,
amount NUMBER
) COMPRESS FOR OLTP;– 2. 为现有表启用高级行压缩
ALTER TABLE fgsales MODIFY COMPRESS FOR OLTP;– 3. 压缩现有数据
ALTER TABLE fgsales MOVE COMPRESS FOR OLTP;– 4. 查看表的压缩状态
SELECT table_name, compression, compress_for FROM user_tables WHERE table_name = ‘SALES’;– 5. 估算压缩率
EXEC DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
owner_name => ‘SCOTT’,
table_name => ‘SALES’,
partition_name => NULL,
compression_type => DBMS_COMPRESSION.COMPRESS_FOR_OLTP,
subset_factor => 1,
out_comp_ratio => :comp_ratio,
out_blks_compressed => :blks_compressed,
out_blks_uncompressed => :blks_uncompressed
);– 6. 为分区表启用高级行压缩
CREATE TABLE sales_partitioned (
sale_id NUMBER,
sale_date DATE,
customer_id NUMBER,
amount NUMBER
) PARTITION BY RANGE (sale_date) (
PARTITION p2023 VALUES LESS THAN (TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’)) COMPRESS FOR OLTP,
PARTITION p2024 VALUES LESS THAN (TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’)) COMPRESS FOR OLTP
);– 7. 压缩分区
ALTER TABLE sales_partitioned MOVE PARTITION p2023 COMPRESS FOR OLTP;– 8. 测试DML操作
UPDATE sales_advanced_compress SET amount = amount * 1.1 WHERE customer_id = 1001;DELETE FROM sales_advanced_compress WHERE sale_date < TO_DATE('2022-01-01', 'YYYY-MM-DD');INSERT INTO sales_advanced_compress VALUES (10001, SYSDATE, 1002, 5000);-- 9. 监控压缩表的性能 SELECT table_name, num_rows, blocks, empty_blocks FROM user_tables WHERE table_name = 'SALES_ADVANCED_COMPRESS';-- 10. 验证压缩效果 SELECT table_name, blocks * 8192 / 1024 / 1024 AS size_mb FROM user_tables WHERE table_name IN ('SALES', 'SALES_ADVANCED_COMPRESS');

SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name
= ‘SALES’;TABLE_NAME COMPRESS COMPRESS_FOR
—————————— ——– ————
SALES ENABLED OLTP

— 1. 创建混合列压缩表（查询高压缩）
CREATE TABLE sales_hybrid_compress (
sale_id NUMBER,
sale_date DATE,
customer_id NUMBER,
amount NUMBER
) COMPRESS FOR QUERY HIGH;– 2. 创建混合列压缩表（查询中压缩）
CREATE TABLE sales_hybrid_compress_medium (
sale_id NUMBER,
sale_date DATE,
customer_id NUMBER,
amount NUMBER
) COMPRESS FOR QUERY LOW;– 3. 创建混合列压缩表（归档高压缩）
CREATE TABLE sales_hybrid_compress_archive (
sale_id NUMBER,
sale_date DATE,
customer_id NUMBER,
amount NUMBER
) COMPRESS FOR ARCHIVE HIGH;– 4. 为现有表启用混合列压缩
ALTER TABLE fgsales MODIFY COMPRESS FOR QUERY HIGH;– 5. 压缩现有数据
ALTER TABLE fgsales MOVE COMPRESS FOR QUERY HIGH;– 6. 查看表的压缩状态
SELECT table_name, compression, compress_for FROM user_tables WHERE table_name = ‘SALES’;– 7. 估算压缩率
EXEC DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
owner_name => ‘SCOTT’,
table_name => ‘SALES’,
partition_name => NULL,
compression_type => DBMS_COMPRESSION.COMPRESS_FOR_QUERY_HIGH,
subset_factor => 1,
out_comp_ratio => :comp_ratio,
out_blks_compressed => :blks_compressed,
out_blks_uncompressed => :blks_uncompressed
);– 8. 为分区表启用混合列压缩
CREATE TABLE sales_partitioned (
sale_id NUMBER,
sale_date DATE,
customer_id NUMBER,
amount NUMBER
) PARTITION BY RANGE (sale_date) (
PARTITION p2023 VALUES LESS THAN (TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’)) COMPRESS FOR QUERY HIGH,
PARTITION p2024 VALUES LESS THAN (TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’)) COMPRESS FOR QUERY LOW
);– 9. 压缩分区
ALTER TABLE sales_partitioned MOVE PARTITION p2023 COMPRESS FOR QUERY HIGH;– 10. 测试查询性能
SET TIMING ON
SELECT product_id, SUM(amount)
FROM sales_hybrid_compress
WHERE sale_date BETWEEN TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2023-12-31’, ‘YYYY-MM-DD’)
GROUP BY product_id
ORDER BY SUM(amount) DESC;SET TIMING OFF

SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name
= ‘SALES’;TABLE_NAME COMPRESS COMPRESS_FOR
—————————— ——– ————
SALES ENABLED QUERY HIGH

— 案例：为企业应用系统配置表压缩

— 1. 检查当前表大小
SELECT table_name, num_rows, blocks * 8192 / 1024 / 1024 AS size_mb
FROM user_tables
WHERE table_name = ‘CUSTOMER_ORDERS’;– 2. 估算压缩率
DECLARE
comp_ratio NUMBER;blks_compressed NUMBER;blks_uncompressed NUMBER;BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
owner_name => ‘APP’,
table_name => ‘CUSTOMER_ORDERS’,
partition_name => NULL,
compression_type => DBMS_COMPRESSION.COMPRESS_FOR_OLTP,
subset_factor => 1,
out_comp_ratio => comp_ratio,
out_blks_compressed => blks_compressed,
out_blks_uncompressed => blks_uncompressed
);DBMS_OUTPUT.PUT_LINE(‘Compression ratio: ‘ || comp_ratio);DBMS_OUTPUT.PUT_LINE(‘Blocks compressed: ‘ || blks_compressed);DBMS_OUTPUT.PUT_LINE(‘Blocks uncompressed: ‘ || blks_uncompressed);END;/– 3. 为表启用高级行压缩
ALTER TABLE customer_orders MOVE COMPRESS FOR OLTP;– 4. 为相关索引重建
ALTER INDEX customer_orders_customer_idx REBUILD;ALTER INDEX customer_orders_date_idx REBUILD;– 5. 检查压缩后的表大小
SELECT table_name, num_rows, blocks * 8192 / 1024 / 1024 AS size_mb
FROM user_tables
WHERE table_name = ‘CUSTOMER_ORDERS’;– 6. 测试查询性能
SET TIMING ON
SELECT customer_id, COUNT(*), SUM(order_amount)
FROM customer_orders
WHERE order_date BETWEEN TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2023-12-31’, ‘YYYY-MM-DD’)
GROUP BY customer_id
ORDER BY SUM(order_amount) DESC;SET TIMING OFF

— 7. 测试DML操作性能
SET TIMING ON
UPDATE customer_orders SET order_status = ‘COMPLETED’ WHERE order_id <= 1000;DELETE FROM customer_orders WHERE order_date < TO_DATE('2022-01-01', 'YYYY-MM-DD');INSERT INTO customer_orders VALUES (100001, 1001, SYSDATE, 2500, 'PENDING');COMMIT;SET TIMING OFF -- 8. 监控压缩表的访问情况 SELECT table_name, logical_reads, physical_reads, row_accesses FROM v$segment_statistics WHERE owner = 'APP' AND object_name = 'CUSTOMER_ORDERS';-- 9. 备份压缩表 BACKUP TABLE app.customer_orders TO '/backup/customer_orders.dmp';-- 10. 总结压缩效果 -- 比较压缩前后的存储空间、查询性能和DML性能

— 1. 创建测试表
CREATE TABLE test_table_compression (
id NUMBER,
name VARCHAR2(100),
description VARCHAR2(1000),
value NUMBER,
create_date DATE
);– 2. 插入测试数据
INSERT INTO test_table_compression
SELECT
rownum,
‘Product ‘ || rownum,
RPAD(‘Description ‘, 1000, ‘X’),
rownum * 100,
SYSDATE – rownum/1000
FROM dual
CONNECT BY rownum <= 100000;COMMIT;-- 3. 检查未压缩表的大小 SELECT table_name, blocks * 8192 / 1024 / 1024 AS size_mb FROM user_tables WHERE table_name = 'TEST_TABLE_COMPRESSION';-- 4. 测试未压缩表的查询性能 SET TIMING ON SELECT COUNT(*) FROM test_table_compression WHERE value > 5000000;SELECT AVG(value) FROM test_table_compression GROUP BY TO_CHAR(create_date, ‘YYYY-MM’);SET TIMING OFF

— 5. 为表启用高级行压缩
ALTER TABLE test_table_compression MOVE COMPRESS FOR OLTP;– 6. 检查压缩后表的大小
SELECT table_name, blocks * 8192 / 1024 / 1024 AS size_mb
FROM user_tables
WHERE table_name = ‘TEST_TABLE_COMPRESSION’;– 7. 测试压缩表的查询性能
SET TIMING ON
SELECT COUNT(*) FROM test_table_compression WHERE value > 5000000;SELECT AVG(value) FROM test_table_compression GROUP BY TO_CHAR(create_date, ‘YYYY-MM’);SET TIMING OFF

— 8. 测试DML操作性能
SET TIMING ON
UPDATE test_table_compression SET value = value * 1.1 WHERE id <= 10000;DELETE FROM test_table_compression WHERE id > 90000;INSERT INTO test_table_compression
SELECT
rownum + 100000,
‘Product ‘ || (rownum + 100000),
RPAD(‘Description ‘, 1000, ‘X’),
(rownum + 100000) * 100,
SYSDATE – (rownum + 100000)/1000
FROM dual
CONNECT BY rownum <= 10000;COMMIT;SET TIMING OFF -- 9. 分析压缩效果 SELECT 'Before Compression' AS status, 0 AS compression_ratio, 0 AS storage_saved_percent FROM dual UNION ALL SELECT 'After Compression' AS status, (SELECT blocks FROM user_tables WHERE table_name = 'TEST_TABLE_COMPRESSION' AND ROWNUM = 1) /(SELECT blocks FROM user_tables WHERE table_name = 'TEST_TABLE_COMPRESSION' AND ROWNUM = 1) AS compression_ratio, 0 AS storage_saved_percent FROM dual;-- 10. 清理测试数据 DROP TABLE test_table_compression;

— 1. 压缩后性能下降
— 问题：启用压缩后查询性能下降
— 解决方案：
— 1.1 检查压缩类型是否适合工作负载
SELECT table_name, compression, compress_for FROM user_tables WHERE table_name = ‘SALES’;– 1.2 考虑使用不同的压缩类型
ALTER TABLE fgsales MOVE COMPRESS FOR OLTP;– 1.3 确保统计信息是最新的
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘SALES’);– 2. 压缩率不理想
— 问题：压缩率低于预期
— 解决方案：
— 2.1 分析数据特性
SELECT column_name, data_type, num_distinct, density
FROM user_tab_col_statistics
WHERE table_name = ‘SALES’;– 2.2 尝试不同的压缩类型
ALTER TABLE fgsales MOVE COMPRESS FOR QUERY HIGH;– 2.3 检查是否有大量唯一值
SELECT COUNT(DISTINCT customer_id) FROM fgsales;– 3. DML操作变慢
— 问题：启用压缩后DML操作性能下降
— 解决方案：
— 3.1 考虑使用高级行压缩
ALTER TABLE fgsales MOVE COMPRESS FOR OLTP;– 3.2 分批执行大型DML操作
DECLARE
CURSOR c_sales IS SELECT id FROM fgsales WHERE status = ‘INACTIVE’;TYPE id_tab IS TABLE OF fgsales.id%TYPE;l_ids id_tab;l_batch_size NUMBER := 1000;BEGIN
OPEN c_sales;LOOP
FETCH c_sales BULK COLLECT INTO l_ids LIMIT l_batch_size;EXIT WHEN l_ids.COUNT = 0;FORALL i IN 1..l_ids.COUNT
UPDATE fgsales SET status = ‘ARCHIVED’ WHERE id = l_ids(i);COMMIT;END LOOP;CLOSE c_sales;END;/– 4. 备份时间增加
— 问题：启用压缩后备份时间增加
— 解决方案：
— 4.1 使用RMAN压缩备份
BACKUP AS COMPRESSED BACKUPSET DATABASE;– 4.2 考虑增量备份策略
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘incr_backup’ DATABASE;– 5. 压缩功能不可用
— 问题：无法启用某些压缩特性
— 解决方案：
— 5.1 检查Oracle版本和许可
SELECT * FROM v$version;– 5.2 检查数据库兼容性
SELECT name, value FROM v$parameter WHERE name = ‘compatible’;– 5.3 确保表空间支持压缩
SELECT tablespace_name, block_size FROM dba_tablespaces;