Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about what index types are in the Oracle database, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

I. B-Tree index

Three major characteristics: low height, stored column values, and orderly structure

1. Optimize by using the characteristics of index

Index on foreign key: not only can improve query efficiency, but also can effectively avoid lock competition (foreign key table delete record is not submitted, primary key table will be locked).

Statistical class queries SQL:count (), avg (), sum (), max (), min ()

Sort operation: index the order by field

Deduplication operation: distinct

UNION/UNION ALL:union all does not need to be duplicated or sorted.

two。 Joint index

Application scenario 1: SQL query columns are few, so establishing a federated index of query columns can effectively eliminate returning tables, but generally federated indexes with more than three fields are not appropriate.

Application scenario 2: more records are returned in field A, more records are returned in field B, and few records are returned by querying in field An and B at the same time. For example, if you execute the query below, there are many C1 and c2 results, but few in c3.

Select count (1) C1 from t where A = 1; select count (1) c2 from t where B = 2; select count (1) c3 from t where A = 1 and B = 2

Who comes first in the column of the federated index?

The popular view is that the fields with few repeated records are put in front, and the fields with more repeated records are put behind. In fact, this conclusion is not accurate.

Drop table t purge; create table t as select * from dba_objects; create index idx1_object_id on t (object_id,object_type); create index idx2_object_id on t (object_type,object_id)

Equivalence query:

Select * from t where object_id = 20 and object_type = 'TABLE'; select / * + index * / * from t where object_id = 20 and object_type =' TABLE'; select / * + index) * / * from t where object_id = 20 and object_type = 'TABLE'

Conclusion: in the case of equivalent query, the performance of the column of the composite index is the same no matter which column comes first.

Range query:

Select * from t where object_id > = 20 and object_id

< 2000 and object_type = 'TABLE'; select /*+ index(t,idx1_object_id) */ * from t where object_id >

= 20 and object_id

< 2000 and object_type = 'TABLE'; select /*+ index(t,idx2_object_id) */ * from t where object_id >

= 20 and object_id < 2000 and object_type = 'TABLE'

Conclusion: the columns of the combined index are listed first in the equivalent query and last in the range query. However, if you want to determine who is the first to combine index columns in the actual production environment, you should consider all commonly used indexes in SQL, because too many indexes will affect storage performance.

3. The harm of index

Having too many indexes on a table will seriously affect insert performance.

For delete operations, deleting a small number of data indexes can effectively and quickly locate and improve the efficiency of deletion, but it will have a negative impact if you delete a large amount of data.

The operation on update is similar to delete, and has no effect if you update a non-indexed column.

4. Monitoring of index

-- Monitoring alter index [index_name] monitoring usage; select * from vested object usage;-- canceling Monitoring: alter index [index_name] nomonitoring usage

Based on the results of monitoring the index, you can consider deleting indexes that have not been used for a long time.

5. Common execution plans for indexes

INDEX FULL SCAN: full scan, single block read, ordered index

INDEX RANGE SCAN: range scan of the index

INDEX FAST FULL SCAN: fast full scan of index, multi-block read, disorder

INDEX FULL SCAN (MIN/MAX): query for the MAX (), MIN () function

INDEX SKIP SCAN: the first column of the composite index is not used in the query condition, but may be used when the first column of the composite index is highly repetitive

2. Bitmap index

Application scenario: the update operation of the table is very few, and the columns are highly repetitive.

Advantages: high efficiency of count (*)

Create table t (name_id, gender not null, location not null, age_range not null, data) as select rownum, decode (floor (dbms_random.value (0pime 2)), 0pje Meng Jing (1pm) F') gender, ceil (dbms_random.value (0MJ 50)) location, decode (floor (dbms_random.value (0Jing 4)), 0mil childcare, 1LI (0LJ)) age_range, rpad ('*', 20MJ') data from dual connect by rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.