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

— 1. 创建带前缀压缩的复合索引（默认前缀长度）
CREATE INDEX sales_date_customer_idx ON fgsales(sale_date, customer_id) COMPRESS;– 2. 创建带指定前缀长度的复合索引
CREATE INDEX sales_date_customer_idx ON fgsales(sale_date, customer_id) COMPRESS 1;– 3. 为现有索引启用前缀压缩
ALTER INDEX sales_date_customer_idx REBUILD COMPRESS 1;– 4. 查看索引的压缩状态
SELECT index_name, compression, prefix_length FROM user_indexes WHERE table_name = ‘SALES’;– 5. 监控索引大小
SELECT index_name, blevel, leaf_blocks, distinct_keys FROM user_indexes WHERE table_name = ‘SALES’;– 6. 测试索引性能
SET TIMING ON
SELECT * FROM fgsales WHERE sale_date BETWEEN TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2023-12-31’, ‘YYYY-MM-DD’) AND customer_id = 1001;SET TIMING OFF

— 7. 重建带不同前缀长度的索引
ALTER INDEX sales_date_customer_idx REBUILD COMPRESS 2;– 8. 比较不同前缀长度的压缩效果
SELECT index_name, leaf_blocks, bytes / 1024 / 1024 AS size_mb
FROM user_indexes
WHERE table_name = ‘SALES’;– 9. 为多列复合索引启用前缀压缩
CREATE INDEX sales_customer_date_amount_idx ON fgsales(customer_id, sale_date, amount) COMPRESS 2;– 10. 监控索引使用情况
SELECT index_name, usage_count FROM v$object_usage WHERE index_name = ‘SALES_DATE_CUSTOMER_IDX’;

SQL> SELECT index_name, compression, prefix_length FROM user_indexes WHERE
table_name = ‘SALES’;INDEX_NAME COMPRESS PREFIX_LENGTH
—————————— ——– ————
SALES_DATE_CUSTOMER_IDX ENABLED 1
SALES_CUSTOMER_IDX ENABLED 1

— 1. 创建分区表
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’)),
PARTITION p2024 VALUES LESS THAN (TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’))
);– 2. 创建分区索引并启用压缩
CREATE INDEX sales_partitioned_date_idx ON sales_partitioned(sale_date) COMPRESS LOCAL;– 3. 为现有分区索引启用压缩
ALTER INDEX sales_partitioned_date_idx REBUILD PARTITION p2023 COMPRESS;– 4. 查看分区索引的压缩状态
SELECT index_name, partition_name, compression FROM user_ind_partitions WHERE index_name = ‘SALES_PARTITIONED_DATE_IDX’;– 5. 监控分区索引大小
SELECT index_name, partition_name, leaf_blocks, bytes / 1024 / 1024 AS size_mb
FROM user_ind_partitions
WHERE index_name = ‘SALES_PARTITIONED_DATE_IDX’;– 6. 测试分区索引性能
SET TIMING ON
SELECT * FROM sales_partitioned WHERE sale_date BETWEEN TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2023-12-31’, ‘YYYY-MM-DD’);SET TIMING OFF

— 7. 重建分区索引
ALTER INDEX sales_partitioned_date_idx REBUILD PARTITION p2023 COMPRESS;– 8. 为全局分区索引启用压缩
CREATE INDEX sales_partitioned_customer_idx ON sales_partitioned(customer_id) COMPRESS GLOBAL;– 9. 监控全局分区索引大小
SELECT index_name, leaf_blocks, bytes / 1024 / 1024 AS size_mb
FROM user_indexes
WHERE index_name = ‘SALES_PARTITIONED_CUSTOMER_IDX’;– 10. 测试全局分区索引性能
SET TIMING ON
SELECT * FROM sales_partitioned WHERE customer_id = 1001;SET TIMING OFF

SQL> SELECT index_name, partition_name, compression FROM user_ind_partitions WHERE
index_name = ‘SALES_PARTITIONED_DATE_IDX’;INDEX_NAME PARTITION_NAME COMPRESS
—————————— —————————— ——–
SALES_PARTITIONED_DATE_IDX P2023 ENABLED
SALES_PARTITIONED_DATE_IDX P2024 ENABLED

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

— 1. 检查当前索引大小
SELECT index_name, leaf_blocks, bytes / 1024 / 1024 AS size_mb
FROM user_indexes
WHERE table_name = ‘CUSTOMER_ORDERS’;– 2. 分析索引列的重复值情况
SELECT COUNT(DISTINCT customer_id) / COUNT(*) AS distinct_ratio
FROM customer_orders;– 3. 为复合索引启用前缀压缩
ALTER INDEX customer_orders_date_customer_idx REBUILD COMPRESS 1;– 4. 为大型索引启用基本压缩
ALTER INDEX customer_orders_product_idx REBUILD COMPRESS;– 5. 检查压缩后的索引大小
SELECT index_name, leaf_blocks, bytes / 1024 / 1024 AS size_mb
FROM user_indexes
WHERE table_name = ‘CUSTOMER_ORDERS’;– 6. 测试索引性能
SET TIMING ON
SELECT * FROM customer_orders WHERE customer_id = 1001 AND order_date BETWEEN TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2023-12-31’, ‘YYYY-MM-DD’);SET TIMING OFF

— 7. 监控索引使用情况
SELECT index_name, usage_count FROM v$object_usage WHERE table_name = ‘CUSTOMER_ORDERS’;– 8. 备份压缩索引
BACKUP INDEX app.customer_orders_date_customer_idx TO ‘/backup/index_backup.dmp’;– 9. 验证备份大小
SELECT BYTES / 1024 / 1024 AS backup_size_mb
FROM dba_data_files
WHERE tablespace_name = ‘INDEX_DATA’;– 10. 总结压缩效果
— 比较压缩前后的索引大小、查询性能和备份大小

— 1. 创建测试表
CREATE TABLE test_index_compression (
id NUMBER,
category VARCHAR2(100),
subcategory VARCHAR2(100),
value NUMBER,
create_date DATE
);– 2. 插入测试数据
INSERT INTO test_index_compression
SELECT
rownum,
‘Category ‘ || (rownum MOD 10),
‘Subcategory ‘ || (rownum MOD 100),
rownum * 100,
SYSDATE – rownum/1000
FROM dual
CONNECT BY rownum <= 100000;COMMIT;-- 3. 创建未压缩索引 CREATE INDEX test_index_uncompressed ON test_index_compression(category, subcategory);-- 4. 检查未压缩索引大小 SELECT index_name, leaf_blocks, bytes / 1024 / 1024 AS size_mb FROM user_indexes WHERE table_name = 'TEST_INDEX_COMPRESSION';-- 5. 测试未压缩索引性能 SET TIMING ON SELECT * FROM test_index_compression WHERE category = 'Category 5' AND subcategory = 'Subcategory 50';SET TIMING OFF -- 6. 创建压缩索引 CREATE INDEX test_index_compressed ON test_index_compression(category, subcategory) COMPRESS 1;-- 7. 检查压缩索引大小 SELECT index_name, leaf_blocks, bytes / 1024 / 1024 AS size_mb FROM user_indexes WHERE table_name = 'TEST_INDEX_COMPRESSION';-- 8. 测试压缩索引性能 SET TIMING ON SELECT * FROM test_index_compression WHERE category = 'Category 5' AND subcategory = 'Subcategory 50';SET TIMING OFF -- 9. 测试DML操作性能 SET TIMING ON UPDATE test_index_compression SET value = value * 1.1 WHERE category = 'Category 5';DELETE FROM test_index_compression WHERE category = 'Category 9';INSERT INTO test_index_compression VALUES (100001, 'Category 10', 'Subcategory 100', 10000100, SYSDATE);COMMIT;SET TIMING OFF -- 10. 分析压缩效果 SELECT index_name, leaf_blocks, bytes / 1024 / 1024 AS size_mb, compression FROM user_indexes WHERE table_name = 'TEST_INDEX_COMPRESSION';-- 11. 清理测试数据 DROP TABLE test_index_compression;

— 1. 压缩后索引性能下降
— 问题：启用压缩后索引查询性能下降
— 解决方案：
— 1.1 检查索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS(‘SCOTT’, ‘SALES_CUSTOMER_IDX’);– 1.2 考虑调整压缩类型
ALTER INDEX sales_customer_idx REBUILD NOCOMPRESS;– 1.3 检查执行计划
EXPLAIN PLAN FOR SELECT * FROM fgsales WHERE customer_id = 1001;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 2. 压缩率不理想
— 问题：索引压缩率低于预期
— 解决方案：
— 2.1 分析索引列的重复值情况
SELECT COUNT(DISTINCT customer_id) / COUNT(*) AS distinct_ratio
FROM fgsales;– 2.2 调整前缀压缩长度
ALTER INDEX sales_date_customer_idx REBUILD COMPRESS 2;– 2.3 考虑使用不同的索引类型
CREATE BITMAP INDEX sales_status_idx ON fgsales(status) COMPRESS;– 3. DML操作变慢
— 问题：启用压缩后DML操作性能下降
— 解决方案：
— 3.1 考虑使用NOCOMPRESS
ALTER INDEX sales_customer_idx REBUILD NOCOMPRESS;– 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 定期重建索引
ALTER INDEX sales_customer_idx REBUILD COMPRESS;– 4.2 调整索引重建策略
EXEC DBMS_SCHEDULER.CREATE_JOB(
job_name => ‘REBUILD_INDEXES’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN ALTER INDEX sales_customer_idx REBUILD COMPRESS; END;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=WEEKLY; BYDAY=SUN’,
enabled => TRUE
);– 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;