Oracle数据库基础知识——索引

一、基本概念

     索引是数据库中用于加快数据检索速度的数据结构，类似于书籍的目录。它可以帮助数据库快速定位到数据所在的位置，提高查询效率。索引基于一个或多个列的值创建，并按照一定的数据结构进行组织，允许数据库系统更快速地访问和检索数据。

创建索引的好处：

1.提高检索速度：

索引可以加速数据检索，特别是对于经常用于WHERE字句和连接操作的列。

2.优化查询性能：

当查询中涉及到索引列是，数据库可以更快地定位和筛选数据，提高查询效率。

3.加速排序和分组操作：

索引对于排序和分组操作也能提供性能优势。

4.唯一约束：

唯一索引可以确认列中的数据值是唯一的，防止重复值的插入。

5.优化连接操作：

符合索引可加速连接操作，特别是在多表连接时。

虽然索引可以提高查询性能，单它们也会带来一些额外的开销。维护索引需要额外的存储空间和更新成本。此外，过多的索引可能会导致插入、更新和删除操作的性能下降，因此需要在创建索引时权衡利弊，并根据实际需求和查询模式进行优化和管理。

二、索引相关语句

查看索引信息

1.查看表的索引：

SELECT index_name, table_name, column_name 
FROM user_ind_columns 
WHERE table_name = 'your_table';

这将列出指定表的索引、索引包含的列和索引的信息。

2.查看索引的统计信息

SELECT index_name, table_name, distinct_keys, num_rows 
FROM user_indexes 
WHERE table_name = 'your_table';

这个查询将返回索引的统计信息，例如索引的唯一键数和行数等。 

创建和删除索引

1.创建索引：

在Oracle中，可以使用'CREATE INDEX'语句来创建索引，示例如下：

--创建单个列的索引
CREATE INDEX index_name ON table_name(column_name);

--多个列创建复合索引
CREATE INDEX index_name ON table_name(column1, column2, ...);

创建一个名为'index_name'的索引，该索引基于制定表'table_name'中的'column_name'列。

 2.删除索引

DROP INDEX index_name;

删除名为'index_name'的索引。

索引管理和维护：

1.查询索引状态：

SELECT index_name, status 
FROM user_indexes 
WHERE table_name = 'your_table';

这个查询将返回指定表的索引及其状态信息。'status'列会显示索引的状态，其中包括'

VALID（有效）'、'UNUSABLE（不可用）'、'IN PROGRESS（进行中）'等状态。

如果索引状态为'UNUSABLE（不可用）'，你可能需要使用'ALTER INDEX'语句重新构建或修复索引，使其恢复有效状态。

2.重建索引：

ALTER INDEX index_name REBUILD;

使用'REBULID',可以尝试重新构建索引，使其恢复为有效状态。如果索引状态持续为不可用、可能需要进一步排查并采取相应措施解决问题。

2.1在Oracle中，索引可以处于不同的状态，这些状态反映了索引的可用性和有效性。创建的索引状态包括：

1.VALID（有效）：索引处于有效状态，可以被数据库系统使用。

2.UNUSABLE（不可用）：索引不可用，可能是由于某些操作导致索引失效，比如表结构变更、意外关机等情况。这时需要重新创建或修复索引。

3.IN PROGRESS（进行中）：索引正在被创建、重建或维护的过程中。

2.2什么时候需要重建索引

2.2.1.索引碎片严重：

当索引碎片过多，导致索引的数据存储不连续时，可能会影响查询性能。在这种情况下，重新构建索引可以整理碎片，提高索引的效率。

2.2.2索引失效或不可用：

当索引状态显示为'UNUSABLE'或‘INVALID'时，表名索引失效或不可用。这时可能需要重新创建索引来修复这种情况。

2.2.3.数据量大幅增加或减少：

当数据表中的数据量大幅变化时，原有的索引可能不再适用于新的数据分布。这时候可能需要重新构建索引以适应新的数据情况，提高查询性能。

2.2.4.查询性能下降：

当数据库中的某些查询性能明显下降时，可能是应为索引失效、数据分布不均匀或索引选择不当等原因导致。这时可以考虑重建索引以优化查询性能。

2.2.5.数据库维护期间：

在数据库维护期间，如备份、重建统计信息等操作完成后，重建索引可以是优化性能的一部分。