Index efficiency optimization

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 can be a waste of database space, or even greatly reduce query performance.   

Administrative cost of indexes

1, disk space to store indexes

2, index maintenance from performing data modification operations (INSERT, UPDATE, DELETE)

3, and additional fallback space to be required for data processing.

Actual data modification test:

A table has fields A, B, C, and inserts 10000 rows of records test

The average time to complete without indexing is 2.9 seconds

The average completion time for a Jianjian index is 6.7 seconds.

The average finish time for a and B-word Jianjian is 10.3 seconds

The average finish time for a, B, and C fields is 11.7 seconds

from the above test results you can see the effect of indexes on data modification

Index by storage method

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 pages of the index. Both the general index and the unique constraint index use the b* tree index. The

Bitmap index

Bitmap storage is used primarily to conserve space and reduce Oracle access to data blocks, which correspond to the row ID numbers of the table using bitmap offsets, which are typically a table field with too many duplicate values. 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. The

Index is categorized by functional

Unique index

Unique index has two functions, one is data constraint, the other is Data index, in which data constraints are used to guarantee the integrity of data, each record in the index record produced by the unique index corresponds to a unique rowid. The

Primary key index

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. The

General index

General index does not produce data constraints, and its function is primarily to establish an indexed table of fields to improve the speed of data queries.

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. contrast to

The next three SQL, where the HBS_BH and Qc_bh fields for 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 you don't typically use such a large amount of sort memory in an actual OLTP database application