Shulou(Shulou.com)06/01 Report--

Combined index elements!

/ * 1. It is suitable for situations where a single query returns a large number of records and suddenly returns few records after a combined query:

For example, where degree = master's degree or above returns a lot of records

For example, where occupation = cashier also returns a lot of records

Therefore, no matter which conditional query is indexed, it is not appropriate.

However, if you have a master's degree or above and work as a cashier, there will be very few returns.

So the federated index can be built in this way.

, /

/ * 2. The combination order of the combined query should consider the individual prefix query (otherwise the index of the separate prefix query cannot take effect or can only be used by the jump index)

For example, when you are building a federated index of id,object_type, you should consider whether there are more individual where id=xxx queries or separate where object_type queries.

The details are omitted here for the time being, and there is a description in the case section.

, /

-3. When querying only equivalents with no scope, the combined index order does not affect performance (for example, where col1=xxx and col2=xxx, whether COL1+COL2 combination or COL2+COL1 combination)

Drop table t purge

Create table t as select * from dba_objects

Insert into t select * from t

Insert into t select * from t

Insert into t select * from t

Update t set object_id=rownum

Commit

Create index idx_id_type on t (object_id,object_type)

Create index idx_type_id on t (object_type,object_id)

Set autotrace off

Alter session set statistics_level=all

Set linesize 366

Select / * + index (tfocus idxroomidstereotype) * / * from t where object_id=20 and object_type='TABLE'

Select * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

-

| | 0 | SELECT STATEMENT | | 1 | 1 | 000.00 SELECT STATEMENT 00.01 | 5 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 57 | 1 | 00001 | 00.01 | 5 |

| | * 2 | INDEX RANGE SCAN | IDX_ID_TYPE | 1 | 9 | 1 | 00Rank 00.01 | 4 |

-

Select / * + index (tfocus idxroomtyperoomid) * / * from t where object_id=20 and object_type='TABLE'

Select * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

Plan hash value: 3420768628

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

-

| | 0 | SELECT STATEMENT | | 1 | 1 | 000.00 SELECT STATEMENT 00.01 | 5 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 57 | 1 | 00001 | 00.01 | 5 |

| | * 2 | INDEX RANGE SCAN | IDX_TYPE_ID | 1 | 9 | 1 | 00Rank 00.01 | 4 |

-

-- 4. The best order for composite indexes is generally to put the column of the column equivalent query in front of the column. (test the combination index in the case of different conditions, the conditions are often different, should be put at the back, so that the equivalent is in front.)

Select / * + index (tfocus idxroomidstereotype) * / * from t where object_id > = 20 and object_id=20 and object_id

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.