Performance testing for Oracle single-column indexed multicolumn indexes

Source: Internet
Author: User
Tags time 0

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.