Application Analysis of indexes in Databases

Application Analysis of indexes in Databases
 
Index is the most effective way to improve data query and the most difficult technology to master, because correct index may increase the efficiency by 10000 times, but it is invalid
 
This may result in a waste of database space or even a significant reduction in query performance.
 

Index management cost
 
1. disk space for storing Indexes
 
2. Perform index maintenance generated by data modification operations (insert, update, and delete)
 
3. extra space for rollback during data processing.
 
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.
 
Indexes are classified by storage method.
 
B * Tree Index
 
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 the big object of books.
 
The leaf block is equivalent to the index to a specific book page. Generally, both the index and the unique constraint index use the B * tree index.
 
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.
 
Generally, table fields with too many duplicate values are cited. Bitmap indexes are rarely used in actual intensive OLTP (Data Transaction Processing) Because OLTP performs a large number
 
Oracle locks the data blocks to be operated each time it performs the delete, modify, and create operations.
 
To deadlock. It is advantageous to apply bitmap in OLAP (data analysis and processing), because most of OLAP is the query operation on the database, and generally uses the number
 
Because of the data warehouse technology, it is obvious to use Bitmap indexes to save space for a large amount of data.
 
Index by function
 
Unique Index
 
A unique index has two functions: Data constraints and data indexes. Data constraints are mainly used to ensure data integrity.
 
Each record in the index record corresponds to a unique rowid.
 
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.
 
General Index
 
Generally, indexes do not have data constraints. The function is to create an index table for fields to improve the data query speed.
 

Index by index object
 
Single-Column index (index of table fields)
 
Multi-column index (index of multiple fields in the table)
 
Function Index (index for 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 function returns the value when the input value is fixed.
 
Also fixed. Example:
Create or replace function trunc_add (input_date date) return date deterministic
 
As
Begin
Return trunc (input_date + 1 );
 
End trunc_add;
 
Application Index scan Classification
 
Index unique scan (scan by unique index value)
 
Select * From zl_yhjbqk where hbs_bh = '20140901'
 
Index range scan (scan by index value range)
 
Select * From zl_yhjbqk where hbs_bh> '20140901'
 
Select * From zl_yhjbqk where qc_bh> '20140901'
 
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
 
Under what conditions should indexes be created?
 
Primary Keywords of a table
 
Automatically create a unique index
 
For example, hbs_bh (User ID number) in zl_yhjbqk (User basic information)
 
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)
 
Fields for direct conditional Query
 
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'
 
Fields associated with other tables in the query
 
Fields often establish foreign key relationships
 
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'
 

Fields sorted 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, in which the package
 
Including qc_bh and cb_sx fields)
 
Fields of statistics or group statistics in queries
 
Select max (hbs_bh) from zl_yhjbqk
 
Select qc_bh, count (*) from zl_yhjbqk group by qc_bh
 
Under what circumstances should I create no or less indexes?
 
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.
 
The data table is not in the same data block. In this case, Oracle must read the data block at least twice. Without an index, Oracle will
 
Reading times, the processing speed is obviously faster than using indexes.
 
For example, the table zl_sybm (Department used) generally has only a few records, and no performance optimization is generated for any field except the primary keyword. In fact, if
 
After the statistical analysis of 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)
 
 
 

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.
 
Table fields with duplicate data and average distribution
 
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%. Then, create an index for this table field.
 
Does not increase the database query speed.
 
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 charge sequence number, user ID number, meter reading date, electricity bill generation date, and Operation mark.
 
If all fields are created in one index, the modification, insertion, and deletion time of the data will be increased. In fact, a collection is analyzed.
 
The fee serial number index has reduced the number of records to only a few. If the index is queried by the following fields, the performance will not be significantly affected.
 
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
 
In the following three SQL statements, indexes are created for 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 table access by index rowid (
 
Use rowid to access the Table). Because the returned result columns include columns (hbs_bh, xh_bz) that are not indexed in the current index (qc_bh ),
 
SQL directly returns the result through qc_bh, which is the method to directly return the result through the index.
 

How to recreate an index
 
Alter index table power result table primary key rebuild
 

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's sorting memory may be insufficient.
 
Reference the disk swap space, which seriously affects the work of the server database. The solution is to increase the sorting memory parameters in database startup initialization, as shown in figure
 
If you need to modify a large number of indexes, you can set the sorting memory of more than 10 MB (the default Oracle size is 64 K). After the index is created, you should modify the parameters back.
 
Because such a large sorting memory is generally not used in actual OLTP database applications.