Introduction and optimization strategy of Oracle index indexing __oracle

Introduction to Oracle Index indexes

Indexing is an important part of relational database systems, which plays an important role in improving the speed of data retrieval. In Oracle, indexes are basically divided into the following categories:B*tree Index, reverse index, descending index, bitmap index, function index, Intermedia Full-text index, etc.

A bad choice can cause a deadlock and cause a sharp drop in database performance or a process termination. And if you make the right choice, you can use resources reasonably, so that processes that have been running for hours or even days will be completed in a few minutes, making you a hero immediately. This article will briefly discuss each index option. The main contents are as follows:

[1] Basic Index Concepts

The query dba_indexes view gets a list of all the indexes in the table, noting that the index of the schema (schema) can only be retrieved through the User_indexes method. Access the User_ind_columns view to get a specific column indexed in a given table.

[2] combination index

when an index contains more than one indexed column, the index is called a combined (concatented) index . Before Oracle9i introduces a jump-Scan index access method, the query can only use the index under limited conditions. For example: The table EMP has a composite index key that contains empno, ename, and Deptno. You cannot use this key to perform a range scan unless you specify a value for the first column (EMPNO) in the where sentence before oracle9i.

[3] ORACLE ROWID

With the rowid of each row, the index Oracle provides the ability to access the Single-line data. rowID is actually a line diagram that directly points to a single line. If you want to check for duplicate values or other references to the ROWID itself, you can use and specify ROWID columns in any table.

[4] Limit index

Restricting indexing is one of the common mistakes that inexperienced developers make. There are many traps in SQL that make some indexes unusable. Some common issues are discussed below:

4.1 Use Not equal to operator (<>,!=)

The following query still performs a full table scan, even if there is an index in the cust_rating column.

Select Cust_id,cust_name

From customers

where cust_rating <> ' AA ';

Change the above statement to the following query statement so that the index is used when adopting a rule-based optimizer instead of a cost-based optimizer (more intelligent).

Select Cust_id,cust_name

From customers

where cust_rating < ' AA ' or cust_rating > ' AA ';

Special Note: You can use indexes to avoid full table scans by changing the not-equal operator to an OR condition.

4.2 using is null or is NOT null

Using is null or is not NULL also restricts the use of indexes. Because null values are not defined. Using NULL in SQL statements can be a lot of trouble. Therefore, developers are advised to set the columns that need to be indexed to not NULL when the table is being built. If the indexed column has a null value in some rows, the index is not used (unless the index is a bitmap index and the bitmap index is discussed in detail later).

4.3 using functions

If you do not use a function based index, the optimizer ignores these indexes when you use functions in the WHERE clause of the SQL statement for columns that are indexed.

The following query does not use an index (as long as it is not a function based index)

Select Empno,ename,deptno from emp
where trunc (hiredate) = ' 01-may-81 ';

Change the above statement to the following statement so that you can find it by index.

Select Empno,ename,deptno from emp
where hiredate< (to_date (' 01-may-81 ') +0.9999);

4.4 Compare mismatched data types

Comparing mismatched data types is also one of the more difficult performance issues to discover.

Note The following query example, Account_number is a VARCHAR2 type and has an index on the Account_number field. The following statement performs a full table scan.

Select Bank_name,address,city,state,zip

From banks

where account_number = 990354;

Oracle can automatically turn the WHERE clause into to_number (account_number) = 990354, which limits the use of the index and can be used by changing to the following query:

Select Bank_name,address,city,state,zip

From banks

where Account_number = ' 990354 ';

Special Note: comparisons between mismatched data types will allow Oracle to automatically restrict the use of indexes, even if the query execution explain plan does not let you understand why a "full table scan" was done.

[5] Selective

Using the user_indexes view, a distinct_keys column is displayed in the view. By comparing the number of unique keys and the number of rows in the table, you can determine the selectivity of the index. The higher the selectivity, the less data the index returns.

[6] cluster factor (clustering Factor)

Clustering factor is located in the User_indexes view. This column reflects whether the data is ordered in relation to the indexed column. If the value of the clustering Factor column is close to the number of leaf blocks (leaf block) in the index, the data in the table is more orderly. If its value is close to the number of rows in the table, the data in the table is not very orderly.

[7] Two yuan height (Binary height)

The two-yuan height of the index plays a key role in the amount of I/O required to return ROWID to the user process. After parsing an index, you can view its two-dollar height by querying the dba_indexes b-level column. The binary height varies mainly with the size of the table and the narrow extent of the values in the indexed column. If there is a large number of deleted rows on the index, its two yuan height will also increase. Updating an indexed column is similar to a delete operation because it increases the number of deleted keys. Rebuilding the index can reduce the height by two yuan.

[8] fast Global scanning

You can use the Fast global scan (fast full Scan) option after Oracle7.3. This option allows Oracle to perform a global index scan operation. Fast global scan reads all leaf blocks on a B-tree index. The Db_file_multiblock_read_count parameter in the initialization file can control the number of blocks that are read concurrently.

[9] Jump-Scan

Starting with Oracle9i, the index jump scan feature allows the optimizer to use a combined index, even if the leading column of the index does not appear in the WHERE clause. Index jump scans are much faster than full index scans.

[10] type of index

　B-Tree index

Bitmap Index

Hash Index

Index Choreography Table

Reverse Key index

function-based indexing

Partitioned Index

Local and global indexes
Brief explanation:
B*tree index: Almost all relational databases have B*tree type indexes, and are also used most. The tree structure is similar to the two-fork tree, where the rows accessed are quickly positioned according to the RID .
Reverse Indexing : Reverses the byte in the B*tree index code, which is more evenly distributed index entries, and is used in parallel server environments to reduce the competition for index leaves.
Descending index : The newly appearing index type in 8i for reverse-sorted queries.
Bitmap Indexing : Use bitmaps to manage the corresponding relationships to data rows, and more for OLAP systems .
function Index : In this index, the data column is saved based on the value returned by the function, and the SELECT * FROM table where function (column) =value this type of statement.

Function Index

A function based index is also a new product since 8i, which has the ability to index computed columns, is easy to use and provides a calculated value, and improves query performance without modifying the application's logic. There are several prerequisites for using a function based index:

1 must have query REWRITE(in this mode) or GLOBAL query REWRITE(other mode) permissions.

2 must use a cost-based optimizer, the Rule-based optimizer will be ignored.

3 The following two system parameters must be set:

query_rewrite_enabled=true

query_rewrite_integrity=trusted

Can be set at the system level or at the thread level through ALTER system set,alter session set, or by adding implementations in Init.ora.

Here's an example of a function based index:

Sql> CREATE index Test.ind_fun on Test.testindex (Upper (a));

The index has been created.

sql> INSERT into Testindex values (' a ', 2);

1 lines have been created.

Sql> commit;

Submit completed.

Sql> Select/*+ rule*/* from Test.testindex where upper (a) = ' a ';

A B

-- ----------

A 2

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=hint:rule

1 0 TABLE ACCESS (full) ' Testindex '

(The optimizer has selected a full table scan)

--------------------------------------------------------------------

Sql> SELECT * from Test.testindex where upper (a) = ' a ';

A B

-- ----------

A 2

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT optimizer=choose (cost =2 card=1 bytes=5)

1 0 TABLE ACCESS (by INDEX ROWID) of ' Testindex ' (cost =2 card=

1 bytes=5)

2 1 INDEX (RANGE SCAN) of ' Ind_fun ' (non-unique) (cost =1 car

d=1) (using the Ind_fun index)

How to create various indexes

(1) *tree index.

Create index IndexName on tablename (Columnname[columnname ...])

(2) reverse indexing.

Create index IndexName on tablename (Columnname[columnname ...]) reverse

(3) descending index.

Create index IndexName on tablename (ColumnName desc[columnname ...])

(4) bitmap index.

Create BITMAP Index indexname on tablename (Columnname[columnname ...])

(5) function index.

Create index IndexName on tablename (functionname (columnname))

Note: After you create an index, the analysis is indexed to work.

Analyze index IndexName compute statistics;

Various index use occasions and suggestions

(1) b*tree index.

regular indexes, more for OLTP systems, and fast-positioned rows should be built on high cardinality columns (that is, the column's unique value divided by the number of rows is a large value, with very few identical values).

(2) reverse indexing.

the derivative products of b*tree are applied to special occasions and are established on the added columns of the OPS environment, and are not suitable for region scanning.

(3) descending index.

The derivative of the B*tree, applied to search statements in descending order, in which the index code in descending order is stored in the index and provides a quick descending search.

(4) bitmap index.

Bitmap-managed indexes, suitable for OLAP (on-line analysis) and DSS (decision processing) systems, should be built on low cardinality columns, suitable for centralized reading, unsuitable for inserts and modifications, and provide more space than the B*tree index.

(5) function index.

the derivative of the b*tree is applied to the case where the function is included on the condition column of the query statement, and the index code value of the function computed is stored in the index. Query efficiency can be improved without modifying the application.

When does the index not work?

First, you have to declare two knowledge points:

(1) rbo&CBO.

Oracle has two executive optimizer, one is RBO (rule Based Optimizer) rule-based optimizer , this optimizer chooses execution path based on SQL statement; The other is the CBO( cost Based Optimizer a rule-based optimizer that Oracle chooses execution paths based on statistical analysis information, and Oracle will use RBO instead of CBO if the tables and indexes are not analyzed If the tables and indexes are not analyzed for a long time, theCBO may also choose the wrong execution path, but the CBO is the direction of Oracle development, since the 8i version has gradually replaced Rbo.

(2) autotrace.

to see if the index is being used we need to use an Oracle called Autotrace feature , which shows the execution path of the SQL statement, and we can see how the SQL is executed inside Oracle, which is a very good helper, is widely used in SQL tuning. Let's take a look at how to use Autotrace:

① because Autotrace automatically assigns Executionplan to the user, plan_table must have been established before the user uses Autotrace. If not, run the utlxplan.sql script (which is in the $oracle_home/rdbms/admin directory).

②autotrace can be set by running the plustrce.sql script (which is in the $oracle_home/sqlplus/admin directory), logging in with the SYS user, and then After the Plustrce.sql is run, a plustrace role is established and the Plustrace role is granted to the relevant users, who can then use the Autotrace feature.

The default use method for ③autotrace is set Autotraceon, but this method is not always suitable for various occasions, especially when the number of rows is returned. The Set Autotrace Traceonly provides the ability to view only statistical information without querying the data.

Sql> set Autotrace on

Sql> select * from test;

A

----------

1

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (full) ' TEST '

Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

0 consistent gets

0 physical Reads

0 Redo Size

0 Bytes sent via sql*net to client

0 Bytes received via sql*net from client

0 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Rows processed

Sql> Set Autotrace traceonly

Sql> select * from Test.test;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (full) ' TEST '

Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

0 consistent gets

0 physical Reads

0 Redo Size

0 Bytes sent via sql*net to client

0 Bytes received via sql*net from client

0 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Rows processed

Hints is an auxiliary usage provided by Oracle, literally meaning ' hint ', and it does have the effect of prompting the optimizer to choose the execution path according to the keyword it provides, especially when it comes to SQL tuning. Use the following methods:

{delete| Insert| Select| UPDATE}/*+ hint [text] [Hint[text]] ... */

The Oracle SQL Reference can be referenced specifically.

With these previous knowledge points, let's take a look at when the index doesn't work. Here are a few things to list.

(1) When the type does not match.

Sql> CREATE TABLE Test.testindex (a varchar (2), b number);

Table has been created.

Sql> CREATE index Ind_cola on Test.testindex (a);

The index has been created.

sql> INSERT into Test.testindex values (' 1 ', 1);

1 lines have been created.

Sql> commit;

Submit completed.

sql> Analyze table Test.testindex Compute statistics for all indexes;

The table is parsed.

Sql> set autotrace on;

Sql> Select/*+rule */* from Test.testindex where a= ' 1 ';(using the rule based optimizer, data type matching

A B

-- ----------

1 1

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=hint:rule

1 0 TABLE ACCESS (by INDEX ROWID) of ' Testindex '

2 1 index (RANGE SCAN) of ' Ind_cola ' (non-unique) (index Ind_cola used)

――――――――――――――――――――――――――――――――――

Sql> Select/*+rule */* from Test.testindex where a=1 (data type mismatch)

A B

-- ----------

1 1

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=hint:rule

1 0 Table ACCESS (full) ' Testindex ' (the optimizer has selected all table scans)

(2) The Condition column contains a function but does not create a function index.

Sql> Select/*+ rule */* from Test.testindex where upper (a) = ' a '; (using the function upper () on column a);

A B

-- ----------

A 2

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=hint:rule

1 0 Table ACCESS (full) ' Testindex ' (Optimizer selects whole table scan)

----------------------------------------------------------

To create a function-based index

Sql> CREATE index Test.ind_fun on Test.testindex (Upper (a));

The index has been created.

sql> INSERT into Testindex values (' a ', 2);

1 lines have been created.

Sql> commit;

Submit completed.

Sql> Select/*+ rule*/* from Test.testindex where upper (a) = ' a ';

A B

-- ----------

A 2

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=hint:rule

1 0 TABLE ACCESS (full) ' Testindex '

(The function index is ignored under the Rule optimizer and a full table scan is selected)

-----------------------------------------------------------

Sql> SELECT * from Test.testindex where Upper (a)

= ' A ';

A B

-- ----------

A 2

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT optimizer=choose (cost =2 card=1 bytes=5)

1 0 TABLE ACCESS (by INDEX ROWID) of ' Testindex ' (cost =2 card=

1 bytes=5)

2 1 INDEX (RANGE SCAN) of ' Ind_fun ' (non-unique) (cost =1 car

d=1) (TheCBO Optimizer uses the Ind_fun index)

(3) The leading columns in the composite index are not used as query criteria.

Create a composite index

Sql> CREATE index ind_com on Test.testindex (A,B);

The index has been created.

Sql> Select/*+ rule*/* from Test.testindex where a= ' 1 ';

A B

-- ----------

1 2

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=hint:rule

1 0 Index (RANGE SCAN) of ' ind_com ' (non-unique) (index ind_com when the condition list contains leading columns)

Sql> Select/*+ rule*/* from Test.testindex where b=1;

No rows selected

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=hint:rule

1 0 Table ACCESS (full) ' Testindex ' (the condition list does not include a leading column is a select whole table scan)

-----------------------------------------------------------

(4)