Skip to main content

A list partitiong bug

Last week, I found a not-so-nice bug in 10gR2. Just try this short test case, creating a table with two list partitions, default and null:
alter session set nls_territory=America;
alter session set nls_language=American;

select version from product_component_version;

drop table th3;

prompt Creating TH3...
create table TH3
(
DF DATE,
DT DATE,
N NUMBER
)
partition by list (DT)
(
partition THTBL_CURRENT values (NULL)
tablespace USERS,
partition THTBL_OLD values (default)
tablespace USERS
)
;

prompt Loading TH3...
insert into TH3 (DF, DT, N)
values (to_date('01-01-2000', 'dd-mm-yyyy'), to_date('10-04-2008', 'dd-mm-yyyy'), 1);
insert into TH3 (DF, DT, N)
values (to_date('01-01-1000', 'dd-mm-yyyy'), to_date('01-03-3000', 'dd-mm-yyyy'), 2);
insert into TH3 (DF, DT, N)
values (to_date('10-04-2008', 'dd-mm-yyyy'), to_date('10-04-2008', 'dd-mm-yyyy'), 10);
insert into TH3 (DF, DT, N)
values (to_date('11-04-2008', 'dd-mm-yyyy'), to_date('11-04-2008', 'dd-mm-yyyy'), 11);
insert into TH3 (DF, DT, N)
values (to_date('10-04-2008', 'dd-mm-yyyy'), to_date('11-04-2008', 'dd-mm-yyyy'), 1011);
commit;
prompt 5 records loaded
prompt Done.

SELECT to_date('10.04.2008', 'dd.mm.yyyy'), N, df, dt, rowid
FROM th3
WHERE to_date('10.04.2008', 'dd.mm.yyyy')
BETWEEN DF AND dt;

SELECT to_date('10.04.2008', 'dd.mm.yyyy'), N, df, dt, rowid
FROM th3
WHERE to_date('10.04.2008', 'dd.mm.yyyy')
BETWEEN DF AND
nvl(dt,to_date('10.04.9999', 'dd.mm.yyyy'));

exit;

Although the selects should not differ in the results (there is no NULL value in dt column), they do:

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 12 14:59:05 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected To:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


Session altered.


Session altered.


VERSION
--------------------------------------------------------------------------------
10.2.0.1.0
10.2.0.1.0
10.2.0.1.0
10.2.0.1.0

drop table th3
*
ERROR at line 1:
ORA-00942: table or view does not exist


Creating TH3...

Table created.

Loading TH3...

1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.

5 records loaded
Done.

no rows selected


TO_DATE(' N DF DT ROWID
--------- ---------- --------- --------- ------------------
10-APR-08 1 01-JAN-00 10-APR-08 AAAM1CAAEAAAAGtAAA
10-APR-08 2 01-JAN-00 01-MAR-00 AAAM1CAAEAAAAGtAAB
10-APR-08 10 10-APR-08 10-APR-08 AAAM1CAAEAAAAGtAAC
10-APR-08 1011 10-APR-08 11-APR-08 AAAM1CAAEAAAAGtAAE

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

The same results were obtained for 10.2.0.3. However, in the 10.2.0.4 (and 11.1.0.6), it seems fixed:

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 12 18:45:30 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Session altered.


Session altered.


VERSION
--------------------------------------------------------------------------------
10.2.0.4.0
10.2.0.4.0
10.2.0.4.0
10.2.0.4.0

drop table th3
*
ERROR at line 1:
ORA-00942: table or view does not exist


Creating TH3...

Table created.

Loading TH3...

1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.

5 records loaded
Done.

TO_DATE(' N DF DT ROWID
--------- ---------- --------- --------- ------------------
10-APR-08 1 01-JAN-00 10-APR-08 AAAM7pAAEAAAAGlAAA
10-APR-08 2 01-JAN-00 01-MAR-00 AAAM7pAAEAAAAGlAAB
10-APR-08 10 10-APR-08 10-APR-08 AAAM7pAAEAAAAGlAAC
10-APR-08 1011 10-APR-08 11-APR-08 AAAM7pAAEAAAAGlAAE


TO_DATE(' N DF DT ROWID
--------- ---------- --------- --------- ------------------
10-APR-08 1 01-JAN-00 10-APR-08 AAAM7pAAEAAAAGlAAA
10-APR-08 2 01-JAN-00 01-MAR-00 AAAM7pAAEAAAAGlAAB
10-APR-08 10 10-APR-08 10-APR-08 AAAM7pAAEAAAAGlAAC
10-APR-08 1011 10-APR-08 11-APR-08 AAAM7pAAEAAAAGlAAE

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


If you create the table as nonpartitioned, you get correct results irrespective of version.

Tested on Windows 32-bit (on 10.2.0.1, 10.2.0.3 and 10.2.0.4) and RHEL 5.1 x86_64 (10.2.0.3).

If you are curious, here's the explain plan (10.2.0.3) for the first select:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4063410327

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


2 - filter("DF"<=TO_DATE('2008-04-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "DT">=TO_DATE('2008-04-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

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

On 10.2.0.4, I get:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3503314195

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


2 - filter("DF"<=TO_DATE('2008-04-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "DT">=TO_DATE('2008-04-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

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

So it looks like a problem in partition elimination - on 10.2.0.3, Oracle didn't evaluate the involved partitions properly.

No patch was provided by Oracle, as we have two luxurious options: a workaround and 10.2.0.4. And of course, the second select is after all the correct one for the initial problem...

Comments

Popular posts from this blog

ORA-27048: skgfifi: file header information is invalid

I was asked to analyze a situation, when an attempt to recover a 11g (standby) database resulted in bunch of "ORA-27048: skgfifi: file header information is invalid" errors.

I tried to reproduce the error on my test system, using different versions (EE, SE, 11.1.0.6, 11.1.0.7), but to no avail. Fortunately, I finally got to the failing system:

SQL> recover standby database;
ORA-00279: change 9614132 generated at 11/27/2009 17:59:06 needed for thread 1
ORA-00289: suggestion :
/u01/flash_recovery_area/T1/archivelog/2009_11_27/o1_mf_1_208_%u_.arc
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-00280: change 9614132 for thread 1 is in sequence #208


Interestingly, nothing interesting is written to alert.log n…

Reading data from PGA and SGA

Overview For our investigation of execution plan as it is stored in memory, we need in the first place to be able to read the memory.

We have the options of
x$ksmmem, reading SGA using SQL. Personally I don't like it, it's cumbersome and slow.direct SGA read: obviously reading SGA only; it's fast and easy to doread process memory: can read PGA, process stack - and since the processes do map the SGA, too, you can read it as well. Unfortunately ptrace sends signals to the processes and the process is paused when reading it, but so far all my reads were short and fast and the processes did not notice. Some OS configurations can prevent you from using ptrace (e.g. docker by default), google for CAP_SYS_PTRACE.gdb: using your favorite debugger, you can read memory as well. Useful when investigating. Direct SGA read I always considered direct SGA read of some dark magic, but the fundamentals are actually very easy. It still looks like sorcery when actually reading the Oracle in…

Filter and access predicates

More than just column projections When we look around for further pointers in the tree nodes, we find more pieces resembling the column projections we have seen so far. With some experimenting, we will find out that these are access predicates and filters.

First of all, the location of these pointers is not always the same, it seems that the value at 0x34 is some kind of flag, indicating whether filters and/or access predicates are present, and where. Or whether there is just one, or more of them.  It probably also indicates what other info is present, but I have no idea what info that would be or what each value means.
Resembling, but different The data we see as predicates are not columns; after all, a predicate is a condition, not a single column. But the structure is similar to what we have seen with columns, and if we follow pointers further, we eventually build a tree, and some of the leaves are indeed just column projections.
After some contemplation, we realize it's all t…