Oracle索引 #

索引类型
├── B树索引（B-Tree Index）
│   └── 默认索引类型，适合高基数列
├── 位图索引（Bitmap Index）
│   └── 适合低基数列
├── 函数索引（Function-based Index）
│   └── 基于函数或表达式
├── 反向键索引（Reverse Key Index）
│   └── 解决索引热点问题
└── 其他索引
    ├── 分区索引
    ├── 域索引
    └── 文本索引

-- 创建普通索引
CREATE INDEX idx_emp_name ON employees(last_name);

-- 创建复合索引
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- 创建表空间指定索引
CREATE INDEX idx_emp_name ON employees(last_name)
TABLESPACE users_index;

-- 适合创建索引的场景
-- 1. WHERE条件频繁使用的列
-- 2. JOIN连接的列
-- 3. ORDER BY排序的列
-- 4. DISTINCT去重的列

-- 不适合创建索引的场景
-- 1. 小表（数据量小）
-- 2. 频繁更新的列
-- 3. 低选择性列（如性别）
-- 4. 很少查询的列

-- 位图索引适合低基数列
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
CREATE BITMAP INDEX idx_emp_status ON employees(status);

-- 位图索引特点
-- 1. 适合低基数列（如性别、状态）
-- 2. 适合OLAP系统，不适合OLTP
-- 3. 占用空间小
-- 4. 不适合频繁更新的表

-- 基于函数的索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

-- 使用
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- 基于表达式的索引
CREATE INDEX idx_emp_annual_sal ON employees(salary * 12);

-- 使用
SELECT * FROM employees WHERE salary * 12 > 100000;

-- 查看表上的索引
SELECT index_name, index_type, uniqueness, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- 查看索引列
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

-- 重建索引
ALTER INDEX idx_emp_name REBUILD;

-- 在线索引重建（不停机）
ALTER INDEX idx_emp_name REBUILD ONLINE;

-- 合并索引碎片
ALTER INDEX idx_emp_name COALESCE;

-- 删除索引
DROP INDEX idx_emp_name;

-- 禁用索引
ALTER INDEX idx_emp_name UNUSABLE;

-- 启用索引
ALTER INDEX idx_emp_name REBUILD;

-- 开启索引使用监控
ALTER INDEX idx_emp_name MONITORING USAGE;

-- 查看监控结果
SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMP_NAME';

-- 关闭监控
ALTER INDEX idx_emp_name NOMONITORING USAGE;

-- 强制使用索引
SELECT /*+ INDEX(e idx_emp_name) */ *
FROM employees e
WHERE last_name = 'Smith';

-- 强制全表扫描
SELECT /*+ FULL(e) */ *
FROM employees e
WHERE last_name = 'Smith';