联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
原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
----------------------------------------------------------
2 recursive calls
8 db block gets
111504 consistent gets
1 physical reads
692 redo size
8075 bytes sent via SQL*Net to client
502 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
54 rows 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)
2 SELECT 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
----------------------------------------------------------
2 recursive calls
8 db block gets
33 consistent gets
1 physical reads
648 redo size
7688 bytes sent via SQL*Net to client
502 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed
这里可以发现,该sql使用了创建的含常数的复合index,sql执行时间从4分56秒,提高到现在的1秒钟,逻辑读从当初的111504减小到现在的33,巧用含常数的复合索引使得sql执行效率极大提高.