Plsql_ Performance Optimization Index introduction (concept)

2014-06-01 Baoxinjian in Capgemini

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

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

I. How to connect an index

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

Ii. 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

Three. Use of hints in the index

1. Build a test table, and test the index

1 CREATE TABLEDba_name (2 3UsernameVARCHAR( -),4 5PasswordVARCHAR( -)6 7 ) ;8   9 CREATE INDEXindex_t onDba_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;)

Thanks and regards