分区键值为空的bug

昨天才写了一个解决方案，今天就发现bug。

创建单独的分区存储NULL记录：http://yangtingkun.itpub.net/post/468/487646

 

 

首先先来看昨天给出的例子：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> create table t_part_list
  2  (id number, created date)
  3  partition by list (created)
  4  (partition p1 values (null),
  5  partition p2 values (default));

Table created.

SQL> insert into t_part_list values (1, sysdate);

1 row created.

SQL> insert into t_part_list values (2, null);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_part_list partition (p1);

        ID CREATED
---------- -------------------
         2

SQL> select * from t_part_list partition (p2);

        ID CREATED
---------- -------------------
         1 2009-07-17 15:54:26

这样查询没有问题，但是指定分区列的限制条件查询会导致返回结果不正确：

SQL> select * from t_part_list;

        ID CREATED
---------- -------------------
         2
         1 2009-07-17 15:54:26

SQL> select * from t_part_list where created is null;

        ID CREATED
---------- -------------------
         2

SQL> select * from t_part_list where created is not null;

        ID CREATED
---------- -------------------
         1 2009-07-17 15:54:26

SQL> select * from t_part_list where created > trunc(sysdate);

no rows selected

问题出现了，看看出错sql的执行计划：

SQL> set autot on exp
SQL> select * from t_part_list where created > trunc(sysdate);

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1235158136

-----------------------------------------------------------------------------------------
|Id|Operation            |Name       |Rows |Bytes|Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------
| 0|SELECT STATEMENT     |           |    1|   22|    2   (0)| 00:00:01 |       |       |
| 1| PARTITION LIST EMPTY|           |    1|   22|    2   (0)| 00:00:01 |INVALID|INVALID|
|*2|  TABLE ACCESS FULL  |T_PART_LIST|    1|   22|    2   (0)| 00:00:01 |INVALID|INVALID|
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CREATED">TRUNC(SYSDATE@!))

Note
-----
   - dynamic sampling used for this statement

很明显Pstart和Pstop的结果都是不正确的。问题是由于分区列表指定的NULL值引起的。

SQL> select * from t_part_list where created is null;

        ID CREATED
---------- -------------------
         2

Execution Plan
----------------------------------------------------------
Plan hash value: 1694785230

----------------------------------------------------------------------------------------
|Id|Operation             |Name       |Rows |Bytes|Cost (%CPU)|Time    | Pstart| Pstop |
----------------------------------------------------------------------------------------
| 0|SELECT STATEMENT      |           |    1|   22|    2   (0)|00:00:01|       |       |
| 1| PARTITION LIST SINGLE|           |    1|   22|    2   (0)|00:00:01|   KEY |   KEY |
| 2|  TABLE ACCESS FULL   |T_PART_LIST|    1|   22|    2   (0)|00:00:01|     1 |     1 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

SQL> select * from t_part_list where created is not null;

        ID CREATED
---------- -------------------
         1 2009-07-17 15:54:26

Execution Plan
----------------------------------------------------------
Plan hash value: 1826308962

----------------------------------------------------------------------------------------
|Id|Operation          |Name       |Rows |Bytes| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------
| 0|SELECT STATEMENT   |           |    1|   22|     2   (0)| 00:00:01 |       |       |
| 1| PARTITION LIST ALL|           |    1|   22|     2   (0)| 00:00:01 |     1 |     2 |
|*2|  TABLE ACCESS FULL|T_PART_LIST|    1|   22|     2   (0)| 00:00:01 |     1 |     2 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CREATED" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement

SQL> select * from t_part_list where created = to_date('2009-07-17 15:54:26');

        ID CREATED
---------- -------------------
         1 2009-07-17 15:54:26

Execution Plan
----------------------------------------------------------
Plan hash value: 1694785230

-------------------------------------------------------------------------------------------
|Id|Operation             |Name       |Rows |Bytes| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT      |           |    1|   22|     2   (0)| 00:00:01 |       |       |
| 1| PARTITION LIST SINGLE|           |    1|   22|     2   (0)| 00:00:01 |   KEY |   KEY |
|*2|  TABLE ACCESS FULL   |T_PART_LIST|    1|   22|     2   (0)| 00:00:01 |     2 |     2 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CREATED"=TO_DATE('2009-07-17 15:54:26', 'yyyy-mm-dd hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement

当Oracle访问全表，或者单独一个分区的时候，都不会出错。

Oracle的metalink在文档Doc ID:  5245038.8中描述了这个问题。在10.2.0.4解决了这个bug。

Oracle认为这个bug在11.1.0.6上解决，但是测试发现11.1.0.6上同样的问题仍然存在。

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-609526/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-609526/