Why NOT Use My Index

原创 2013年12月04日 14:35:53
为什么定义了索引,执行时却没有用到? 可能的几种情况:


SQL> show parameter optimizer_mode
NAME                                        TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                         string       ALL_ROWS
关于optimizer_mode参数的解释可以参考:The Oracle SQL Optimizers

3.复合索引(Concatenated Indexes)中的前导列没有作为查询条件

推荐阅读:Concatenated Indexes

4.条件列包含函数但没有创建函数索引(Function-Based Indexes)
一般来讲,Where子句中使用了函数,即使列上有索引,也不会使用到。8i后引入了Function-Based Indexes,可以解决函数列用不到索引的问题。


B*TREE Indexes improve the performance of queries that select a small percentage of rows from a table. As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table's rows. This value may be higher in situations where all data can be retrieved from an index, or where the indexed columns can be used for joining to other tables.

The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index. The ideal selectivity is 1. Such a selectivity can be reached only by unique indexes on NOT NULL columns.

Example with good Selectivity

A table having 100'000 records and one of its indexed column has 88000 distinct values, then the selectivity of this index is 88'000 / 10'0000 = 0.88.

Oracle implicitly creates indexes on the columns of all unique and primary keys that you define with integrity constraints. These indexes are the most selective and the most effective in optimizing performance. The selectivity of an index is the percentage of rows in a table having the same value for the indexed column. An index's selectivity is good if few rows have the same value.

Example with bad Selectivity

lf an index on a table of 100'000 records had only 500 distinct values, then the index's selectivity is 500 / 100'000 = 0.005 and in this case a query which uses the limitation of such an index will retum 100'000 / 500 = 200 records for each distinct value. It is evident that a full table scan is more efficient as using such an index where much more I/O is needed to scan repeatedly the index and the table.




Why Isn't Oracle Using My Index?!

The question in the title of this piece is probably the single most  frequently occurring question t...

Kill -9 Why You Should Not Use It Unless Absolutely Necessary

It has come to my attention that there is a great wave of people out there that think using kill -9 ...

The ultimate SO_LINGER page, or: why is my tcp not reliable

转自:http://blog.csdn.net/cpp_chen/article/details/29864509 This post is about an obscure corner of T...

我们为什么用卷积? Why should we use convolution?

Why should we use convolution? 问题限定: 仅对离散信号做分析 首先要回答什么是卷积的问题.               In mathematics a...

C++ Why CPP Not Just an OOPL

  • 2009年11月28日 02:34
  • 53KB
  • 下载

why use aftermarket parts

Aftermarket Parts An aftermarket part is any part for a vehicle that is not sourced from the ca...

【Zookeeper】——Why should we use zookeeper?

Table of contentTable of content why should we use Services Discovery why do we choose zookeeper to ...
您举报文章:Why NOT Use My Index