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

I. Environment

Centos 6.7and oracle 11.2The table is partitioned by month according to date

Second, testing

1. Index ind_1 (org_id,visit_date,visit_no,patient_id) exists in the query table.

Execute query statement

The implementation plan is as follows:

The total query time is about 2 minutes and 40 seconds.

2. Delete index ind_1 (org_id,visit_date,visit_no,patient_id)

Drop index ind_1

Execute query statement

The implementation plan is as follows:

The total query time is about 9 seconds.

Summary: when there is an index in the table, test 1 uses the index of the table, resulting in a query time of about 2 minutes and 40 seconds. when the index of the table is deleted, test 2 partitions the query according to the table, and the time is only about 9 seconds.

Question: when the above test statement is changed to not exists, no index is used when executing the statement in the database, and the query time is only about a few seconds. When the query statement is put into the java+mybatis framework, the query uses the index of the table, and the query time is about 3 minutes. Ask for an explanation from the warrior you've met. Thank you

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.