包头焦点网站建设,重庆免费网站推广软件,产品怎样推广有效,校园网网络设计#x1f3c6; 文章目标#xff1a;本篇介绍Oracle索引知识以及案例场景 #x1f340; Oracle索引知识看这一篇就足够 ✅ 创作者#xff1a;Jay… #x1f389; 个人主页#xff1a;Jay的个人主页 #x1f341; 展望#xff1a;若本篇讲解内容帮助到您#xff0c;请帮忙… 文章目标本篇介绍Oracle索引知识以及案例场景 Oracle索引知识看这一篇就足够 ✅ 创作者Jay… 个人主页Jay的个人主页 展望若本篇讲解内容帮助到您请帮忙点个赞吧再点点您的小手关注下您的支持是我继续写作的最大动力谢谢 作为回馈对我博客内容感兴趣的小伙伴可以私聊我我们一起学习 Oracle 和 PostgreSQL的知识大家一起共同进步。 什么是索引
Oracle 索引是数据库中用于加快数据访问速度的一种结构。当对某个字段进行查询时如果该字段有索引数据库就可以直接查找到数据的位置而无需扫描整个表。这就像在书中查找某个特定的词如果书中有索引你就可以直接翻到该词的页面而无需一页一页地查找。
索引的分类 以下介绍的索引适用版本 Oracle 8i, 9i, 10g, 11g, 12c, 18c和19c等。 Oracle 提供了多种类型的索引包括
B-Tree 索引
B-Tree平衡多路查找树索引是Oracle数据库中最常用的索引类型。B-Tree索引对于高选择性的查询非常有效这意味着查询返回的结果集占总行数的一小部分。这种索引类型也支持对索引键进行排序和范围搜索。
CREATE INDEX emp_last_name_idx ON employees (last_name);此案例中我们创建了一个名为emp_last_name_idx的B-Tree索引该索引基于employees表的last_name列。
Bitmap 索引
Bitmap索引使用一种称为位图的数据结构每个位表示一个行的存在或不存在。Bitmap索引非常适合低选择性的数据即查询返回的结果集占总行数的很大一部分。另外Bitmap索引在数据仓库环境中很常用因为它可以高效地处理多个Bitmap索引之间的AND和OR操作。
CREATE BITMAP INDEX emp_gender_bidx ON employees (gender);在此案例中我们创建了一个名为emp_gender_bidx的Bitmap索引该索引基于employees表的gender列。
Partitioned 索引
Partitioned索引是与表分区配合使用的。表分区可以按不同的方式划分数据同样索引也可以按相同或不同的方式进行分区。每个分区索引对应一个表分区。分区索引可以是B-Tree索引或Bitmap索引。
CREATE INDEX sales_date_idx ON sales (sale_date)LOCAL (PARTITION sales_q1 VALUES LESS THAN (TO_DATE(01-APR-2022,DD-MON-YYYY)),PARTITION sales_q2 VALUES LESS THAN (TO_DATE(01-JUL-2022,DD-MON-YYYY)),PARTITION sales_q3 VALUES LESS THAN (TO_DATE(01-OCT-2022,DD-MON-YYYY)),PARTITION sales_q4 VALUES LESS THAN (TO_DATE(01-JAN-2023,DD-MON-YYYY)));在此案例中我们创建了一个名为sales_date_idx的分区索引该索引基于sales表的sale_date列索引按照日期范围进行了分区。
Function-Based 索引
Function-Based索引是在某个函数的结果上建立的。这个函数可以是内置函数例如UPPER, LOWER或用户定义函数。Function-Based索引对于处理复杂查询和改善特定类型的查询性能非常有用。
CREATE INDEX emp_name_upper_idx ON employees (UPPER(last_name));在此案例中我们创建了一个名为emp_name_upper_idx的函数索引该索引基于employees表的last_name列的大写形式。
Reverse Key 索引
Reverse Key索引和B-Tree索引类似只不过它将索引键的字节反转。这种索引类型主要用于减少索引键插入的热点适用于有大量插入操作的索引。
CREATE INDEX emp_id_reverse_idx ON employees (employee_id) REVERSE;在此案例中我们创建了一个名为emp_id_reverse_idx的反向键索引该索引基于employees表的employee_id列。
Text 索引
Text索引用于全文搜索可以搜索包含某个词或短语的文档。
首先需要创建一个文本首选项并设置其属性
BEGINCTX_DDL.CREATE_PREFERENCE(my_preference, BASIC_WORDLIST);CTX_DDL.SET_ATTRIBUTE(my_preference, STEM_FUZZY, ENGLISH);
END;然后可以创建文本索引
CREATE INDEX docs_text_idx ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (WORDLIST my_preference);在此案例中我们创建了一个名为docs_text_idx的文本索引该索引基于docs表的text列。
Spatial 索引
Spatial索引用于空间数据可以搜索在某个地理区域内的数据。
在创建空间索引之前首先需要在表上添加一个空间列
ALTER TABLE geo_data ADD (shape SDO_GEOMETRY);然后可以创建空间索引
CREATE INDEX geo_shape_sidx ON geo_data(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;在此案例中我们创建了一个名为geo_shape_sidx的空间索引该索引基于geo_data表的shape列。
每种索引类型都有其特定的优点和使用场景你可以根据实际需求选择合适的索引类型.
常见的索引优化案例
正面案例
假设你有一个员工表表中包含数百万条记录。如果你需要根据员工的姓氏查询数据没有索引的情况下Oracle 需要对整个表进行全表扫描这可能需要花费很长的时间。如果你在姓氏字段上创建了索引查询操作可以直接在索引中查找相应的数据大大减少了查询时间。
CREATE INDEX idx_lastname ON employees (lastname);然后你可以执行以下查询它将利用你刚刚创建的索引
SELECT * FROM employees WHERE lastname Smith;反面案例
假设你有一个订单表该表的每一行都有一个状态字段该字段表示订单的状态如“新订单”“处理中”“已完成”。如果你在此状态字段上创建了索引可能并不能获得你预期的性能提升。
CREATE INDEX idx_status ON orders (status);如果大部分的订单都处于“已完成”的状态那么查询所有“已完成”的订单可能会返回表中的大部分行。在这种情况下数据库可能决定进行全表扫描而不是使用索引因为全表扫描可能比查找并返回索引中的大部分行更有效。
SELECT * FROM orders WHERE status 已完成;因此当你考虑在某个字段上创建索引时需要考虑该字段的数据分布。在有大量重复值的字段上创建索引可能不会带来预期的性能提升。
索引失效的案例
有时间明明已经建立了索引缺无法生效那时为什么呢这时候需要考虑是否索引失效了如下场景介绍了失效的几种案例
1、使用了函数或表达式在这个例子中我们用到了UPPER函数。索引可能不会被使用
SELECT * FROM employees WHERE UPPER(last_name) SMITH;在这个情况下你可能需要创建一个function-based index来优化这个查询。
2、使用了不等运算符在这个例子中我们用到了不等运算符所以索引可能不会被使用
SELECT * FROM employees WHERE salary 5000;3、使用了OR运算符在这个例子中我们用到了OR运算符。尽管department_id和job_id列都有索引但是在这种情况下Oracle可能无法有效地利用它们
SELECT * FROM employees WHERE department_id 10 OR job_id SA_REP;4、NULL值Oracle在索引中不存储NULL值。因此在这个例子中尽管last_name列有索引但是如果我们搜索NULL值索引可能不会被使用
SELECT * FROM employees WHERE last_name IS NULL;5、使用了不等运算符当在查询中使用不等号运算符时Oracle可能无法使用索引。例如
SELECT * FROM employees WHERE salary 5000;在此查询中Oracle无法使用salary的索引如果存在的话因为不等运算符无法有效利用B-tree索引。
6、使用了OR运算符在WHERE子句中使用OR运算符可能会使索引失效尤其是当OR运算符连接的列都有各自的索引时。例如
SELECT * FROM employees WHERE department_id 10 OR job_id SA_REP;在此查询中即使department_id和job_id列都有索引Oracle也可能无法有效地利用它们。
7、数据分布不均如果索引列的数据分布非常不均匀那么对于某些查询Oracle可能会选择全表扫描而不是使用索引。
8、统计信息过时或不准确Oracle优化器依赖于准确的统计信息来制定最有效的执行计划。如果统计信息过时或不准确Oracle可能不会选择最佳的执行计划这可能包括不正确的索引选择。
这些都是索引可能失效的原因如果在执行计划中发现索引未被使用可以检查上述情况是否存在。如果存在可以通过调整查询、改变数据分布或更新统计信息等方式来优化。
索引的最佳实践
创建索引前要充分理解业务需求理解你的查询工作负载是至关重要的因为它将决定哪些列需要被索引以及应该使用哪种类型的索引。避免过度索引虽然索引可以加速查询但每个额外的索引都会消耗磁盘空间并在插入、更新和删除操作时带来额外的开销。因此你应该避免对不需要的列创建索引。定期维护和重建索引索引会随着时间的推移而变得碎片化可能需要定期重建。你可以使用 Oracle 的 ANALYZE INDEX 命令或 DBMS_STATS 包来收集索引的统计信息并确定是否需要重建索引。
