Plsql_ Performance Optimization Series 01_oracle Index Index

2014-06-01 Baoxinjian

I. Summary

In the Plsql query optimization, the use and the most contact should be indexed index of the concept, the individual also feel that the index selection and optimization is an important concept in the program optimization process, especially the initial contact Plsql performance optimization

Some concepts of indexing

• An index can consist of one or more columns,
• Setting an index on a column is actually sorting the contents of the column in a certain way, retrieving the data, retrieving the sorted data, retrieving the last valid data, and then jumping out of the search
• This eliminates the need for a full table scan, while many algorithms can be used to improve retrieval efficiency
• Data retrieval by using binary method of database

How indexes are connected

• Hash Join
• Nested Loops
• Merge Join

Classification of indexes

• B-Tree Index
• Reverse Index
• Descending index
• Bitmap index
• Function index
• Indexes need to be parsed for index to take effect after index is established
• Differences between primary key and uniqueness index

Hints used in the index

• Force index

The cost of building an index

• When the basic table is maintained, the system should maintain the index at the same time, the unreasonable index will seriously affect the system resources, mainly in the CPU and I/O;
• Inserting, updating, deleting data generates a large number of DB file sequential read lock waits;

In some cases, although index is defined in the query field, Plsql does not have an index on that field, because Oracle has a role-based approach to parsing the plan, one that is based on cost in some cases, the efficiency of index is lower than the whole table sweep second, not index, Plsql performance will multiply, and worse, the index will also lead to performance than the original index is worse, there is an understanding of the misunderstanding, so a little mention;)

Also, sometimes the index you build may be invalidated for space or other reasons, so it can cause some programs to have no problems, sudden performance problems, and very large performance issues, so there is a high demand for EBS DBAs, to monitor some system anomalies

Second, the connection of the index method

1. Hash Join

2. Nested Loops

3. Merge Join

Specific analysis

1. Hash Join

(1). Overview

I. Read data from a table and place it into memory and create a bitmap index of the unique keyword

Ii. reading another table, and comparing the in-memory table with the hash algorithm

(2). Applicable objects

I. Large Table Connection Small table

Ii. two large tables

2. Nested Loops

(1). Overview

I. Circular appearance record

II. Whether the connection between the individual alignment and the internal standard meets the conditions

(2). Applicable objects

Small tables drive large tables, returning fewer result sets

3. Merge Join

(1). Overview

I. Two tables for table access full

Ii. sorting the results of table access full

Iii. merging the sorting structure with a merge join

(2). Applicable objects

Accessing Data through ROWID

Iii. Classification of indexes

1. B-Tree Index

2. Reverse Indexing

3. Descending index

4. Bitmap indexing

5. Function Index

6. Indexes need to be analyzed for index to be effective after index creation

7. Differences between primary key and uniqueness index

Specific analysis

1. B-Tree Index

(1). Overview

The most commonly used index structure, which is established by default, is this structure

Applies to high-cardinality data columns (most of which have different values)

(2). How to Build

CREATE INDEX index_name on wip_entities (wip_entity);

2. Reverse Indexing

(1). Overview

(2). How to Build

3. Descending index

(1). Overview

For columns that need to be sorted in descending order

(2). How to Build

CREATE INDEX index_name on wip_entities (wip_entity DESC);

4. Bitmap indexing

(1). Overview

For low cardinality data columns (most of which have the same value)

(2). How to Build

CREATE BITMAP INDEX index_name on wip_entities (sex);

5. Function Index

(1). Overview

Columns that apply to the column where the function is required

(2). How to Build

CREATE INDEX index_name on Wip_entities (TRUNC (creation_date));

6. Analysis Index

ANALYZE INDEX index_name COMPUTE STATISTICS;

7. Differences between primary key and uniqueness index

(1). The primary key is a constraint, and the uniqueness index is just an index

(2). Primary key cannot be null, uniqueness can be null

Four. Use of hints in index-force index

1. Build a test table, and test the index

 1 create table Dba_name ( 3 username  VARCHAR (100 4  5 password varchar (100)  7 ) ;  8 9 create index index_t on dba_name (username);        

2. Comparison of methods

(1). Force index not used

SELECT *

From Dba_name

WHERE username = ' Baoxinjian '

(2). Forced index used

SELECT /*+ index (t index_t) */

*

From Dba_name t

WHERE username = ' Baoxinjian '

(3). In some cases, although index is defined in the query field, Plsql does not have an index on that field because Oracle is a role-based

In some cases, the efficiency of the index is lower than the full table sweep seconds, not built index,plsql performance will multiply, worse, the index will also lead to performance than the original index is worse, there is a misunderstanding, so a little mention;)

Five. Indexing some rules

1. The relationship between the number of indexes and DML is weighed, and DML is the insertion and deletion of data operations.

There is a need to weigh a problem, the purpose of indexing is to improve the efficiency of the query, but the establishment of too many indexes, will affect the speed of inserting and deleting data, because we modify the table data, the index will also be modified. Here we need to weigh whether our operation is to query more or modify more.

2, put the index and the corresponding table in a different table space.

When a table is read, the table is in parallel with the index. If the table and index and in a table space will generate resource competition, placed in two tables this empty can be executed in parallel.

3, the best use of the same size is a block.

Oracle defaults to five blocks, read I/O, and if you define 6 blocks or 10 blocks you need to read two I/O. It is better to have an integer multiplier of 5 to improve efficiency.

4. If a table is large, indexing takes a long time because indexing also generates a large amount of redo information, so you can set up redo information when you create an index without producing or producing less. As long as the table data exists, the index fails to build again, so it is not necessary to generate redo information.

5, when the index should be built according to the specific business SQL to create, especially where conditions, as well as the order of where conditions, as far as possible to filter a wide range of placed in the back, because the SQL execution is from the back forward

Six. Operation of the Index

To change the index:

sql> ALTER index Employees_last _NAME_IDX storage (next 400K maxextents 100); After the index is created, it does not feel reasonable, and its parameters can be modified. Read more about the documentation

2. Adjust the space of the index:

2.1 New Space Additions

Sql> ALTER INDEX ORDERS_REGION_ID_IDX allocate extent (size 200K datafile '/disk6/index01.dbf ');

2.2 Freeing up space

sql> ALTER index ORAERS_ID_IDX DEALLOCATE unused;

There may be insufficient space or wasted space in the process of using the index, which requires new or free space. The above two commands complete the new and release operations. New Oracle on space can help automatically, and if you understand the database, manual additions can improve performance.

3. Re-create the index:

The citation is done automatically by Oracle, and when we operate on the database frequently, the index is also modified, and when we delete a record in the database, the corresponding index does not just make a delete tag, but it still occupies space. The entire block of space is freed unless all the tags in a block are completely deleted. With this time, the performance of the index will decrease. This time you can re-establish a clean index to improve efficiency.

sql> ALTER index ORDERS_REGION_ID_IDX rebuild tablespace index02; You can reproduce an index by using the above command, Oracle re-indexing process:

1, lock the table, the lock table after the other people can not do anything to the table.

2. Create a new (clean) temporary index.

3, delete the old index

4. Rename the new index to the name of the old index

5. Unlock the table.

4. Mobile Cited:

In fact, we also use the command above to move the index to other table spaces, specifying a different tablespace when specifying a table space. The new index is created in the other position, the old to kill, the equivalent of moving.

sql> ALTER index ORDERS_REGION_ID_IDX rebuild tablespace index03;

5. Re-create the index online:

As described above, when creating an index, the table is locked and cannot be used. For a large table, it takes a long time to re-create the index, and in order to meet the needs of the user for table operations, the resulting online re-creation of the index.

sql> ALTER index ORDERS_ID_IDX rebuild online; creation process:

1, Lock the table

2. Create temporary and empty indexes and IoT tables used to present on-going DML. The key value stored by the ordinary table, the data in the table directly stored by the IoT, On-gong DML is also the user to do some additions and deletions of the operation.

3. Unlock the table

4. Create a new index from the old index.

5. The IoT table holds on-going DML information, and the contents of the IoT tables are merged with the newly created indexes.

6, Lock the table

7. Update the contents of the IoT table to the new index again, and kill the old index.

8. Rename the new index to the name of the old index

9. Unlock the table

6. Consolidate Index Fragmentation:

For example, there is space left in many indexes, and the remaining space can be combined by a single command.

Sql> ALTER index ORDERS_ID_IDX COALESCE;

7. Delete the index:

sql> DROP Index Hr.departments_name_idx;

Seven. Analysis of the Index

Check the results cited, the previous introduction, the long time of the cable reference will produce a lot of debris, garbage information and waste of the remaining space. You can improve the performance cited by re-creating indexes.

The analysis index can be completed by a single command, and the results of the analysis are stored in the Index_stats table.

1. View the table that holds the analysis data:

Sql> Select COUNT (*) from index_stats;

COUNT (*)

----------

0

2. Perform the parse index command:

Sql> Analyze index MY_BIT_IDX validate structure;

Index analyzed.

Look again index_stats already has a piece of data

Sql> Select COUNT (*) from index_stats;

COUNT (*)
----------
1

3. Check out the data:

Sql> select Height,name,lf_rows,lf_blks,del_lf_rows from Index_stats;

HEIGHT NAME lf_rows lf_blks del_lf_rows
---------- ---------------------------------------------------------------------- ---------- -----------
2 MY_BIT_IDX 1000 3 100 Analysis data analysis:

4. Analysis

• (height) The height of the primer is 2;
• (name) index is named My_bit_idx;
• (lf_rows) The cited table has 1000 rows of data;
• (lf_blks) occupies 3 blocks;
• (del_lf_rows) Delete 100 Records '
• Here also verify that the above mentioned a problem, delete the 100 data is only marked for deletion, because the total number of data bar is still 1000, occupy 3 blocks, then each block is greater than 333 records, only the deleted data is more than 333 records, when a block is emptied, the total number of data bars will be reduced.

Eight. Advantages and disadvantages of indexes

1. Advantages of the Index

2. Disadvantages of the Index

Nine. The condition of not taking the index

The following are some common scenarios of non-walking indexes caused by improper indexing:

1. Index on t (x, y) but only the Y field in the Where condition.

A full table scan is usually performed.

2. Select COUNT (*) from T usually because the index is smaller than table, Oracle makes an index full scan.

However, if the indexed field contains a null value, the index is not gone because the index value does not contain NULL, and if the index full scan statistic value is inaccurate.

3. Select * from t where F (index_column) =value

If it is not a function index, the Where condition functions on the indexed field does not go to the index

4. Select * FROM t where indexed_column=5 field type needs to be converted.

For example, Indexed_column is a character but a where condition uses a number

5. The Oracle Optimizer considers the full-table scan to be more efficient than walking the index.

In this case, Oracle chooses a full table scan. If the developer feels the need to walk the index, you can use hint to force the index.

6. Failure to analyze,statistics the table in time is inaccurate.

For example, it was originally a small table, and later the volume of data increased greatly. Because statistics is still old, the Oracle optimizer chooses not to walk the index

Author: Bao Xinjian ********************

Plsql_ Performance Optimization Series 01_oracle Index Index