Skip to Main Content
  • Questions
  • Observation regarding Interval partitioning

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Gururaj.

Asked: December 01, 2019 - 12:15 am UTC

Last updated: December 05, 2019 - 2:08 pm UTC

Version: Oracle 12.2.0.1.0

Viewed 1000+ times

You Asked

Hi,

I had some observation regarding Interval partitioning when I was looking into one issue,

Below is the use case


For ex : We have two tables

CREATE TABLE TEST_GURU_1
(
  ENAME VARCHAR2(500),
  EMPDATE DATE
)
PARTITION BY RANGE (EMPDATE)
(  
  PARTITION BUS_DATE_032003 VALUES LESS THAN (TO_DATE(' 2003-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
               
               

CREATE TABLE TEST_GURU
(
  ENAME VARCHAR2(500),
  EMPDATE DATE
)
PARTITION BY RANGE (EMPDATE)
INTERVAL( NUMTOYMINTERVAL(1, 'MONTH'))
(  
  PARTITION BUS_DATE_032003 VALUES LESS THAN (TO_DATE(' 2003-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));


TEST_GURU_1 table is created without interval partition
TEST_GURU table is created with interval partition


As of now table is empty ,I fire below queries

Greater than 15-dec-9999

SELECT * FROM TEST_GURU_1  
where EMPDATE > to_date('12/15/9999','mm/dd/yyyy') ;
--This works fine and gives 0 rows


SELECT * FROM TEST_GURU
where EMPDATE > to_date('12/15/9999','mm/dd/yyyy') ;
--This works fine and gives 0 rows


--Greater than 16-dec-9999
SELECT * FROM TEST_GURU_1  
where EMPDATE > to_date('12/16/9999','mm/dd/yyyy') ;
--This works fine and gives 0 rows(For table without interval partition)


SELECT * FROM TEST_GURU
where EMPDATE > to_date('12/16/9999','mm/dd/yyyy') ;
--This throws error
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


I wanted to know why this behavior only for the Interval partitioned table.


Just curious about the issue here.

Regards
Gururaj

and Connor said...

I suspect its a bug that has been fixed, because I cannot reproduce that on my 12.2 or 18c instance.

SQL> CREATE TABLE TEST_GURU_1
  2  (
  3    ENAME VARCHAR2(500),
  4    EMPDATE DATE
  5  )
  6  PARTITION BY RANGE (EMPDATE)
  7  (
  8    PARTITION BUS_DATE_032003 VALUES LESS THAN (TO_DATE(' 2003-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

Table created.

SQL>
SQL>
SQL>
SQL> CREATE TABLE TEST_GURU
  2  (
  3    ENAME VARCHAR2(500),
  4    EMPDATE DATE
  5  )
  6  PARTITION BY RANGE (EMPDATE)
  7  INTERVAL( NUMTOYMINTERVAL(1, 'MONTH'))
  8  (
  9    PARTITION BUS_DATE_032003 VALUES LESS THAN (TO_DATE(' 2003-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

Table created.

SQL>
SQL> SELECT * FROM TEST_GURU_1
  2  where EMPDATE > to_date('12/15/9999','mm/dd/yyyy') ;

no rows selected

SQL>
SQL> SELECT * FROM TEST_GURU
  2  where EMPDATE > to_date('12/15/9999','mm/dd/yyyy') ;

no rows selected

SQL>
SQL> SELECT * FROM TEST_GURU_1
  2  where EMPDATE > to_date('12/16/9999','mm/dd/yyyy') ;

no rows selected

SQL>
SQL> SELECT * FROM TEST_GURU
  2  where EMPDATE > to_date('12/16/9999','mm/dd/yyyy') ;

no rows selected




So perhaps get in touch with Support and see if there is a patch for your platform

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

on CODE Tags

Rajeshwaran Jeyabal, December 02, 2019 - 11:12 am UTC

Perhaps the closing tag of the CODE is incorrect.
that would make the above code in a formatted way to read.
Chris Saxon
December 05, 2019 - 2:08 pm UTC

Thanks, fixed.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.