Oracle数据库索引与数据类型全解析

# Oracle数据库索引与数据类型全解析 ## 1. 索引列排序与压缩 在数据库操作中，把索引列按照区分度排序并不能带来固有的效率提升。实际上，使用索引键压缩时，将选择性最低的列放在前面反而有优势。例如，在特定查询中，如果对索引使用 `COMPRESS 2`，第一个索引的 I/O 操作大约是第二个索引的三分之二。 索引列的排序决策应基于索引的使用方式。若有如下大量查询： ```sql select * from t where c1 = :x and c2 = :y; select * from t where c2 = :y; ``` 那么在 `T(C2,C1)` 上创建索引更合理。因为这个单一索引可用于上述两个查询，并且若 `C2` 在前，使用索引键压缩能构建更小的索引。假设 `C1` 和 `C2` 平均长度均为 10 字节，该索引的条目原本为 2,000,000 字节（100,000 × 20），使用索引键压缩后可缩减至 1,250,000 字节（100,000 × 12.5），因为 `C2` 的四次重复中有三次可被压缩。 在 Oracle 5 版本中，曾主张将最具选择性的列放在索引前面，这与该版本实现的索引压缩方式有关（与索引键压缩不同）。但从版本 6 引入行级锁后，此特性被移除。此后，将最具区分度的条目放在索引前面并不能使索引更小或更高效。而使用索引键压缩则能让索引变小，不过仍需根据索引的使用方式来决定。 ## 2. 自动索引功能概述 从 19c 版本开始，Oracle 数据库具备自动索引功能，可将部分索引管理任务委托给数据库。该功能会根据应用程序的工作负载创建、重建和删除索引，由其管理的索引称为自动索引。自动索引的主要方面如下： - 识别可能提高 SQL 语句性能的待索引列，即候选索引。 - 自动创建以 `SYS_AI` 为前缀的不可见索引，这些新索引初始不可见，优化器不会考虑使用。 - 测试不可见索引，若能提升 SQL 语句性能则使其可见；若没有性能提升，则标记为不可用并随后删除。 - 自动删除未使用的索引。 需注意，自动索引功能目前仅在安装了企业版的 Oracle Exadata 机器上可用。在非 Exadata 系统中，若尝试执行与此功能相关的包，会收到 `ORA - 40216: feature not supported` 消息。不过，可通过设置隐藏参数 `_exadata_feature_on` 为 `TRUE` 来启用该功能，但需获得 Oracle 许可，因为这涉及许可问题。 启用自动索引后，数据库会管理索引的创建、重建和删除。此时，DBA 的工作包括： - 通过设置各种参数管理配置。 - 审查系统生成的报告。 ## 3. 自动索引的管理 自动索引通过内部的 `DBMS_AUTO_INDEX` PL/SQL 包进行管理，其默认模式为 `REPORT ONLY`。在此模式下，自动索引开启，但创建的索引不可见，优化器不会使用。 可在根容器级别或可插拔数据库级别启用/禁用索引。例如，可在根容器中启用自动索引，而在可插拔数据库中禁用，反之亦然。可通过以下方式查看当前自动索引设置（假设连接到可插拔数据库，以下仅显示 PDB 级别的设置）： ```sql $ sqlplus eoda/foo@PDB1 SQL> select con_id, parameter_name, parameter_value from cdb_auto_index_config order by 1, 2; CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ----------------------------------- --------------------------- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE 3 AUTO_INDEX_MODE REPORT ONLY 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 50 ``` 可通过 `DBMS_AUTO_INDEX` 的 `CONFIGURE` 过程修改上述参数。例如，启用自动索引功能： ```sql SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT'); ``` 禁用自动索引： ```sql SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF'); ``` 启用自动索引后，所有模式都可能被自动管理索引。可通过 `CONFIGURE` 过程修改此设置，例如创建包含列表： ```sql SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'EODA', allow => TRUE); ``` 运行显示配置参数的查询，可看到包含列表： ```sql SQL> CON_ID PARAMETER_NAME PARAMETER_VALUE ---- ----------------------------------- --------------------------- 3 AUTO_INDEX_SCHEMA schema IN (EODA) ``` 同样，可创建排除列表： ```sql SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'EODA', allow => FALSE); SQL> select con_id, parameter_name, parameter_value from cdb_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA'; CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ----------------------------------- --------------------------- 3 AUTO_INDEX_SCHEMA schema NOT IN (EODA) ``` 若要将 `AUTO_INDEX_SCHEMA` 值恢复为默认值，可按如下方式将 NULL 传递给过程： ```sql SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => FALSE); ``` 部分自动索引功能需充分测试后使用。例如，有一个功能可删除数据库或特定模式中的所有二级索引（二级索引指不用于强制约束的索引），使用此功能时需格外谨慎。 以下代码将删除数据库中的所有二级索引，若连接到可插拔数据库，则仅删除该数据库中的二级索引： ```sql $ sqlplus system/foo@PDB1 SQL> exec dbms_auto_index.drop_secondary_indexes; ``` 若数据库中有大量索引，上述命令可能需要几分钟才能完成。若要删除特定模式中的索引： ```sql SQL> exec dbms_auto_index.drop_second ```