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

— 1. 创建压缩索引
CREATE INDEX sales_customer_idx ON fgsales(customer_id) COMPRESS;– 2. 创建压缩索引（指定前缀长度）
CREATE INDEX sales_date_customer_idx ON fgsales(sale_date, customer_id) COMPRESS 1;– 3. 为现有索引启用压缩
ALTER INDEX sales_customer_idx REBUILD COMPRESS;– 4. 查看索引的压缩状态
SELECT index_name, compression FROM user_indexes WHERE table_name = ‘SALES’;– 5. 监控索引大小
SELECT index_name, blevel, leaf_blocks, distinct_keys FROM user_indexes WHERE table_name = ‘SALES’;– 6. 压缩分区索引
ALTER INDEX sales_partitioned_idx REBUILD PARTITION p2023 COMPRESS;– 7. 创建位图索引并启用压缩
CREATE BITMAP INDEX sales_status_idx ON fgsales(status) COMPRESS;– 8. 创建函数索引并启用压缩
CREATE INDEX sales_month_idx ON fgsales(TO_CHAR(sale_date, ‘YYYY-MM’)) COMPRESS;– 9. 重建索引以释放空间
ALTER INDEX sales_customer_idx REBUILD COMPRESS;– 10. 监控索引使用情况
SELECT index_name, usage_count FROM v$object_usage WHERE index_name = ‘SALES_CUSTOMER_IDX’;

SQL> SELECT index_name, compression FROM user_indexes WHERE table_name = ‘SALES’;INDEX_NAME COMPRESS
—————————— ——–
SALES_CUSTOMER_IDX ENABLED
SALES_DATE_IDX DISABLED

— 1. 创建带LOB压缩的表
CREATE TABLE documents (
doc_id NUMBER,
doc_name VARCHAR2(100),
doc_content CLOB,
doc_image BLOB
) LOB(doc_content) STORE AS (COMPRESS)
LOB(doc_image) STORE AS (COMPRESS HIGH);– 2. 为现有LOB列启用压缩
ALTER TABLE documents MODIFY LOB(doc_content) (COMPRESS);– 3. 修改LOB压缩级别
ALTER TABLE documents MODIFY LOB(doc_image) (COMPRESS MEDIUM);– 4. 查看LOB列的压缩状态
SELECT column_name, segment_name, compression FROM user_lobs WHERE table_name = ‘DOCUMENTS’;– 5. 监控LOB存储空间
SELECT segment_name, bytes, blocks FROM user_segments WHERE segment_name IN (
SELECT segment_name FROM user_lobs WHERE table_name = ‘DOCUMENTS’
);– 6. 压缩现有LOB数据
ALTER TABLE documents MOVE LOB(doc_content) STORE AS (COMPRESS);– 7. 为分区表的LOB列启用压缩
CREATE TABLE documents_partitioned (
doc_id NUMBER,
doc_date DATE,
doc_content CLOB
) PARTITION BY RANGE (doc_date) (
PARTITION p2023 VALUES LESS THAN (TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2024 VALUES LESS THAN (TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’))
) LOB(doc_content) STORE AS (COMPRESS);– 8. 压缩分区表的LOB数据
ALTER TABLE documents_partitioned MOVE PARTITION p2023 LOB(doc_content) STORE AS (COMPRESS);– 9. 测试LOB压缩效果
INSERT INTO documents VALUES (1, ‘Test Document’, RPAD(‘X’, 10000, ‘X’), NULL);COMMIT;– 10. 查看LOB压缩前后的大小
SELECT segment_name, bytes FROM user_segments WHERE segment_name IN (
SELECT segment_name FROM user_lobs WHERE table_name = ‘DOCUMENTS’
);

SQL> SELECT column_name, segment_name, compression FROM user_lobs WHERE table_name = ‘DOCUMENTS’;COLUMN_NAME SEGMENT_NAME COMPRESS
—————————— ————————- ——–
DOC_CONTENT SYS_LOB0000100008C00003$$ ENABLED
DOC_IMAGE SYS_LOB0000100008C00004$$ ENABLED

— 案例：为数据仓库系统配置数据压缩

— 1. 检查当前表大小
SELECT table_name, num_rows, blocks * 8192 / 1024 / 1024 AS size_mb
FROM user_tables
WHERE table_name = ‘SALES_HISTORY’;– 2. 估算压缩率
DECLARE
comp_ratio NUMBER;blks_compressed NUMBER;blks_uncompressed NUMBER;BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
owner_name => ‘DW’,
table_name => ‘SALES_HISTORY’,
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
);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 sales_history MOVE COMPRESS FOR QUERY HIGH;– 4. 为相关索引启用压缩
ALTER INDEX sales_history_product_idx REBUILD COMPRESS;ALTER INDEX sales_history_date_idx REBUILD COMPRESS;– 5. 检查压缩后的表大小
SELECT table_name, num_rows, blocks * 8192 / 1024 / 1024 AS size_mb
FROM user_tables
WHERE table_name = ‘SALES_HISTORY’;– 6. 测试查询性能
SET TIMING ON
SELECT product_id, SUM(amount)
FROM sales_history
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

— 7. 监控压缩表的访问情况
SELECT table_name, logical_reads, physical_reads, row_accesses
FROM v$segment_statistics
WHERE owner = ‘DW’ AND object_name = ‘SALES_HISTORY’;– 8. 备份压缩表
BACKUP TABLE dw.sales_history TO ‘/backup/sales_history.dmp’;– 9. 验证备份大小
SELECT BYTES / 1024 / 1024 AS backup_size_mb
FROM dba_data_files
WHERE tablespace_name = ‘DW_DATA’;– 10. 总结压缩效果
— 比较压缩前后的存储空间、查询性能和备份大小

— 1. 创建测试表
CREATE TABLE test_compression (
id NUMBER,
name VARCHAR2(100),
description VARCHAR2(1000),
value NUMBER,
create_date DATE
);– 2. 插入测试数据
INSERT INTO test_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_COMPRESSION';-- 4. 测试未压缩表的查询性能 SET TIMING ON SELECT COUNT(*) FROM test_compression WHERE value > 5000000;SELECT AVG(value) FROM test_compression GROUP BY TO_CHAR(create_date, ‘YYYY-MM’);SET TIMING OFF

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

— 8. 测试DML操作性能
SET TIMING ON
UPDATE test_compression SET value = value * 1.1 WHERE id <= 10000;DELETE FROM test_compression WHERE id > 90000;INSERT INTO test_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_COMPRESSION' AND ROWNUM = 1) /(SELECT blocks FROM user_tables WHERE table_name = 'TEST_COMPRESSION' AND ROWNUM = 1) AS compression_ratio, 0 AS storage_saved_percent FROM dual;-- 10. 清理测试数据 DROP TABLE test_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;