Oracle index principle (1) _ MySQL

Oracle indexing principle (1) Oracle provides a large number of indexing options. Knowing which option to use under a given condition is very important to the performance of an application. A wrong choice may lead to deadlocks, resulting in a sharp drop in database performance or process termination.

If you make the right choice, you can reasonably use the resources, so that those processes that have been running for several hours or even a few days can be completed in a few minutes, so that you will immediately become a hero. This article will briefly discuss each index option. It mainly includes the following content:

[1] basic indexing concepts

Query the DBA_INDEXES view to obtain the list of all indexes in the table. Note that you can only retrieve the schema indexes using the USER_INDEXES method. Access the USER_IND_COLUMNS view to obtain a specific column indexed in a given table.

[2] Composite Index

When an index contains multiple indexed columns, it is called a concatented index. Before Oracle9i introduces the skip scan index access method, the query can only use this index with limited conditions. For example, the table emp has a composite index key, which contains empno, ename, and deptno. Before Oracle9i, unless a value is specified for the first column (empno) in the where clause, this index key cannot be used for a range scan.

Note: Before Oracle9i, a composite index can be used only when the index's leading index is used!

[3] ORACLE ROWID

With the ROWID of each row, Oracle indexes provide the ability to access a single row of data. ROWID is actually a line chart pointing directly to a separate line. To check duplicate values or other references to ROWID, you can use and specify the rowid column in any table.

[4] restrict indexes

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

4.1 use non-equals operators (<> ,! =)

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

Select cust_Id, cust_name

From MERS

Where cust_rating <> 'A ';

Change the preceding statement to the following query statement. in this way, indexes are used when a rule-based optimizer is used instead of a cost-based optimizer (more intelligent.

Select cust_Id, cust_name

From MERS

Where cust_rating <'AA' or cust_rating> 'AA ';

Note: You can use indexes by changing the non-equals operator to the OR condition to avoid full table scanning.

4.2 use is null or IS NOT NULL

Using is null or is not null also limits the use of indexes. The NULL value is not defined. Using NULL in SQL statements can cause a lot of trouble. Therefore, we recommend that you set the column to not null when creating a table. If the indexed column has a NULL value in some rows, this index will not be used (unless the index is a single-digit index reference, which will be discussed in detail later ).

4.3 Use functions

If function-based indexes are not used, the optimizer will ignore these indexes when using functions for indexed columns in the WHERE clause of SQL statements.

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 preceding statement to the following statement, so that you can search by index.

Select empno, ename, deptno

From emp

Where hiredate <(to_date ('01-MAY-81 ') + 0.9999 );

4.4 unmatched data types

Unmatched data types are also one of the performance issues that are difficult to find. Note that in the following query example, account_number is of the 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 change the where clause to to_number (account_number) = 990354, which limits the use of the index. you can change it to the following query to use the index:

Select bank_name, address, city, state, zip

From banks

Where account_number = '123 ';

Note: Oracle automatically limits the use of indexes when comparing unmatched data types, even if you execute an Explain Plan for this query, you cannot understand why a full table scan is performed ".
Oracle provides a large number of index options. Knowing which option to use under a given condition is very important to the performance of an application. A wrong choice may lead to deadlocks, resulting in a sharp drop in database performance or process termination.

If you make the right choice, you can reasonably use the resources, so that those processes that have been running for several hours or even a few days can be completed in a few minutes, so that you will immediately become a hero. This article will briefly discuss each index option. It mainly includes the following content:

[1] basic indexing concepts

Query the DBA_INDEXES view to obtain the list of all indexes in the table. Note that you can only retrieve the schema indexes using the USER_INDEXES method. Access the USER_IND_COLUMNS view to obtain a specific column indexed in a given table.

[2] Composite Index

When an index contains multiple indexed columns, it is called a concatented index. Before Oracle9i introduces the skip scan index access method, the query can only use this index with limited conditions. For example, the table emp has a composite index key, which contains empno, ename, and deptno. Before Oracle9i, unless a value is specified for the first column (empno) in the where clause, this index key cannot be used for a range scan.

Note: Before Oracle9i, a composite index can be used only when the index's leading index is used!

[3] ORACLE ROWID

With the ROWID of each row, Oracle indexes provide the ability to access a single row of data. ROWID is actually a line chart pointing directly to a separate line. To check duplicate values or other references to ROWID, you can use and specify the rowid column in any table.

[4] restrict indexes

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

4.1 use non-equals operators (<> ,! =)

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

Select cust_Id, cust_name

From MERS

Where cust_rating <> 'A ';

Change the preceding statement to the following query statement. in this way, indexes are used when a rule-based optimizer is used instead of a cost-based optimizer (more intelligent.

Select cust_Id, cust_name

From MERS

Where cust_rating <'AA' or cust_rating> 'AA ';

Note: You can use indexes by changing the non-equals operator to the OR condition to avoid full table scanning.

4.2 use is null or IS NOT NULL

Using is null or is not null also limits the use of indexes. The NULL value is not defined. Using NULL in SQL statements can cause a lot of trouble. Therefore, we recommend that you set the column to not null when creating a table. If the indexed column has a NULL value in some rows, this index will not be used (unless the index is a single-digit index reference, which will be discussed in detail later ).

4.3 Use functions

If function-based indexes are not used, the optimizer will ignore these indexes when using functions for indexed columns in the WHERE clause of SQL statements.

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 preceding statement to the following statement, so that you can search by index.

Select empno, ename, deptno

From emp

Where hiredate <(to_date ('01-MAY-81 ') + 0.9999 );

4.4 unmatched data types

Unmatched data types are also one of the performance issues that are difficult to find. Note that in the following query example, account_number is of the 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 change the where clause to to_number (account_number) = 990354, which limits the use of the index. you can change it to the following query to use the index:

Select bank_name, address, city, state, zip

From banks

Where account_number = '123 ';

Note: Oracle automatically limits the use of indexes when comparing unmatched data types, even if you execute an Explain Plan for this query, you cannot understand why a full table scan is performed ".