SQL Tuning Basics Overview 05-oracle Index types and introduction

First, B-tree index

Three main features: low height, stored column values, orderly structure

1.1 optimize with index features

Index on foreign keys: not only can improve query efficiency, but also can effectively avoid the competition of the lock (the foreign key table Delete Record is not committed, the primary key table will be locked).

Statistics class Query Sql:count (), AVG (), SUM (), Max (), Min ()

Sort operation: Order by Field indexing

Redo operation: Distinct

Union/union all:union all do not need to go heavy, do not need to sort

1.2 Federated Indexes
Scenario One: SQL query columns are rare, and a federated index that creates query columns can effectively eliminate the table, but a federated index that generally exceeds 3 fields is inappropriate.
Scenario Two: In field a returns record many, in the field B returns the record many, in the field A, a query return record is few, for example executes the following query, the result c1,c2 are many, C3 but very few.

    Select Count(1) C1 fromTwhereA= 1; Select Count(1) C2 fromTwhereB= 2; Select Count(1) C3 fromTwhereA= 1  andB= 2;

Union index column who is in front?
Popular view: Repeat record less fields are placed in front, repeat records are placed in the back, in fact, this conclusion is not accurate.

    Drop Table t purge;     Create Table  as Select *  from dba_objects;     Create Index  on t (object_id, object_type);     Create Index  on t (object_type,object_id);

--Equivalent query:
    Select *  fromTwhere object_id =  -  andObject_type= 'TABLE'; Select /*+ index (t,idx1_object_id)*/ *  fromTwhere object_id =  -  andObject_type= 'TABLE'; Select /*+ index (t,idx2_object_id)*/ *  fromTwhere object_id =  -  andObject_type= 'TABLE';

Observation Statement Execution Plan conclusion: in the case of an equal query, the columns of the composite index are the same for each column, regardless of the previous columns.

--Scope query:
Select *  fromTwhere object_id >= -  and object_id <  -  andObject_type= 'TABLE'; Select /*+ index (t,idx1_object_id)*/ *  fromTwhere object_id >= -  and object_id <  -  andObject_type= 'TABLE'; Select /*+ index (t,idx2_object_id)*/ *  fromTwhere object_id >= -  and object_id <  -  andObject_type= 'TABLE';

Conclusion: The columns of the combined index, the equivalent queries are listed before, and the range queries are listed later. However, if you want to determine who is in the composite index column in the actual production environment, consider all common SQL usage indexes in general, because too many indexes can affect inbound performance. For example, if other SQL can frequently use a single-column index to OBJECT_ID, and when a single column query column is the same as the predecessor of a federated index, a single column can not be indexed, and a single-column query can also use a composite index. Consider putting the object_id in front of the general.

1.3 Endangerment of the index
Too many indexes on the table can seriously affect the insertion performance;

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

The update operation is similar to delete, and has no effect if the updated non-indexed column.

1.4 Monitoring of the index

    -- Monitoring    Alter Index [index_name] monitoring usage;     Select *  from v$object_usage;     -- To cancel the  monitoring:    Alter Index [index_name] nomonitoring usage;

Depending on the results of the index monitoring, it is possible to consider deleting an index that has not been used for a long time.

1.5 Common execution plans for indexes

Index full Scan: Fully scanned indexes, block read, ordered
Index range Scan: Range sweep of index
Index fast full Scan: Fast all scans of indexes, multiple reads, unordered
INDEX full SCAN (Min/max): Query for MAX (), MIN () function
Index SKIP SCAN: The first column of the combined index is not used by the query condition, and the first column of the composite index is high in repetition, which may be used

Second, bitmap index

Scenario: The table has very few update operations and a column with a high degree of repetition.

Advantage: Count (*) High efficiency

Ad hoc queries are highly efficient (this feature actually verifies that there is a version difference: in 10.2.0.1 version The Ad hoc query efficiency of the bitmap index is much higher than that of the composite Index query efficiency, while in 11.2.0.3 version Verify that the ad hoc query efficiency of the common composite index is more efficient than the efficiency of the bitmap index)

  Create TableT (name_id, gender not NULL, location not NULL, Age_range not NULL, data) as Selectrownum, Decode ( Floor(Dbms_random.value (0,2)),0,'M',1,'F') gender, Ceil (Dbms_random.value (0, -) Location , decode ( Floor(Dbms_random.value (0,4)),0,' Child',1,' Young',2,'Middle',3,' Old') Age_range, Rpad ('*', -,'*') Data fromDual Connect byRowNum<= 100000;

  Create Index  on T (gender,location,age_range);   Create Index  on T (gender);   Create Index  on t (location);   Create Index  on T (Age_range);

  Select *  fromTwhereGender= 'M'  andLocationinch(1,Ten, -) andAge_range= ' Child'; Select /*+ index (t,idx_t)*/*  fromTwhereGender= 'M'  andLocationinch(1,Ten, -) andAge_range= ' Child';

Third, function index

Scenario: A scene that has to perform a function operation on a column.

Using a function index is less efficient than using a normal index.

Reference book: "Harvest, more than Oracle"

SQL Tuning Basics Overview 05-oracle Index types and introduction