Application analysis of the hottest index in Oracl

2022-07-30
  • Detail

Analysis on the application of index in Oracle

index is the most effective method to improve data query and the most difficult technology to master comprehensively, because the correct index may improve the efficiency by 10000 times, while the invalid index may waste database space and even greatly reduce the query performance

index management cost

1. Disk space for storing indexes

2. Index maintenance generated by data modification operations (insert, update, delete)

3. Additional fallback space is required during data processing

actual data modification test:

a table has fields a, B and C, and 10000 rows of records are inserted at the same time

the average completion time is 2.9 seconds when no index is created

the average completion time is 6.7 seconds in January 2017 after the index of field a

the average completion time is 10.3 seconds after the index of field a and field B

The average completion time after both fields B and C are indexed is 11.7 seconds

it can be seen from the above test results that the impact of the index on data modification

the index is classified according to the storage method

b* tree index

b* tree index is the most commonly used index. Its storage structure is similar to the index structure of a book. There are two types of storage data blocks: branch and leaf. The branch block is equivalent to the large directory of a book, The leaf block is equivalent to the specific page indexed. B* tree index is used for general index and unique constraint index

bitmap index

bitmap index storage is mainly used to save space and reduce Oracle's access to data blocks. It uses bitmap offset to correspond to the row ID number of the table. Bitmap index is generally a table field with too many duplicate values. Bitmap indexes are rarely used in the actual intensive OLTP (data transaction processing), because OLTP will perform a large number of delete, modify, and create operations on the table, and Oracle will lock the data block to be operated each time. Therefore, multi person operations are prone to block lock waiting or even deadlock. The application of bitmap in OLAP (data analysis and processing) has advantages, because most of OLAP is the query operation of database, and generally adopts data warehouse technology, so a large number of data use bitmap index to save space is obvious

index classification by function

unique index

unique index has two functions, one is data constraint, and the other is data index. Data constraint is mainly used to ensure data integrity. Each record in the index record generated by the 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 primary keyword is created in the database

general index

general index does not produce data constraint. Its main function is to create an index table for fields to improve data query speed

index classification by index object

single column index (index of multiple fields in the form)

multi column index (index of multiple fields in the table)

function index (index of functional operations on fields)

method of establishing 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 indexing the function, if the current session wants to reference, set the query of the current session_ rewrite_ Enabled is true

alter session set query_ rewrite_ Enabled=true

note: if the user function is indexed, the deterministic parameter should be added to the user function, which means that the return value of the function is also 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;

scan classification of application index

index unique scan (scan by index unique value)

select * from ZL_ yhjbqk where hbs_ Bh= '

index range scan

select * from ZL_ yhjbqk where hbs_ bh>’’

select * from zl_ yhjbqk where qc_ Bh> '7001'

index fast full scan

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 created

the primary keyword of a table

automatically creates a unique index

such as ZL_ HBS in yhjbqk (user profile)_ BH (user ID number)

field unique constraint of the table

oracle uses indexes to ensure data integrity

such as LC_ LC in HJ (process link)_ bh+hj_ SX (process number + phase sequence)

fields queried by direct conditions

fields used for condition constraints in SQL

such as ZL_ QC in yhjbqk (user profile)_ BH (area book number)

select * from ZL_ yhjbqk where qc_ Bh='7001 '

fields associated with other tables in the query

often establish foreign key relationships

such as ZL_ Jldb in ydcf_ BH (measurement point table No.)

sele has unstable friction force CT * from ZL_ ydcf a,zl_ yhdb b where db_ bh=db_ bh and db_ Bh= '

fields sorted in query

if the sorted fields are 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=’7001’ order by cb_ SX (create qc_bh+cb_sx index, note: it is only an index, including qc_bh and cb_sx fields)

fields for statistics or group statistics in query

select max (hbs_bh) from ZL_ yhjbqk

select qc_ bh,count(*) from zl_ yhjbqk group by qc_ Bh

under what circumstances should we not create or create fewer indexes

there are too few table records

if a table has only 5 records and uses an index to access records, it is necessary to access the index table first, 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 the data block back and forth at least twice. Without index, Oracle will read all the data at once, and the processing speed is obviously faster than using index

as shown in table ZL_ Sybm (operating Department) generally has only a few records. Indexing any field except the primary keyword will not produce performance optimization. In fact, Oracle will not use your index after statistical analysis of this table, but will automatically perform full table access. For example:

select * from ZL_ sybm where sydw_ Bh='5401 '(indexing sydw_bh will not lead to performance optimization)

tables that are frequently inserted, deleted, and modified

for some frequently processed business tables, the index should be reduced as much as possible if the query allows, such as ZL_ yhbm,gc_ dfss,gc_ dfys,gc_ Fpdy and other business tables

table fields with repeated data and average distribution

if a table has 100000 rows of records, and a field a has only t and F values, and the distribution probability of each value is about 50%, indexing the field a of this table will not generally improve the query speed of the database

table fields that are frequently queried together with the main field but have more index values of the main field

such as GC_ The DFSS (electricity charge paid in) table often queries a collection by charging serial number, customer ID number, meter reading date, electricity charge occurrence year and year, and operation flag. If all fields are built in one index, the time for data modification, insertion, and deletion will be increased. In fact, if a collection is indexed by charging serial number, the records will be reduced to only a few, If the query is indexed by the following fields, the performance will not be greatly affected

how to return results only through an index

an index generally includes single or multiple fields. If you can directly apply the index to return results without accessing the table, it will greatly improve the performance of database queries. Compare the following three SQL statements. For table ZL_ HBS of yhjbqk_ BH and QC_ BH field is indexed:

1 select HBS_ bh,qc_ bh,xh_ bz from zl_ yhjbqk where qc_ Bh='7001 '

execution path:

select state, goal = choose 112655565

table access by index ROWID dlyx ZL_ Yhjbqk 112655565

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 state, goal = choose 112653710

table access by index ROWID dlyx ZL_ Yhjbqk 112653710

index range scan dlyx area index 1 265

average execution time (0.078 seconds)

3 select QC_ bh from zl_ yhjbqk where qc_ BH it is possible that the execution path ='7001 '

is often used:

select state, goal = choose 12651060

index range scan dlyx region index 12651060

average execution time (0.062 seconds)

from the execution results, we can see that the third SQL has the highest efficiency. From the execution path, it can be seen that the first and second SQL statements all implement the technologies of table access to the experimental machine. Jinan new era assaying Instrument Co., Ltd. gives full play to its advantages by index ROWID, because the returned result columns include the non indexed columns (hbs_bh, xh_bz) in the currently used index (qc_bh), while the third SQL statement directly passes through QC_ BH returns the result, which is the method to return the result directly through the index

how to rebuild an index

alter index table primary key of power result table rebuild

how to quickly create an index of a large data table

if the records of a table reach more than 1million, it may take a long time to build an index on one of the fields, and even cause the server database to crash, because Oracle needs to take out all the contents of the index fields and sort them comprehensively when building an index, A large amount of data may cause the server to sort out of memory and reference the disk swap space, which will seriously affect the work of the server database. The solution is to increase the sorting memory parameter in the database startup initialization. If a large number of index modifications are required, you can set a sorting memory of more than 10m (the default size of Oracle is 64K). After the index is established, you should modify the parameter back, because such a large sorting memory is generally not used in the actual OLTP database application. (end)

Copyright © 2011 JIN SHI