Update associated query does not go index, inefficient

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