Update associated query does not go index, inefficient

Source: Internet
Author: User

This two-day optimization of a SQL, that is, there are a A, a, two tables, to use the field B table to update a table corresponding fields. Shaped like

SQL code

    1. Update A Set a.a= (select b.b from B where a.id=b.id);



Original SQL

Updatepntmall_rptpoint_detail a set a.scrm_rptpnt_processed= (

Select distinctb.scrm_rptpnt_processed frompntmall_rptpoint_detail_tmp b where a.pntmall_rptpnt_id=b.pntmall_rptpnt _id

);


There are only 1 data in table B, there are 370,000 data in table A, the application logic is the same as updating the ID and B table in a table.

This SQL execution took 6 hours to complete, the execution plan is displayed as a full table scan, that is, although the execution of 1 SQL, but perform the update process to scan 370,000 data, until owe the same ID to update


It is also not desirable to attempt a hit to force an index, nor is it ideal if the ID column type in the Where condition is the same as int, and there is no case where the field type causes the index not to go.


Take the way:

Optimized SQL

Updatepntmall_rptpoint_detail a set a.scrm_rptpnt_processed= (

Select distinctb.scrm_rptpnt_processed frompntmall_rptpoint_detail_tmp b where a.pntmall_rptpnt_id=b.pntmall_rptpnt _id

) wherea.pntmall_rptpnt_id in (select b.pntmall_rptpnt_id frompntmall_rptpoint_detail_tmp b)


The optimized SQL reaches the second level to meet customer needs.


Summarize the situation that does not follow the index:

1, the condition field selectivity is weak, the result set of the detection is large, do not go index;

2. Where condition equals sign field type is different, do not go index;

3, optimizer analysis of statistical information may also lead to the index;

4, the index field is null does not go index;

5, for the count (*) When the index field has notnull constraints to walk the index, otherwise do not go index;

6, like after the character when the first is a wildcard character does not go index;

7, use not equal to operators such as:<>,! = etc do not go index;

8, the index field before adding a function or participate in the operation does not go index;


Update associated query does not go index, inefficient

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.