Analysis of the application of index in database

Data | database | index
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.







Administrative cost of indexing



1. Storage Index disk space



2, the implementation of data modification operations (INSERT, UPDATE, DELETE) generated by the index maintenance



3, in the data processing, back to the need for additional fallback space.







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







Index by storage Method classification



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.



Bitmap index



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.







Index by functional classification



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.







Primary key 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.



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.











Index by Index Object category







Single-column Index (Index of a single field in a table)



Multiple-column index (index of multiple fields in a table)



Function index (Index of function operations on a field)



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;







Scan classification with index applied



Index unique SCAN (scanned by index unique value)



SELECT * from zl_yhjbqk where hbs_bh= ' 5420016000 '



Index range SCAN (scanned by index value range)



SELECT * from zl_yhjbqk where hbs_bh> ' 5420016000 '



SELECT * from zl_yhjbqk where qc_bh> ' 7001 '



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







Under what circumstances should an index be established



Primary key for Table



Automatically establish a unique index



HBS_BH (User ID number), such as ZL_YHJBQK (customer base)



Table's 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)



fields for direct conditional queries



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 '



Fields associated with other tables in a query



Field often establishes a foreign key relationship



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 '







Fields sorted in the 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)



A field in a query that counts or groups statistics



Select Max (HBS_BH) from Zl_yhjbqk



Select Qc_bh,count (*) from ZL_YHJBQK GROUP by QC_BH







Under what circumstances should an index not be built or built less



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)







Tables that are frequently inserted, deleted, and modified



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







table field 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.







A table field that is frequently queried with the main field but has a higher index value than the primary 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.







How to return results only through an 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.







How to rebuild an index



Alter INDEX Table power result table primary key rebuild







How to quickly create a new index for a 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.