An in-depth analysis of the application of indexes in Oracle _oracle

Indexing is the most effective way to improve data querying and is the most difficult technology to master, because proper indexing can increase efficiency by 10,000 times times, and invalid indexes may be a waste of database space, or even greatly reduce query performance.

I. Management costs of indexes
1. Storage Index disk space
2, the implementation of data modification operations (INSERT, UPDATE, DELETE) generated by the index maintenance
3, in the processing of the need for additional fallback space.

Two. Actual Data modification test:
One table has fields A, B, and C, while inserting 10000-line recording tests
The average finish time is 2.9 seconds when no index is built
The average completion time for a Jianjian index is 6.7 seconds.
Average completion time is 10.3 seconds after the Jianjian index for a and B characters
The average completion time is 11.7 seconds after indexing the A field, b field, and C field
From the above test results can be seen clearly the impact of the index on data modification

Three. Index by storage Method classification
1.b* Tree Index
b* Tree Index is the most commonly used index, its storage structure similar to the index structure of the book, there are branches and leaves two types of storage data blocks, branch block equivalent to the large directory of books, leaf block equivalent to the specific page index. Both the general index and the unique constraint index use the b* tree index.

2. Bitmap indexing
Bitmap index storage is mainly used to save space, reduce Oracle access to data blocks, it uses a bitmap offset to the table row ID number, using a bitmap index is usually too many duplicate values of the table field. Bitmap indexes are used less in actual-intensive OLTP (data transaction processing), because OLTP will be a large number of delete, modify, new operation, Oracle each operation will be the data block to operate lock, so many people operation is very easy to produce block lock wait even deadlock phenomenon. In OLAP (data analysis Processing) The application of bitmap has advantages, because most of OLAP is the database query operations, and generally using data Warehouse technology, so a large number of data using bitmap index to save space is more obvious.

Four. Index by Function classification
1. Unique index
The unique index has two functions, one is data constraint, the other is Data index, in which data constraint is mainly used to guarantee the integrity of the data, and each record in the index record produced by the unique index corresponds to a unique rowid.

2. Primary keyword Index
The primary key index produces an index that is the same as a unique index, except that it is created automatically when the database establishes the primary key.

3. General Index
The general index does not produce the data constraint function, its function mainly is establishes the index table to the field, enhances the data query speed.

Five. Index by Index Object classification
1. Single column index (Index of Table single field)
2. Multiple-column index (index of multiple fields in a table)
3. Function index (Index of a field for function operations)

How to establish a function index:
CREATE index charge Date index on GC_DFSS (trunc (SK_RQ))
Create index full customer number index on YHZL (qc_bh| | KH_BH)
After the function has been indexed, if the current session wants to refer to the query_rewrite_enabled that should set the current session to true.
Alter session Set Query_rewrite_enabled=true

Note: If the user function is indexed, the user function should be added with the deterministic parameter, meaning that the return value is fixed when the function is fixed in the input value. Cases:
Create or Replace function Trunc_add (input_date date) return date deterministic
As
Begin
return trunc (input_date+1);
End Trunc_add;

Six. Application of the Index scan classification
1.INDEX Unique SCAN (scanned by index unique value)
SELECT * from zl_yhjbqk where hbs_bh= ' 5420016000 '

2.INDEX Range SCAN (scanned by index value range)
SELECT * from zl_yhjbqk where hbs_bh> ' 5420016000 '
SELECT * from zl_yhjbqk where qc_bh> ' 7001 '

3.INDEX Fast Full SCAN (Quick scan by index value)
Select Hbs_bh from Zl_yhjbqk ORDER by HBS_BH
Select COUNT (*) from ZL_YHJBQK
Select Qc_bh from Zl_yhjbqk GROUP by QC_BH

Seven. Under what circumstances should an index be established
1. The primary key of the table, automatically establishing a unique index
HBS_BH (User ID number), such as ZL_YHJBQK (customer base)

2. table field UNIQUE constraint
Oracle leverages indexes to ensure data integrity
such as LC_HJ (process link) in the LC_BH+HJ_SX (process number + sequence of links)

3. Field of Direct condition query
fields for conditional constraints in SQL
QC_BH (Area book number), such as zl_yhjbqk (user base)
SELECT * from zl_yhjbqk where qc_bh= ' 7001 '

4. A foreign key relationship is often established in a query with fields associated with other tables
such as ZL_YDCF (electrical components) in the JLDB_BH (Metering point table number)
SELECT * from ZL_YDCF a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh= ' 540100214511 '

5. Sorted fields in a query
Sorted fields if accessed by index that will greatly improve the sorting speed
SELECT * from Zl_yhjbqk ORDER by QC_BH (establish QC_BH index)
SELECT * from zl_yhjbqk where qc_bh= ' 7001 ' ORDER by CB_SX (establish QC_BH+CB_SX index, note: Only one index, including QC_BH and CB_SX fields)

6. Fields for statistical or grouped statistics in a query
Select Max (HBS_BH) from Zl_yhjbqk
Select Qc_bh,count (*) from ZL_YHJBQK GROUP by QC_BH

Eight. Under what circumstances should not build or less index
1. Too few table records
If a table has only 5 records, using the index to access the record, the first need to access the index table, and then through the Index table access to the data table, the General Index table and the data table is not the same block, in this case, Oracle to read the data block at least two times. Instead of indexing, Oracle will read all of the data one at a time, and the processing speed will obviously be faster than indexing.

If the table ZL_SYBM (use department) generally has only a few records, in addition to the primary key word for any one word Jianjian index will not produce performance optimization, in fact, if the table for the statistical analysis of Oracle will not use the index you built, but automatically perform full table access. Such as:
SELECT * from ZL_SYBM where sydw_bh= ' 5401 ' (indexing SYDW_BH does not produce performance optimizations)

2. Frequently inserted, deleted, modified tables
Some frequently-processed business tables should be minimized as the query allows, such as zl_yhbm,gc_dfss,gc_dfys,gc_fpdy business tables.

3. Table fields with duplicate data and average distribution
If a table has 100,000 rows of records, there is a field a only T and F two values, and the probability of distribution of each value is about 50%, then the table a character Jianjian index generally does not improve the database query speed.

4. Frequently and the main field of a query but the main field index values more than the table field
If the GC_DFSS (electricity charge) table is often by the serial number, household identification number, meter reading date, electricity tariff occurrence, operation signs to specific query a collection of circumstances, if all the fields are built in an index that will increase the data modification, insertion, deletion time, Analyze a collection from the actual if the record is reduced to just a few by the index of the charge number, if you press the following few fields the index query will not have much effect on performance.

Nine. How to return results only via index
An index typically includes one or more fields, and returns results if you can apply the index directly without accessing the table that will greatly improve the performance of the database query. Compares the following three SQL, where the HBS_BH and Qc_bh fields of table zl_yhjbqk are indexed:

1 Select Hbs_bh,qc_bh,xh_bz from zl_yhjbqk where qc_bh= ' 7001 '
Execution path:
SELECT STATEMENT, GOAL = CHOOSE 11 265 5565
TABLE ACCESS by INDEX ROWID dlyx zl_yhjbqk 11 265 5565
Index RANGE SCAN dlyx Area index 1 265
Average execution time (0.078 seconds)

2 Select Hbs_bh,qc_bh from zl_yhjbqk where qc_bh= ' 7001 '
Execution path:
SELECT STATEMENT, GOAL = CHOOSE 11 265 3710
TABLE ACCESS by INDEX ROWID dlyx zl_yhjbqk 11 265 3710
Index RANGE SCAN dlyx Area index 1 265
Average execution time (0.078 seconds)

3 Select Qc_bh from zl_yhjbqk where qc_bh= ' 7001 '
Execution path:
SELECT STATEMENT, GOAL = CHOOSE 1 265 1060
Index RANGE SCAN dlyx area 1 265 1060
Average execution time (0.062 seconds)
From the execution result you can see that the third SQL is the most efficient. The execution path shows that 1th, 2 SQL performs more than one step of table access by INDEX ROWID (Access table via ROWID) because the returned result column includes columns (HBS_BH,XH_BZ) that are not indexed in the currently used index (QC_BH). The 3rd SQL returns the result directly via QC_BH, which is the way to return results directly from the index.

10. How to quickly create a new index of large data scale
If a table has a record of more than 1 million, it can take a long time to Jianjian an index on one of the characters, or even cause the server database to panic, because Oracle is going to take all the contents of the index field out and do a full order when the index is being built, A large amount of data may cause the server to have low order memory and reference disk swap space, which can seriously affect the server database. The workaround is to increase the sort memory parameters in database startup initialization, and if you want to make a large number of index modifications, you can set up more than 10M of sorted memory (the Oracle default size is 64K), and the parameters should be modified back after the index is established. Because such a large amount of sorted memory is not generally used in actual OLTP database applications.