In-depth analysis of the application of indexes in Oracle

Indexing is the most effective way to improve data query and the most difficult technology to master, because correct indexing may increase the efficiency by 10000 times, and invalid indexing may waste database space, the query performance is even greatly reduced.

I. index management costs
1. disk space for storing Indexes
2. Perform index maintenance generated by data modification operations (INSERT, UPDATE, and DELETE)
3. Additional space for rollback during data processing.

Ii. Test the actual data modification:
A table contains fields A, B, and C, and inserts 10000 rows of records at the same time.
When no index is created, the average completion time is 2.9 seconds.
The average completion time of index A is 6.7 seconds.
The average completion time for the index of field A and field B is 10.3 seconds.
The average completion time of index A, B, and C fields is 11.7 seconds.
From the above test results, we can see the impact of indexes on data modification.

Iii. Index classification by storage method
1. B * tree indexes
B * tree indexes are the most common indexes. Their storage structure is similar to the index structure of books. There are two types of storage data blocks: branch and leaf. The branch block is equivalent to a large directory of books, A leaf block is equivalent to a specific index page. Generally, both the index and the unique constraint index use the B * tree index.

2. Bitmap Index
Bitmap index storage is mainly used to save space and reduce ORACLE access to data blocks. It uses bitmap offset to correspond to the table's row ID, bitmap indexes are usually used for table fields with too many duplicate values. Bitmap indexes are rarely used in actual intensive OLTP (Data Transaction Processing) Because OLTP performs a large number of Delete, modify, and create operations on tables, each ORACLE operation locks the data blocks to be operated. Therefore, multi-user operations can easily lead to data block lock waits or even deadlocks. It is advantageous to apply bitmap in OLAP (data analysis and processing) because most of OLAP is a query operation on the database, and generally uses the data warehouse technology, therefore, the use of Bitmap indexes for a large amount of data saves much space.

Iv. indexing by function
1. Unique Index
Unique indexes have two functions: Data constraints and data indexes. Data constraints are mainly used to ensure data integrity, each record in the index record generated by a unique index corresponds to a unique ROWID.

2. Primary keyword index
The index generated by the primary keyword index is the same as the unique index, but it is automatically created when the database creates the primary keyword.

3. General Indexes
Generally, indexes do not have data constraints. The function is to create an index table for fields to improve the data query speed.

5. Index classification by index object
1. Single Column index (index of individual fields in the form)
2. Multi-column index (index of multiple fields in the table)
3. function indexes (index of function operations on fields)

How to Create a function index:
Create index billing date index on GC_DFSS (trunc (sk_rq ))
Create index full customer ID index on yhzl (qc_bh | kh_bh)
After the function is indexed, if the current session is to be referenced, set query_rewrite_enabled to TRUE for the current session.
Alter session set query_rewrite_enabled = true

Note: If the user function is indexed, the user function should add the deterministic parameter, which means that the return value of the function is fixed when the input value is fixed. Example:
Create or replace function trunc_add (input_date date) return date deterministic
As
Begin
Return trunc (input_date + 1 );
End trunc_add;

6. Application Index scan Classification
1. index unique scan (SCAN by unique index value)
Select * from zl_yhjbqk where hbs_bh = '20140901'

2. index range scan (SCAN by INDEX value RANGE)
Select * from zl_yhjbqk where hbs_bh> '20140901'
Select * from zl_yhjbqk where qc_bh> '20140901'

3. index fast full scan (fast full 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

VII. Under what circumstances should indexes be created
1. The primary key word of the table, which automatically creates a unique index
For example, hbs_bh (User ID number) in zl_yhjbqk (User basic information)

2. Unique field constraints
ORACLE uses indexes to ensure data integrity
For example, lc_bh + hj_sx in lc_hj (process link) (process number + link sequence)

3. fields directly queried by conditions
Fields used for condition constraints in SQL
For example, qc_bh in zl_yhjbqk (User basic information)
Select * from zl_yhjbqk where qc_bh = '20140901'

4. fields associated with other tables in the query are often associated with foreign keys.
For example, jldb_bh (metering point table number) in zl_ydcf (electric component)
Select * from zl_ydcf a, zl_yhdb B where a. jldb_bh = B. jldb_bh and B. jldb_bh = '20140901'

5. Sorting fields in the query
If the sorted field is accessed through the index, the sorting speed will be greatly improved.
Select * from zl_yhjbqk order by qc_bh (create qc_bh index)
Select * from zl_yhjbqk where qc_bh = '000000' order by cb_sx (create qc_bh + cb_sx index, note: it is just an index, including qc_bh and cb_sx fields)

6. fields of statistics or group statistics in the query
Select max (hbs_bh) from zl_yhjbqk
Select qc_bh, count (*) from zl_yhjbqk group by qc_bh

8. Under what circumstances should I create no or less indexes?
1. Too few table records
If a table has only five records and uses indexes to access the records, you must first access the index table and then access the data table through the index table. Generally, the index table and the data table are not in the same data block, in this case, ORACLE must read data blocks at least twice. ORACLE reads all the data once without an index, and the processing speed is obviously faster than that with an index.

For example, the table zl_sybm (Department used) generally has only a few records, and no performance optimization will be produced for indexing any field except the primary keyword, in fact, if you perform statistical analysis on this table, ORACLE will not use the index you created, but will automatically perform full table access. For example:
Select * from zl_sybm where sydw_bh = '000000' (performance optimization is not performed for indexing sydw_bh)

2. frequently inserted, deleted, and modified tables
For business tables that are frequently processed, minimize the number of indexes allowed by queries, such as zl_yhbm, gc_dfss, gc_dfys, and gc_fpdy.

3. Duplicate and evenly distributed table fields
Assume that A table has 0.1 million rows of records and one field A has only T and F values, and the distribution probability of each value is about 50%, creating an index for this table field A generally does not increase the database query speed.

4. Table fields that are frequently queried with the primary field but have a large index value
For example, in the gc_dfss (electricity bill actually collected) table, you can query a collection by billing sequence number, user ID number, meter reading date, electricity bill generation date, and Operation mark, if all fields are created in one index, the data modification, insertion, and deletion time will be increased, from the actual analysis of a collection, if the index by the billing serial number has been reduced to only a few records, if you search by the following fields, it will not have a big impact on performance.

9. How to return results only through Indexes
An index generally contains one or more fields. If an index is applied directly without access to the table, the returned results will greatly improve the database query performance. Compare the following three SQL statements, and index the hbs_bh and qc_bh fields of the table zl_yhjbqk:

1 select hbs_bh, qc_bh, xh_bz from zl_yhjbqk where qc_bh = '20170101'
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 = '20170101'
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 = '000000'
Execution path:
Select statement, GOAL = CHOOSE 1 265 1060
Index range scan dlyx Area INDEX 1 265 1060
Average execution time (0.062 seconds)
The execution result shows that the efficiency of the third SQL statement is the highest. The execution Path shows that 1st and 2 SQL statements run the TABLE ACCESS BY INDEX ROWID (ACCESS TABLE through ROWID) step, because the returned result column contains the currently used INDEX (qc_bh) unindexed columns (hbs_bh, xh_bz), and 3rd SQL statements directly return results through QC_BH, which is the method to directly return results through indexes.

10. How to quickly create an index for a big data table
If a table has more than 1 million records, it may take a long time to index one of the fields, or even cause the server database to crash, when creating an index, ORACLE needs to retrieve and sort all the content of the index field. If the data volume is large, the server may not have enough sort memory and reference the disk swap space, this will seriously affect the work of the server database. The solution is to increase the sorting memory parameter during database startup initialization. If you want to perform a large number of index modifications, you can set the sorting memory of more than 10 MB (the default ORACLE size is 64 K ), after the index is created, the parameter should be modified back, because such a large sorting memory is generally not used in the actual OLTP database application.