Performance testing for Oracle single-column indexed multicolumn indexes

Clear Oralce cache: Alter system flush Buffer_cache;

Environment: Oracle 10g, 4 million data, frequency 5 minutes

1. Application scenario: Find the latest data for all sites. The SQL statements are as follows:

——————————————————————————————————————————————————

With AA as (
Select T1. EQP_ID, Max (measure_time) Maxtm from Plu_water_data t1 where T1. Measure_time >= to_date (' 2014-01-28 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
and T1. Measure_time < To_date (' 2014-01-28 02:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') group by T1. eqp_id

)
, BB as
(
Select T1. eqp_id as STCD,
T1. Measure_time as TM,
T1. Flux_value as FLOW
From Plu_water_data T1
where T1. Measure_time >= to_date (' 2014-01-28 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
and T1. Measure_time < To_date (' 2014-01-28 02:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

)
SELECT * from the AA inner join BB on AA. eqp_id = Bb.stcd and Aa.maxtm = bb.tm INNER join vi_plustation cc on CC.STCD =aa. eqp_id

——————————————————————————————————————————

Execution Result:

A. Creating TM+STCD multicolumn Index, TM Single-column index, STCD single-row index (Execution time 2 seconds)
B. Creating a TM+STCD multicolumn index, STCD Single-column index (execution time 20 seconds)
C. Creating a STCD Single-column index (execution time n seconds)
D. Creating a TM+STCD Multi-column index (execution time 22 seconds)
E. Creating a TM+STCD+TN+TP+COD Multi-column index (execution time 71 seconds)
F. Creating a TM Single-column index (execution time 2 seconds)

2. Scenario: Find data for a period of time in a single site. The SQL statements are as follows:

Select T1. eqp_id as STCD,
T1. Measure_time as TM,
T1. Flux_value as FLOW
From Plu_water_data T1
where T1. Measure_time >= to_date (' 2014-01-28 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
and T1. Measure_time < To_date (' 2014-01-28 23:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and eqp_id = ' 1244 '

Execution Result:

Query criteria on TM and STCD
A. Creating a TM Single-column index (execution time 17 seconds)
B. Creating a TM single-column index, STCD single-column index (execution time 3 seconds)
C. Creating a TM Single-column index, STCD single-column index, TM+STCD multicolumn index (execution time 0.5 seconds)
D. Creating a TM+STCD Multi-column index (execution time 0.7 seconds)

3. Conclusion: The STCD or TM in the query condition restricts the WHERE condition separately, if the STCD+TM Multi-column index is used, the efficiency is reduced. If STCD and TM are used as federated where conditions, a single-column index of STCD and TM is established, which is significantly less efficient than a multicolumn index using STCD+TM.

A. When a SQL statement has a site grouping followed by a time query nesting, it is recommended to separate the STCD and TM into a single-column index. columns such as:

Select T1. EQP_ID, Max (measure_time) Maxtm from Plu_water_data t1 where T1. Measure_time >= to_date (' 2014-01-28 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and T1. Measure_time < To_date (' 2014-01-28 02:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') group by T1. EQP_ID)

A statement like this if the STCD+TM index is established, then the read speed is 7m, and if you use STCD and TM separate single-column index, speed within 2m.

B. When a SQL statement specifies "site" and "time", it is recommended to establish a STCD+TM multi-column index. for example

Select T1. eqp_id as STCD,T1. Measure_time as TM,T1. Flux_value as FLOW from Plu_water_data T1
where T1. Measure_time >= to_date (' 2014-01-28 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
and T1. Measure_time < To_date (' 2014-01-28 23:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and eqp_id = ' 1244 '

Statements like this if the STCD and TM are set up separately to establish a single-column index, speed at 3m, but if the STCD+TM Multi-column index is established, the speed at 0.7m

C. When a SQL statement formulates a "time" query, it is recommended to establish a TM single-column index. For example

Select T1. eqp_id as STCD,T1. Measure_time as TM,T1. Flux_value as FLOW from Plu_water_data T1
where T1. Measure_time >= to_date (' 2014-01-28 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
and T1. Measure_time < To_date (' 2014-01-28 23:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

If a statement like this is built on a composite index of STCD+TM, the speed is 5m, if it is built on a single-column index of the TM, the speed is at 1.5m

Performance testing for Oracle single-column indexed multicolumn indexes