Using index performance tuning for indexes in Oracle

An index is an optional structure maintained by Oracle that provides fast access to data. It is difficult to know exactly where to use the index, and it is advantageous to use the index to adjust the retrieval speed. When an index is established, you must specify the name of the table used for tracking and one or more table columns. Once an index is established, Oracle automatically maintains the index when the database is established, changed, and deleted in the user table. When you create an index, the following guidelines help users make decisions:
1) The index should be established in the "where" or "and" section of the SQL statement that is involved in the table column (also the appellation word). If the "FirstName" table column of the personnel table is displayed as a query result, rather than as a predicate part, the table column will not be indexed, regardless of its value.
2) The user should index a range of table columns with a general principle: if the values of the columns in the table are within 2 0 of the rows in the table, the table column can be used as a candidate Index table column. If a table has 36 000 rows and the values of one table column in the table are evenly distributed (approximately every 12000 rows), then the table column is not suitable for an index. However, if the row for the column values in the other table columns in the same table is between 1 0 0 0~1 5 0 0 (3%~4%), the table column can be used as an index.

3) If more than one table column in the S Q L statement predicate is continuously referenced together, you should consider putting these table columns together in an index, and O R a C l e will maintain the index of a single table column (built on a single table column) or a composite index (built on multiple table columns). The composite index is called the collocated index.

first, the constraints of the primary key word

relational database theory states that one or more table columns in a table that uniquely identify each row of data in a table are the primary keywords of the object. Because the primary key defined in the data dictionary ensures uniqueness between the rows of data in the table, establishing a table index keyword in the o r a C l e 8 I database helps to apply throttling. In addition, this reduces the need for developers to have their own programming requirements for uniqueness checking.

tip using the Primary keyword index entry is faster than retrieving it without using the primary key index.

assuming that the table P e R S o n uses its i-D table column as the primary key, the following code is used to set the constraint:
ALTER TABLE person add constraint PERSON_PK primary key (ID) using index storage (initial 1m next 1m pctincrease 0) ta Blespace prd_indexes;

when processing the following S Q L statements: Select Last_Name, first_name, salary from the person where id = 289;

when looking for a determined "I d" table column value, O r a C l e will find directly p e R S O n _ P K. If it does not find the correct index entry, O R a C l e knows that the row does not exist. The main keyword index has the following two unique features:

1.1 Because the index is unique, o r a C l e knows that only one entry has a set value. If the desired entry is found, the lookup is terminated immediately.

1.2 Once an entry is encountered that is greater than the set value, the sequential search for the index can be terminated;

Ii. index in ORDER by

The ORDER by clause uses the index only under two strict conditions.
all columns in an order by must be in the same index and remain in the order in which they are arranged in the index.
all columns in the ORDER by must be defined as non-empty.
The index used in the WHERE clause and the index used in the ORDER BY clause cannot be tied.

For example:
Table Dept contains the following:
Dept_code PK NOT NULL
Dept_desc NOT NULL
dept_type NULL


Non-uniqueness Index (dept_type),
Inefficient : (index not used)
Select Dept_code from Dept ORDER by Dept_type
explain Plan:sort order by table access full

Efficient: (using index)
Select Dept_code from dept where dept_type > 0
Explain plan:
table access by ROWID on EMP
Index range scan on Dept_idx

third, avoid changing the type of indexed columns

Oracle automatically makes simple type conversions to columns when comparing data of different data types.

Suppose Empno is an indexed column of a numeric type:
Select ... from emp where empno = ' 123 '
in fact, after the Oracle type conversion, the statement translates to: Select ... from emp where empno = To_number (' 123 ')

Fortunately, the type conversion did not occur on the index column, and the purpose of the index was not changed.

now, suppose Emp_type is an indexed column of a character type: Select ... from emp where emp_type = 123
this statement was converted by Oracle to: SELECT ... from emp where To_number (emp_type) =123

This index will not be used because of the type conversions that occur internally! To avoid an implicit type conversion of your SQL by Oracle, it is best to explicitly represent the type conversion. Note When comparing characters to numbers, Oracle takes precedence in converting numeric types to character types.

iv. WHERE clause to be careful

the WHERE clause in some SELECT statements does not use an index. Here are some examples:

1, is null and is NOT NULL
You cannot use NULL as an index, and any column that contains null values will not be included in the index. Even if the index has more than one column, the column is excluded from the index as long as there is a column in the column that contains null. This means that if a column has a null value, even indexing the column does not improve performance.
any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the index.

2. '! = ' will not use the index. Remember, the index can only tell you what exists in the table, and cannot tell you what doesn't exist in the table

do not use the index: SELECT * FROM employee where salary<>3000;
Use index: Select account_name from transaction where amount >0;
Use index: SELECT * FROM employee where salary<3000 or salary>3000;

3. Join column, ' | | ' is a character join function. As with other functions, the index is deactivated

do not use the index: Select Account_name,amount from transaction where account_name| | Account_type= ' Amexa ';
Use index: Select Account_name,amount from transaction where account_name = ' AMEX ' and account_type= ' A ';

4, ' + ' is a mathematical function. As with other mathematical functions, the index is deactivated

do not use indexes: Select Account_name, amount from transaction where amount + >5000;
Use index: Select Account_name, amount from transaction where amount >;

5. The same index columns cannot be compared to each other, this will enable full table scan

do not use indexes: Select Account_name, amount from transaction where account_name = NVL (: acc_name,account_name);
Use index: Select Account_name, amount from transaction where account_name like NVL (: acc_name, '% ');

6. A like statement with a wildcard (%)

do not use the index: SELECT * from the employee where last_name like '%cliton% ';
Use index: SELECT * FROM employee where last_name like ' c% '

7, in and exists

do not use indexes: ... where column in (SELECT * from ...);
Use the index: ... where exists (select ' X ' from ...);
instead of not, use not exists as much as possible, although both use not (which cannot be used to slow down the index), the not exists is more efficient than the not-in query.

If you must enable indexing for columns that use functions:
1. Oracle's new features: function-based indexing (function-based index) may be a better solution:
CREATE INDEX emp_i on EMP (Upper (ename));/* Create a function-based index * /
SELECT * from EMP where upper (ename) = ' blacksnail ';/* will use index */
2. MS SQL Server Display declaration specifies the index:
SELECT * from personmember (INDEX = ix_title) WHERE ProcessID in (' Male ', ' female ')

v. How to monitor useless indexes

Oracle 9i above, can monitor the use of the index, if not used for a period of time index, is generally useless index

The syntax is:
start monitoring: Alter index index_name monitoring usage;
Check Usage Status: SELECT * from V$object_usage;
stop monitoring: Alter index index_name nomonitoring usage;
Of course, if you want to monitor the index under the entire user, you can use the following script:

set heading off
set echo off
Set Feedback off
Set pages 10000
Spool Start_index_monitor.sql

SELECT ' alter index ' | | owner| | '. ' | | index_name| | ' monitoring usage; ' From dba_indexes WHERE owner = USER;

Spool off
set heading on
set echo on
Set Feedback on
----------------------------------------------------------------------------------------------------------- ------------------- 
set heading off
set echo off
Set Feedback off
Set pages 10000
Spool Stop_index_monitor.sql

SELECT ' alter index ' | | owner| | '. ' | | index_name| | ' nomonitoring usage; ' From dba_indexes WHERE owner = USER;

Spool off
set heading on
set echo on
Set Feedback on

Original: http://czllfy.iteye.com/blog/510282