oracle中is null优化,is null 优化 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788...

原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执行效率极大提高.