Index is the most effective way to improve data query, and it is also the most difficult to fully and master, because the correct index may increase efficiency by 10,000 times, and invalid index may be wasted to waste database space, and even significantly reduce query performance.
Index management cost
1, store the indexed disk space
2, execute index maintenance generated by Insert, Update, Delete
3. Never return space in data processing.
Actual data modification test:
A table has a field A, B, C, and inserts a 10,000 line record test at the same time.
The average completion time is 2.9 seconds without building an index.
The average completion time is 6.7 seconds after the index of the A field is 6.7 seconds.
The average completion time is 10.3 seconds after the index of the A field and the B field is 10.3 seconds.
The average completion time is 11.7 seconds after the index is built on the A field, the B field, and the C field.
From the above test results, it can clearly see the effects of indexing on data modification.
Index is classified by storage method
B * tree index
B * Tree index is the most common index, its storage structure similar to the index structure of the book, two types of branches and leaves, the branch block equivalent to the big directory of the book, the leaves are equivalent to the specific page of the indexed . The general index and unique constraint index use the B * tree index.
Bitmap index
The bitmap index storage is mainly used to save space, reducing Oracle's access to data blocks, which use bitmap offset to correspond to the line ID number of the table, using bitmap indexes generally repeating the table field. The bitmap index is relatively used in actual intensive OLTP (data transaction), because OLTP will make a lot of deletion, modification, new operations, and Oracle lock each time you operate, so Multi-person operation is easy to generate data block lock waiting or even deadlock. Apply bitmap in OLAP (data analysis processing), because most of the OLAP is a query operation of the database, and generally use data warehouse technology, a large amount of data is saved by bitmap index.
Index by function classification
Single index
The only index has two functions, one is a data constraint, one is a data index, where data constraints are mainly used to ensure data integrity, and each record in the index record generated by the unique index corresponds to a unique RowID.
Main keyword index
The index of the index generated by the primary keyword index is only the system automatically established when it is established when the database is established.
General index
The general index does not generate data constraints, and its function is mainly to establish an index table for fields to increase data query speed.
Index by index object classification
A single column index (the index of a single field)
Multi-column index (index of multiple fields)
Function index (index of function calculation for fields)
Establish a function of 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 is indexed, if the current session is to reference the query_rewrite_enabled to True to set the current session.
ALTER session set query_rewrite_enabled = true
Note: If the user function is indexed, the user function should add a deterministic parameter, meaning that the function is also fixed if the function is fixed in the input value fixed. example:
CREATE OR REPLACE FUNCTION TRUNC_ADD (INPUT_DATE DATE) RETURN DATE DETERMINISTIC
AS
Begin
Return Trunc (Input_Date 1);
END TRUNC_ADD;
Apply indexed scan classification
INDEX UNIQUE SCAN (Scan by index) Select * from zl_yhjbqk where hbs_bh = '5420016000'
Index Range Scan (Scan by the index value)
Select * from zl_yhjbqk where hbs_bh> '5420016000'
SELECT * from ZL_YHJBQK Where Qc_BH> '7001'
INDEX FAST FULL SCAN (Scan by the 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
In the case, index should be established
Lord keyword
Automatically establish a unique index
Such as HBS_BH in ZL_YHJBQK (user basic situation) (account number number)
The field of the table is unique
Oracle uses an index to ensure data integrity
Such as LC_BH HJ_SX in LC_HJ (process link) (process number link order)
Direct condition query field
Fields for condition constraints in SQL
Such as qc_bh in ZL_YHJBQK (Basic Situation)
Select * from zl_yhjbqk where qc_bh = '
7001 '
Inquiries in the field associated with other tables
Fields often establish foreign key relationships
Such as JLDB_BH in ZL_YDCF (meter table number)
Select * from zl_ydcf a, zl_yhdb b where A.jldb_bh = B.jldb_bh and b.jldb_bh = '
540100214511 '
Fields sort in the query
Sort field If you go to access by an index, it 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 (established QC_BH CB_SX index, Note: just an index, including QC_BH and CB_SX fields)
Fields of statistics or grouping statistics in queries
SELECT MAX (HBS_BH) from ZL_YHJBQK
SELECT QC_BH, Count (*) from ZL_YHJBQK GROUP BY QC_BH
Under what circumstances, no or less construction
That is much less
If a table has only 5 records, use an index to access the record, first you need to access the index table, then access the data table by index table, the general index table is not in the same data block, in which case Oracle is at least round-trip Read the data block twice. Without the index, Oracle will read all the data once, and the processing speed will obviously be fast than the index.
If the ZL_SYBM (use department) is generally only a few records, in addition to the main keyword, it will not generate performance optimization. In fact, if you have statistical analysis of this table, Oracle will not build it. Index, but automatically perform full meter access. Such as:
Select * from zl_sybm where sydw_bh = '5401' (establish index for SYDW_BH does not produce performance optimization)
Insert, delete, modified tables for some frequently processed business tables should minus indexing, such as ZL_YHBM, GC_DFSS, GC_DFYS, GC_FPDY, etc. if the query is allowed.
Data repeat and distributed average table field
If a table has 100,000 row records, there is only one value of T and F, and the distribution probability of each value is approximately 50%, then the index of this table A field will generally not improve the query speed of the database. .
Table fields that are often queried by the primary field, but the primary field index value is more
If the GC_DFSS (electricity fee-increasing) table is often in accordance with the charging number, the household identification number, the metrics, the electricity fee is produced, and the operation mark is specifically queried. If all the fields are built in an index That will increase the data modification, insert, delete time, from actually analyzing a payment If the number of sequence number indexes will have been reduced to only a few, if the index query will not be in the rear of several fields Produce too much impact.
How to return only by index
A index generally includes a single or multiple fields, and if you can directly apply the index to directly apply the index, the result will greatly improve the performance of the database query. Compare the following three SQLs, which establishes an index on the HBS_BH and QC_BH fields of Table ZL_YHJBQK:
1 SELECT HBS_BH, QC_BH, XH_BZ from ZL_YHJBQK Where Qc_BH = '
7001 '
Perform path:
Select Statement, Goal = Choose 11 265 5565
Table Access by Index Rowid Dlyx ZL_YHJBQK 11 265 5565
Index Range Scan Dlyx District Index 1 265
Average execution time (0.078 seconds)
2 SELECT HBS_BH, QC_BH from ZL_YHJBQK Where qc_bh = '
7001 '
Perform path:
Select Statement, Goal = Choose 11 265 3710
Table Access by Index Rowid Dlyx ZL_YHJBQK 11 265 3710
Index Range Scan Dlyx District Index 1 265
Average execution time (0.078 seconds)
3 SELECT QC_BH from ZL_YHJBQK Where QC_BH = '
7001 '
Perform path:
Select Statement, Goal = Choose 1 265 1060
Index Range Scan Dlyx District Index 1 265 1060
Average execution time (0.062 seconds)
It can be seen from the execution result to the highest efficiency of the third SQL. The execution path can be seen that sections 1, 2 SQL perform more Table Access By Index RowID (through the RowID Access Table) step, because the returned result column includes columns (HBS_BH) in the current index (qc_bh) (HBS_BH) XH_bz), and the 3 SQL returns the result by QC_BH, which is a method of directly returning the result by indexing.
How to rebuild an index
Alter Index Summer Results Design Design Designation Rebuild
How to quickly create an index of a large data scale
If a table records more than 1 million, it is necessary to build an index for one of the fields that may take a long time, and even lead to the server database dead machine, because Oracle is taken out and processed when it is indexed Sort, if the amount of data is large, it may cause the server to sort memory and reference disk switched space, which will seriously affect the work of the server database. Solution is to increase the sort memory parameters in the database startup initialization, if you want to make a lot of index modification, you can set 10M
The above sort memory (Oracle default size is 64K), and the parameters should be modified back after the index is completed, as such large sort memory is generally not used in the actual OLTP database application.
