Wrong Result With Partitioned Table (Doc ID 1316789.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.

Symptoms

While querying and using a partitioned table, then the wrong result occurs, for example:

1) Run query:

SQL> select trunc(sysdate-NNN,'dd'), to_date('01.11.2010','dd.mm.yyyy') from dual;

You should replace NNN with proper number so that query returns equal values,
for today it is 170

2) Query:

SQL> select * from cis_bonuses_tst cs where cs.subs_subs_id=21032611 and
cs.del_date is not null and cs.cgro_cgro_id=1 and
cs.del_date>=to_date('01.11.2010','dd.mm.yyyy');

return 26 rows.

3) Query:

SQL> select * from cis_bonuses_tst cs where cs.subs_subs_id=21032611 and
cs.del_date is not null and cs.cgro_cgro_id=1 and
cs.del_date>=trunc(sysdate-NNN,'dd');

return 0 rows (NNN - number from first step).

DIAGNOSTIC ANALYSIS:
--------------------
correct_query_plan:

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 756 | 9 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 7 | 756 | 9 (0)| 00:00:01 | 22 | 22 |
| 2 | PARTITION LIST SINGLE | | 7 | 756 | 9 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| CIS_BONUSES | 7 | 756 | 9 (0)| 00:00:01 | 44 | 44 |
|* 4 | INDEX RANGE SCAN | CIS_BONUSES_UK | 1 | | 5 (0)| 00:00:01 | 44 | 44 |
----------------------------------------------------------------------------------------------------------------------


incorrect_query_plan:

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 756 | 9 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 7 | 756 | 9 (0)| 00:00:01 | 22 | 22 |
| 2 | PARTITION LIST EMPTY | | 7 | 756 | 9 (0)| 00:00:01 |INVALID|INVALID|
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| CIS_BONUSES | 7 | 756 | 9 (0)| 00:00:01 |INVALID|INVALID|
|* 4 | INDEX RANGE SCAN | CIS_BONUSES_UK | 1 | | 5 (0)| 00:00:01 |INVALID|INVALID|
----------------------------------------------------------------------------------------------------------------------

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms