oracle使用索引优化性能一

1.时间索引与表达式问题

The plch_employees table was created with this statement:

CREATE TABLE plch_employees
(
    employee_id     INTEGER
,  last_name       VARCHAR2 (100)
,  first_name      VARCHAR2 (100)
,  hire_date       DATE
,  salary          NUMBER
)

and contains 10 million rows. Hire dates are distributed evenly between the years 1980 and 2010.

The table currently has no indexes and users are complaining about performance. For which of the queries would the following index improve performance?

CREATE INDEX i_plch_employees
   ON plch_employees (hire_date)

选择1，使用between 走索引

SELECT last_name
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
This query does not reference hire_date within an expression and the it looking for a approximately 1/20th of the total rows in the table. An index on hire_date should improve performance of this query.

选择2，不走索引

SELECT last_name
  FROM plch_employees
 WHERE TO_CHAR (hire_date, 'YYYY') = '2010'

选择3，大于时间不走索引

SELECT last_name
  FROM plch_employees
 WHERE hire_date < SYSDATE
This query will retrieve all rows from the table, so an index on hire_date will not help.

选择4，表达式不走索引

SELECT last_name
  FROM plch_employees
 WHERE hire_date + 365 BETWEEN TO_DATE ('01-01-2011', 'MM-DD-YYYY')
                           AND TO_DATE ('12-31-2011', 'MM-DD-YYYY')
Since hire_date is part of an expression, Oracle will not use the index to identify rows in the plch_employees table.

总结：

Summary

When you use an indexed column inside an expression, the index for that column will not be used. In addition, if your WHERE clause does not specify a sufficiently small subset of rows, an index will not help improve performance.

 

Background

You can create indexes on columns to speed up queries. Indexes provide faster access to data for operations that return a small portion of a table's rows.

In general, you should create an index on a column in any of the following situations:什么情况下需要建立索引：

• The column is queried frequently.  #某列常用

• A referential integrity constraint exists on the column.#外键约束

• A UNIQUE key integrity constraint exists on the column.#这列有唯一约束

You can create an index on any column; however, if the column is not used in any of these situations, creating an index on the column does not increase performance and the index takes up resources unnecessarily.

Although the database creates an index for you on a column with an integrity constraint, explicitly creating an index on such a column is recommended.

For the database to use the index when appropiate (and not use it when in-appropriate) valid and up-to-date statistics on the table and index are essential. Over the years from version to version Oracle has become better and better at automatically gathering such stats, but if your index is not used where you thought it should be, then check your stats for staleness and consider re-gathering using DBMS_STATS package.

如果索引失效，可以考虑使用DBMS_STATS包来收集统计信息;

下节，如何使用DBMS_STATS调优化https://blog.csdn.net/qingzhuoran/article/details/53927439