Plsql_performance optimization series 01_Oracle Index, plsql01_oracle

Plsql_performance optimization series 01_Oracle Index, plsql01_oracle

2014-06-01 BaoXinjian

I. Summary

In PLSQL query optimization, Index is the most widely used and exposed concept. I also think that Index selection and optimization are important concepts in the program optimization process, especially when it comes to PLSQL Performance Optimization

Index concepts

• An index can consist of one or more columns,
• Setting an index for a column is actually sorting the column content in a certain way. When retrieving data, you can retrieve the sorted data and retrieve the last valid data.
• In this way, you do not need to perform a full table scan. You can also apply many algorithms to improve the search efficiency.
• Database multi-purpose binary data retrieval

Index connection method

• Hash Join
• Nested Loops
• Merge Join

Index category

• B-Tree Index
• Reverse Index
• Descending Index
• Bitmap Index
• Function Index
• After an index is created, you must analyze the index to make the index take effect.
• Differences between primary key and unique index

Hints used in the Index

• Forced Index

Index creation cost

• During basic table maintenance, the system must maintain indexes at the same time. unreasonable indexes will seriously affect system resources, mainly on CPU and I/O;
• Inserting, updating, and deleting data results in a large number of db file sequential read lock waits;

In some cases, although index is defined in the query column, PLSQL does not take the index of this column, because Oracle is a Role-based parsing plan, one is that the efficiency of using Index is lower than that of scanning the entire table in some cases based on Cost, instead of creating an Index, PLSQL performance will multiply, and worse, creating an Index will also lead to worse performance than having no Index. Here is a misunderstanding, so I will mention it a little ;)

In addition, sometimes the index you create may be invalid due to space or other reasons, which may cause some programs to have no problems and suddenly lead to performance problems, which is a very high performance problem, therefore, EBS DBAs have high requirements to monitor system exceptions.

Ii. Index connection method

1. Hash Join

2. Nested Loops

3. Merge Join

Detailed analysis

1. Hash Join

(1). Overview

I. Read the data of a table, place it in the memory, and create a bitmap index with a unique keyword.

Ii. Read another table and compare it with a table in memory using the Hash algorithm.

(2). intended audience

I. Large tables join small tables

Ii. Two large tables

2. Nested Loops

(1). Overview

I. Circular appearance record

Ii. Compare one by one and check whether the connection with the internal mark meets the conditions

(2). intended audience

Small tables drive large tables and return fewer result sets

3. Merge Join

(1). Overview

I. Perform table access full for two tables

Ii. Sort the results of table access full

Iii. Perform merge join to merge the sorting Structure

(2). intended audience

Use rowid to access data

 

Iii. Index classification

1. B-Tree Index

2. Reverse Indexing

3. Descending Index

4. Bitmap Index

5. Function Indexing

6. After an index is created, you must analyze the index to make the index take effect.

7. Differences between primary key and unique index

Detailed analysis

1. B-Tree Index

(1). Overview

The most common index structure. The default index is this structure.

Applicable to high-base data columns (the column values are mostly different)

(2). Creation Method

Create index index_name ON wip_entities (wip_entity );

2. Reverse Indexing

(1). Overview

(2). Creation Method

3. Descending Index

(1). Overview

Applicable to columns in descending order

(2). Creation Method

Create index index_name ON wip_entities (wip_entity DESC );

4. Bitmap Index

(1). Overview

Applicable to low-base data columns (the column values are mostly the same)

(2). Creation Method

Create bitmap index index_name ON wip_entities (sex );

5. Function Indexing

(1). Overview

Applicable to columns that require functions for this column

(2). Creation Method

Create index index_name ON wip_entities (TRUNC (creation_date ));

6. Analyze Indexes

Analyze index index_name compute statistics;

7. Differences between primary key and unique index

(1). The primary key is a constraint, and the unique index is only an index.

(2). The primary key cannot be empty, and the uniqueness can be empty.

 

4. Use Hints in the index-Force Index

1. Create a test table and test indexes

 1 CREATE TABLE dba_name ( 2  3 username VARCHAR(100), 4  5 password VARCHAR(100) 6  7 ) ; 8   9 CREATE INDEX index_t ON dba_name(username) ;

2.Method Comparison

(1). No forced index is 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 the index is defined in the query field, PLSQL does not take the index of this field, because Oracle is based on Role in the parsing plan, one is based on Cost

In some cases, the efficiency of using Index is lower than that of scanning the entire table. Instead of creating an Index, the performance of PLSQL will multiply, and worse, creating an Index will also lead to worse performance than having no Index. Here is a misunderstanding, so I will mention it a little ;)

 

5. Index rules

1. Weigh the relationship between the number of indexes and DML, that is, DML inserts and deletes data.

Here we need to weigh a problem. The purpose of index creation is to improve the query efficiency. However, too many indexes will affect the speed of data insertion and deletion, because the table data we modified, the index also needs to be modified. Here we need to weigh whether our operation is to query more or modify more.

2. Place the index and the corresponding table in different tablespaces.

When reading a table, the table and the index are both performed. If the table and the index are in the same tablespace, resource competition will occur. If the table and the index are in the same tablespace, they can be executed in parallel.

3. It is best to use blocks of the same size.

Oracle has five I/O reads by default. If you define six or ten I/O reads, you must read the I/O twice. Preferably an integer multiple of 5 to improve efficiency.

4. If a table is large, it takes a long time to create an index. Because index creation also produces a large amount of redo information, you can set no or no redo information during index creation. As long as the table data exists, the index fails and cannot be created again. Therefore, you do not need to generate redo information.

5. You should create an index based on the specific business SQL statements, especially the where condition and the order of where conditions. Try to put the filtering range at the end, because the SQL Execution is from the back to the front

 

6. Index operations

Change index:

SQL> alter index employees_last _ name_idx storage (next 400 K maxextents 100); after an index is created, you can modify its parameters. View related documents for details

2. Adjust the index space:

2.1 new space added

SQL> alter index orders_region_id_idx allocate extent (size 200 K datafile '/disk6/index01.dbf ');

2.2 release space

SQL> alter index oraers_id_idx deallocate unused;

When using an index, there may be insufficient space or a waste of space. In this case, you need to add or release space. The preceding two commands are used to complete the ADD and release operations. Oracle can automatically help with the addition of space. Manual addition can improve the performance if you know the database.

3. re-create an index:

Oracle automatically completes the process. When we perform frequent database operations, the index is also modified. When we delete a record in the database, the corresponding index does not only make a delete mark, but it still occupies space. The space of a block is released only when all the tags of a block are deleted. In this way, the index performance will decrease after a long time. At this time, you can create a new clean index to improve efficiency.

SQL> alter index orders_region_id_idx rebuild tablespace index02; the preceding command can be used to recreate an index. The oracle re-indexing process:

1. After locking a table, no one else can perform any operation on the table.

2. Create a New (clean) Temporary index.

3. Delete the old index

4. Rename the new index as the old index name.

5. Unlock the table.

4. Cited by mobile:

In fact, we also use the above command to move the index to other tablespaces and specify different tablespaces when specifying the tablespace. When a new index is created in another location, the old index is moved.

SQL> alter index orders_region_id_idx rebuild tablespace index03;

5. re-create an index online:

As described above, when creating an index, the table is locked and cannot be used. It takes a long time to re-create an index for a large table. In order to meet your needs for table operations, this online re-create index is generated.

SQL> alter index orders_id_idx rebuild online; creation process:

1. Lock a table

2. Create temporary and empty indexes and IOT tables to store on-going DML. The key value stored in a common table, and the data in the table directly stored in the table referenced by IOT. on-gong DML is the addition, deletion, and modification operations performed by the user.

3. unlock a table

4. Create a new index from the old index.

5. the IOT table stores the on-going DML information. The content of the IOT table is merged with the newly created index.

6. Lock a table

7. Update the content of the IOT table to the new index again to remove the old index.

8. Rename the new index as the old index name.

9. unlock a table

6. integrated index fragmentation:

For example, if many indexes have surplus space, you can use a single command to integrate the remaining space.

SQL> alter index orders_id_idx coalesce;

7. Delete the index:

SQL> drop index hr. departments_name_idx;

 

VII. Index Analysis

Check the effects of indexing. As described earlier, indexing takes a long time to produce a large amount of fragmentation, junk information, and waste of space. You can re-create an index to improve the performance.

You can use a command to complete the analysis index. The analysis results are stored in the index_stats table.

1. view the table that stores the analysis data:

SQL> select count (*) from index_stats;

COUNT (*)

----------

0

2. Execute the analysis index command:

SQL> analyze index my_bit_idx validate structure;

Index analyzed.

Check that index_stats has a data record.

SQL> select count (*) from index_stats;

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

3. query 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 cited HEIGHT is 2;
• (NAME) The index NAME is MY_BIT_IDX;
• (LF_ROWS) the cited table contains 1000 rows of data;
• (LF_BLKS) occupies three blocks;
• (DEL_LF_ROWS) Delete 100 records'
• This also verifies the problem mentioned above. The 100 data records deleted are only marked as deleted, because the total number of data records is still 1000, occupying 3 blocks, therefore, if each block contains more than 333 records and the deleted data contains more than 333 records, the total number of data records is reduced when a block is cleared.

 

8. Advantages and Disadvantages of Indexes

1. Advantages of Indexes

 

2. Index disadvantages

 

 9. No index is taken

The following are some common cases of non-indexing caused by improper indexing:

1. Index on t (x, y), but the where condition only contains the y field.

Generally, a full table scan is performed.

2. select count (*) from t generally because the index is smaller than the table, oracle performs index full scan.

However, if the index field contains a NULL value, the index is not taken, because the index value does not include null, and the index full scan statistical value is inaccurate.

3. select * from t where f (index_column) = value

If it is not a function index, the where condition performs function operations on the index field without indexing.

4. select * from t where indexed_column = 5 the field type needs to be converted.

For example, indexed_column is a character, but numbers are used in the where condition.

5. The oracle optimizer believes that full table scan is more efficient than indexing.

In this case, oracle selects full table scan. If developers think it is necessary to take the index, they can use the hint to force the index.

6. The table is not analyzed in time, and the statistics is not accurate.

For example, it was originally a small table, and later the data volume increased significantly. Since statistics is still old, oracle optimizer will choose not to take the index

 

********************Author: Bao Xin********************

Oracle: how to obtain the index creation script based on the index name

Select dbms_metadata.get_ddl ('index', 'bms _ ACC_REM_UNI ', 'mdd') from dual;

INDEX: Specify the INDEX type.
BMS_ACC_REM_UNI: Index name

MDD: schema, which specifies a user.

You checked the information online, and I will detail it.

How Does oracle call a created index when writing a query statement? (For example, an index named T_index already exists)

You do not need to explicitly call the index. Oracle will find the appropriate index at the execution stage.
However, whether or not indexes can be efficiently utilized requires some SQL writing skills.
For example, you can use common query fields as indexes and less like statements. If you need more information, you 'd better find information on oracle query optimization.