SQL optimization (the impact of changes in query conditions on execution plans)

Today, I was counting a piece of data at work. At first, the SQL statement was successfully executed, but then the user adjusted the requirements slightly. I thought it would be possible to add a query condition, the result is generated after more than 20 minutes. Later, I checked the execution plan and found that the execution plans of the two SQL statements changed. The two SQL statements are: www.2cto.com [SQL] SELECT T. YEARID, SUM (T. EXPORTSUM) from stdw. f_CUSTOM_EXPORTDETAIL T, (select. ZONE,. ZONE_NAME from stdw. d_CUSTOM_PROVINCE_ZONE A, STDW. d_CUSTOM_BRANCH_PROVINCE B WHERE. PROVINCE_NO = B. proviceid and B. CORPID = 4400) T2 where t. CITYNO = T2.ZONE AND (T. CUSTOMCODE8 LIKE '000000' or t. CUSTOMCODE8 LIKE '000000' OR (T. CUSTOMCODE8 LIKE '000000' and t. CUSTOMCODE8 not like '100% 'And t. CUSTOMCODE8 not like '20140901') and t. yearid between 2010 AND 2012 group by t. YEARID [SQL] SELECT T. YEARID, SUM (T. EXPORTSUM) from stdw. f_CUSTOM_EXPORTDETAIL T, (select. ZONE,. ZONE_NAME from stdw. d_CUSTOM_PROVINCE_ZONE A, STDW. d_CUSTOM_BRANCH_PROVINCE B WHERE. PROVINCE_NO = B. proviceid and B. CORPID = 4400 and B. PROVICEID = 44) T2 where t. CITYNO = T2.ZONE AND (T. CUSTOMCODE8 LIKE '20140901' Or t. CUSTOMCODE8 LIKE '000000' OR (T. CUSTOMCODE8 LIKE '000000' and t. CUSTOMCODE8 not like '20140901' and t. CUSTOMCODE8 not like '20140901') and t. yearid between 2010 AND 2012 group by t. the execution plan of the first SQL statement of YEARID is: The Execution Plan of the second SQL statement is: the background needs to be described here. Table F_CUSTOM_EXPORTDETAIL is a table partitioned by year, each partition contains more than 10 million data records, which is about 40 million of data records. For the D_CUSTOM_BRANCH_PROVINCE table, there are two pieces of data with the CORPID field 4400 and only one row with the PROVICEID field 44. When only CORPID = 4400 is restricted for the first time, the SQL Execution time is about 2 minutes (after all, the table F_CUSTOM_EXPORTDETAIL is large), and the Execution Plan is reasonable. However, after the restriction "B. PROVICEID = 44" is added for the second time, the SQL Execution time is obviously longer, which takes more than 20 minutes. By analyzing the execution plans of these two SQL statements, we can find that the execution plan of the second SQL statement has changed: The table connection method has changed to NESTED LOOPS. This method is time-consuming when the table data volume is large.
The same SQL statement was encountered before, and the Execution Plan was changed after a period of time because the statistics were not collected in time. The solution was to use the hint of oralce. However, this time is not the same as the previous one. After all, the SQL statements are not the same, and the execution plans of these two SQL statements are fixed. To find out the root cause, instead of using hint, we analyzed the second SQL statement. As mentioned above, the condition "B. after PROVICEID = 44 ", D_CUSTOM_BRANCH_PROVINCE has only one record. This is why ORACLE uses the nested loops table connection method. It actually makes sense, but because the last joined table F_CUSTOM_EXPORTDETAIL has a large amount of data, the execution efficiency is low. In this case, the better way is to use the hash join table connection method. How can we achieve this? According to the previous analysis, we should first consider the possibility of breaking only one record. Analyze the subquery In the second SQL: www.2cto.com [SQL] SELECT. ZONE,. ZONE_NAME from stdw. d_CUSTOM_PROVINCE_ZONE A, STDW. d_CUSTOM_BRANCH_PROVINCE B WHERE. PROVINCE_NO = B. proviceid and B. CORPID = 4400 and B. PROVICEID = 44 because D_CUSTOM_BRANCH_PROVINCE has only one record after the restriction, and the PROVICEID is used when D_CUSTOM_PROVINCE_ZONE is associated with the table, we can directly restrict the conditions of the table shard without D_CUSTOM_BRANCH_PROVINCE, as follows: [SQL] SELECT. ZONE,. ZONE_NAME from stdw. d_CU STOM_PROVINCE_ZONE a where a. PROVINCE_NO = 44. This is totally different from the D_CUSTOM_BRANCH_PROVINCE table, but the actual effect is the same. Finally, modify the SQL statement to [SQL] SELECT T. YEARID year, SUM (T. EXPORTSUM) export amount from stdw. f_CUSTOM_EXPORTDETAIL T, (select. ZONE,. ZONE_NAME from stdw. d_CUSTOM_PROVINCE_ZONE a where. PROVINCE_NO = 44) T2 where t. CITYNO = T2.ZONE AND (T. CUSTOMCODE8 LIKE '000000' or t. CUSTOMCODE8 LIKE '000000' OR (T. CUSTOMCODE8 LIKE '000000' and t. CUSTOMCODE8 not like '20140901' and t. CUSTOMCODE8 not like '20140901') and t. yearid between 2010 AND 2012 group by t. the YEARID execution plan is:
It takes about two minutes to execute the SQL statement. Conclusion: 1. Theoretically, the higher the selectivity, the higher the SQL Execution efficiency, but also the situation: if there is only one query result, the association between tables may be affected, the execution efficiency is low. 2. pay more attention to the connection mode between tables when viewing the execution plan, and think about why ORACLE uses this method, which helps solve the problem.