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...
0 comments:
Post a Comment