What is a field in the database should be indexed?

Application Analysis of indexes in Databases

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.   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 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. 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.   Index by function 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.   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 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;   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 =' 7001' 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 whereA. jldb_bh = B. jldb_bhAnd B. jldb_bh =' 540100214511' 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, 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. 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*FromZl_sybmWhereSydw_bh = '20140901' (performance optimization is not performed for sydw_bh index creation)   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%, creating an index for this table field A generally 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 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 then index the query by the following fields will not have a big impact on the performance.   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_bzFrom 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 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 execute the TABLE ACCESS BY INDEX ROWID (ACCESS TABLE through ROWID) step, because the returned result column containsCurrently Used Index(Qc_bh) non-indexed columns (hbs_bh, xh_bz), and 3rd SQL statements directly return results through QC_BH, which is the method of directly returning results through indexes.   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 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. You can set this parameter if you want to modify a large number of indexes. 10 M The above sorting memory (the default ORACLE size is 64 K) should be modified back after the index is created, because such a large sorting memory is generally not used in actual OLTP database applications.