含is null sql语句优化

8chf

原sql语句与执行计划

1. SQL> set autot trace
   
2. SQL> WITH AL AS (SELECT * FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL)
   
3.    2 SELECT SWP.ID SWP_ID, AL.* FROM AL FULL OUTER JOIN XIFENFEI_LOG_SWAP SWP ON SWP.ID = AL.ID;
   
4. 
   
5. 54 rows selected.
   
6. 
   
7. 
   
8. Execution Plan
   
9. ----------------------------------------------------------
   
10. Plan hash value: 888046630
    
11. 
    
12. ----------------------------------------------------------------------------------------------------------
    
13. | Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
    
14. ----------------------------------------------------------------------------------------------------------
    
15. |   0 | SELECT STATEMENT           |                             |    24 | 11064 | 24658   (2)| 00:04:56 |
    
16. |   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |     |
    
17. |   2 |   LOAD AS SELECT           |                             |       |       |            |     |
    
18. |*  3 |    TABLE ACCESS FULL       | XIFENFEI_LOG                |    23 |  2576 | 24652   (2)| 00:04:56 |
    
19. |   4 |   VIEW                     |                             |    24 | 11064 |     6  (17)| 00:00:01 |
    
20. |   5 |    UNION-ALL               |                             |       |       |            |     |
    
21. |   6 |     NESTED LOOPS OUTER     |                             |    23 | 10465 |     2   (0)| 00:00:01 |
    
22. |   7 |      VIEW                  |                             |    23 | 10304 |     2   (0)| 00:00:01 |
    
23. |   8 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6605_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
    
24. |*  9 |      INDEX UNIQUE SCAN     | XIFENFEI_LOG_SWP_PK         |     1 |     7 |     0   (0)| 00:00:01 |
    
25. |* 10 |     HASH JOIN ANTI         |                             |     1 |    20 |     4  (25)| 00:00:01 |
    
26. |  11 |      INDEX FULL SCAN       | XIFENFEI_LOG_SWP_PK         |    20 |   140 |     1   (0)| 00:00:01 |
    
27. |  12 |      VIEW                  |                             |    23 |   299 |     2   (0)| 00:00:01 |
    
28. |  13 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6605_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
    
29. ----------------------------------------------------------------------------------------------------------
    
30. 
    
31. 
    
32. Predicate Information (identified by operation id):
    
33. ---------------------------------------------------
    
34. 
    
35.    3 - filter("CLEAR_TIME" IS NULL)
    
36.    9 - access("SWP"."ID"(+)="AL"."ID")
    
37.   10 - access("SWP"."ID"="AL"."ID")
    
38. 
    
39. 
    
40. Statistics
    
41. ----------------------------------------------------------
    
42.           2  recursive calls
    
43.           8  db block gets
    
44.      111504  consistent gets
    
45.           1  physical reads
    
46.         692  redo size
    
47.        8075  bytes sent via SQL*Net to client
    
48.         502  bytes received via SQL*Net from client
    
49.           5  SQL*Net roundtrips to/from client
    
50.           0  sorts (memory)
    
51.           0  sorts (disk)
    
52.          54  rows processed

复制代码

这里很明显占用资源多，执行时间长的都在XIFENFEI_LOG表的全表扫描上,而该表的where 条件是CLEAR_TIME is null.


分析CLEAR_TIME 列null值的分布

1. SQL> SELECT count(*) FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL;
   
2. 
   
3.   COUNT(*)
   
4. ----------
   
5.         48
   
6. 
   
7. SQL> SELECT count(*) FROM XIFENFEI_LOG WHERE CLEAR_TIME IS not NULL;
   
8. 
   
9.   COUNT(*)
   
10. ----------
    
11.    6899211

复制代码

通过这里分析可以知道,CLEAR_TIME is null的值非常少,如果能够创建一个index,取到CLEAR_TIME 列null的值,那效率将非常搞.但是有oracle index只是的人都知道,B树index是不包含null列,因此一般性index无法满足该需求.这里思考创建含常数的复合index,而且把CLEAR_TIME放在前面,因为后面的常数一定存在,因此CLEAR_TIME中含有null的记录也就包含在该复合index中.

创建含常数复合index

1. SQL> create index ind_XIFENFEI_LOG_null on XIFENFEI_LOG (CLEAR_TIME,0) online;
   
2. 
   
3. Index created.

复制代码

再次查看执行计划

1. SQL> WITH AL AS (SELECT * FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL)
   
2.   2  SELECT SWP.ID SWP_ID, AL.* FROM AL FULL OUTER JOIN XIFENFEI_LOG_SWAP SWP ON SWP.ID = AL.ID;
   
3. 
   
4. 50 rows selected.
   
5. 
   
6. 
   
7. Execution Plan
   
8. ----------------------------------------------------------
   
9. Plan hash value: 2359331571
   
10. 
    
11. -------------------------------------------------------------------------------------------------------------
    
12. | Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
    
13. -------------------------------------------------------------------------------------------------------------
    
14. |   0 | SELECT STATEMENT              |                             |    24 | 11064 |    25   (4)| 00:00:01 |
    
15. |   1 |  TEMP TABLE TRANSFORMATION    |                             |       |       |            |          |
    
16. |   2 |   LOAD AS SELECT              |                             |       |       |            |          |
    
17. |   3 |    TABLE ACCESS BY INDEX ROWID| XIFENFEI_LOG                |    23 |  2576 |    19   (0)| 00:00:01 |
    
18. |*  4 |     INDEX RANGE SCAN          | IND_XIFENFEI_LOG_NULL       |    23 |       |     3   (0)| 00:00:01 |
    
19. |   5 |   VIEW                        |                             |    24 | 11064 |     6  (17)| 00:00:01 |
    
20. |   6 |    UNION-ALL                  |                             |       |       |            |          |
    
21. |   7 |     NESTED LOOPS OUTER        |                             |    23 | 10465 |     2   (0)| 00:00:01 |
    
22. |   8 |      VIEW                     |                             |    23 | 10304 |     2   (0)| 00:00:01 |
    
23. |   9 |       TABLE ACCESS FULL       | SYS_TEMP_0FD9D660D_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
    
24. |* 10 |      INDEX UNIQUE SCAN        | XIFENFEI_LOG_SWP_PK         |     1 |     7 |     0   (0)| 00:00:01 |
    
25. |* 11 |     HASH JOIN ANTI            |                             |     1 |    20 |     4  (25)| 00:00:01 |
    
26. |  12 |      INDEX FULL SCAN          | XIFENFEI_LOG_SWP_PK         |    20 |   140 |     1   (0)| 00:00:01 |
    
27. |  13 |      VIEW                     |                             |    23 |   299 |     2   (0)| 00:00:01 |
    
28. |  14 |       TABLE ACCESS FULL       | SYS_TEMP_0FD9D660D_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
    
29. -------------------------------------------------------------------------------------------------------------
    
30. 
    
31. Predicate Information (identified by operation id):
    
32. ---------------------------------------------------
    
33. 
    
34.    4 - access("CLEAR_TIME" IS NULL)
    
35.   10 - access("SWP"."ID"(+)="AL"."ID")
    
36.   11 - access("SWP"."ID"="AL"."ID")
    
37. 
    
38. 
    
39. Statistics
    
40. ----------------------------------------------------------
    
41.           2  recursive calls
    
42.           8  db block gets
    
43.          33  consistent gets
    
44.           1  physical reads
    
45.         648  redo size
    
46.        7688  bytes sent via SQL*Net to client
    
47.         502  bytes received via SQL*Net from client
    
48.           5  SQL*Net roundtrips to/from client
    
49.           0  sorts (memory)
    
50.           0  sorts (disk)
    
51.          50  rows processed

复制代码

这里可以发现,该sql使用了创建的含常数的复合index,sql执行时间从4分56秒,提高到现在的1秒钟,逻辑读从当初的111504减小到现在的33,巧用含常数的复合索引使得sql执行效率极大提高.


参考：含is null sql语句优化   创建包含null值index

yrg5101

飞总blog上的文章吧

yhd_my

请问，你那个SQL语句的这种框框是怎么搞得。