昨天才写了一个解决方案,今天就发现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/