含is null sql语句优化
原sql语句与执行计划SQL> set autot trace
SQL> WITH AL AS (SELECT * FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL)
2 SELECT SWP.ID SWP_ID, AL.* FROM AL FULL OUTER JOIN XIFENFEI_LOG_SWAP SWP ON SWP.ID = AL.ID;
54 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 888046630
----------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 11064 | 24658 (2)| 00:04:56 |
| 1 |TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
|*3 | TABLE ACCESS FULL | XIFENFEI_LOG | 23 |2576 | 24652 (2)| 00:04:56 |
| 4 | VIEW | | 24 | 11064 | 6(17)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | NESTED LOOPS OUTER | | 23 | 10465 | 2 (0)| 00:00:01 |
| 7 | VIEW | | 23 | 10304 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_51B4E691 | 23 |2576 | 2 (0)| 00:00:01 |
|*9 | INDEX UNIQUE SCAN | XIFENFEI_LOG_SWP_PK | 1 | 7 | 0 (0)| 00:00:01 |
|* 10 | HASH JOIN ANTI | | 1 | 20 | 4(25)| 00:00:01 |
|11 | INDEX FULL SCAN | XIFENFEI_LOG_SWP_PK | 20 | 140 | 1 (0)| 00:00:01 |
|12 | VIEW | | 23 | 299 | 2 (0)| 00:00:01 |
|13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_51B4E691 | 23 |2576 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("CLEAR_TIME" IS NULL)
9 - access("SWP"."ID"(+)="AL"."ID")
10 - access("SWP"."ID"="AL"."ID")
Statistics
----------------------------------------------------------
2recursive calls
8db block gets
111504consistent gets
1physical reads
692redo size
8075bytes sent via SQL*Net to client
502bytes received via SQL*Net from client
5SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
54rows processed这里很明显占用资源多，执行时间长的都在XIFENFEI_LOG表的全表扫描上,而该表的where 条件是CLEAR_TIME is null.
分析CLEAR_TIME 列null值的分布
SQL> SELECT count(*) FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL;
COUNT(*)
----------
48
SQL> SELECT count(*) FROM XIFENFEI_LOG WHERE CLEAR_TIME IS not NULL;
COUNT(*)
----------
6899211通过这里分析可以知道,CLEAR_TIME is null的值非常少,如果能够创建一个index,取到CLEAR_TIME 列null的值,那效率将非常搞.但是有oracle index只是的人都知道,B树index是不包含null列,因此一般性index无法满足该需求.这里思考创建含常数的复合index,而且把CLEAR_TIME放在前面,因为后面的常数一定存在,因此CLEAR_TIME中含有null的记录也就包含在该复合index中.
创建含常数复合index
SQL> create index ind_XIFENFEI_LOG_null on XIFENFEI_LOG (CLEAR_TIME,0) online;
Index created.再次查看执行计划
SQL> WITH AL AS (SELECT * FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL)
2SELECT SWP.ID SWP_ID, AL.* FROM AL FULL OUTER JOIN XIFENFEI_LOG_SWAP SWP ON SWP.ID = AL.ID;
50 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2359331571
-------------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 11064 | 25 (4)| 00:00:01 |
| 1 |TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| XIFENFEI_LOG | 23 |2576 | 19 (0)| 00:00:01 |
|*4 | INDEX RANGE SCAN | IND_XIFENFEI_LOG_NULL | 23 | | 3 (0)| 00:00:01 |
| 5 | VIEW | | 24 | 11064 | 6(17)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | NESTED LOOPS OUTER | | 23 | 10465 | 2 (0)| 00:00:01 |
| 8 | VIEW | | 23 | 10304 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_51B4E691 | 23 |2576 | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | XIFENFEI_LOG_SWP_PK | 1 | 7 | 0 (0)| 00:00:01 |
|* 11 | HASH JOIN ANTI | | 1 | 20 | 4(25)| 00:00:01 |
|12 | INDEX FULL SCAN | XIFENFEI_LOG_SWP_PK | 20 | 140 | 1 (0)| 00:00:01 |
|13 | VIEW | | 23 | 299 | 2 (0)| 00:00:01 |
|14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_51B4E691 | 23 |2576 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CLEAR_TIME" IS NULL)
10 - access("SWP"."ID"(+)="AL"."ID")
11 - access("SWP"."ID"="AL"."ID")
Statistics
----------------------------------------------------------
2recursive calls
8db block gets
33consistent gets
1physical reads
648redo size
7688bytes sent via SQL*Net to client
502bytes received via SQL*Net from client
5SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
50rows processed这里可以发现,该sql使用了创建的含常数的复合index,sql执行时间从4分56秒,提高到现在的1秒钟,逻辑读从当初的111504减小到现在的33,巧用含常数的复合索引使得sql执行效率极大提高.
参考：含is null sql语句优化 创建包含null值index
{:3_42:}飞总blog上的文章吧 请问，你那个SQL语句的这种框框是怎么搞得。
页:
[1]