Oracle partition by interval using persian calendar

oraclepartitioning

Oracle supports Persian (Jalali) calendar in DDL queries, I can easily say:

select to_char(register_date, 'YYYY-MM-DD', 'nls_calendar=persian')
  from my_table;

I created a table as:

create table test_temp_times (
  id number(18) not null,
  xdate date not null,
  str varchar2(20))
partition by range(xdate)
interval(NUMTOYMINTERVAL(1, 'MONTH'))
(partition p0 values less than (to_date('13920101', 'YYYYMMDD', 'nls_calendar=persian')))
enable row movement;

the table is created normally, but when I add records to it, and oracle creates new partitions, the partitions are:

create table TEMP_TIMES (
  id    NUMBER(18) not null,
  xdate DATE not null,
  str   VARCHAR2(20)
)
partition by range (XDATE)
(
  partition P0 values less than (TO_DATE(' 2013-03-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P61 values less than (TO_DATE(' 2013-04-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P62 values less than (TO_DATE(' 2013-05-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P63 values less than (TO_DATE(' 2013-06-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P64 values less than (TO_DATE(' 2013-07-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

as you can see, the database changed the NLS_CALENDAR to gregorian (the same day as 13920101) and each partition is created according to gregorian calendar, not persian calendar.

Is there any way to force Oracle to use persian calendars for creating new partitions?

Best Answer

I don't see any way to have intervals that are defined in a different calendar than your database-level NLS_CALENDAR. You could get the same effect by partitioning on a numeric representation of the (Persian) month each date falls in, using a virtual column:

create table test_temp_times (
  id number(18) not null,
  xdate date not null,
  str varchar2(20),
  ydate as (to_number(to_char(xdate, 'YYYYMM', 'nls_calendar=persian')))
)
partition by range(ydate)
interval(1)
(partition p0 values less than (139201))
enable row movement;

If that is populated with a record for every day for the year following your example start date:

insert into test_temp_times (id, xdate, str)
select level, date '2013-03-20' + level, null
from dual
connect by level < 366;

The partitions that are created will look something like:

select table_name, partition_name, high_value
from user_tab_partitions where table_name = 'TEST_TEMP_TIMES';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
TEST_TEMP_TIMES                P0                             139201     
TEST_TEMP_TIMES                SYS_P479                       139202     
TEST_TEMP_TIMES                SYS_P480                       139203     
TEST_TEMP_TIMES                SYS_P481                       139204     
TEST_TEMP_TIMES                SYS_P482                       139205     
TEST_TEMP_TIMES                SYS_P483                       139206     
TEST_TEMP_TIMES                SYS_P484                       139207     
TEST_TEMP_TIMES                SYS_P485                       139208     
TEST_TEMP_TIMES                SYS_P486                       139209     
TEST_TEMP_TIMES                SYS_P487                       139210     
TEST_TEMP_TIMES                SYS_P488                       139211     
TEST_TEMP_TIMES                SYS_P489                       139212     
TEST_TEMP_TIMES                SYS_P490                       139213     

 13 rows selected

And you can check which partitions the month boundaries fall in:

select utp.partition_name, min(ttt.xdate), max(ttt.xdate)
from test_temp_times ttt
join user_objects uo on uo.object_id = dbms_rowid.rowid_object(ttt.rowid)
join user_tab_partitions utp on utp.table_name = uo.object_name
and utp.partition_name = uo.subobject_name
group by utp.partition_name
order by partition_name;

PARTITION_NAME                 MIN(TTT.XDATE) MAX(TTT.XDATE)
------------------------------ -------------- --------------
P0                             2013-03-20     2013-03-20     
SYS_P479                       2013-03-21     2013-04-20     
SYS_P480                       2013-04-21     2013-05-21     
SYS_P481                       2013-05-22     2013-06-21     
SYS_P482                       2013-06-22     2013-07-22     
SYS_P483                       2013-07-23     2013-08-22     
SYS_P484                       2013-08-23     2013-09-22     
SYS_P485                       2013-09-23     2013-10-22     
SYS_P486                       2013-10-23     2013-11-21     
SYS_P487                       2013-11-22     2013-12-21     
SYS_P488                       2013-12-22     2014-01-20     
SYS_P489                       2014-01-21     2014-02-19     
SYS_P490                       2014-02-20     2014-03-19

At least, I think that's what you're trying to achieve... Unfortunately I can't add a demo as SQL Fiddle doesn't have the partitioning option, but this is tested against 11.2.0.3.

Of course, you have to make it use the partitions for the query... if I just do:

select * from test_temp_times
where xdate = date '2013-11-01';

It finds the row with plan:

-----------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |     1 |    47 |   164   (0)| 00:00:02 |       |       |
|   1 |  PARTITION RANGE ALL|                 |     1 |    47 |   164   (0)| 00:00:02 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | TEST_TEMP_TIMES |     1 |    47 |   164   (0)| 00:00:02 |     1 |1048575|
-------------------------------------------------------------------------------------------------------

If I explicitly add the virtual column to the query:

select * from test_temp_times
where xdate = date '2013-11-01'
and ydate = to_number(to_char(date '2013-11-01', 'YYYYMM', 'nls_calendar=persian'));

Then it knows which partition to query:

----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |    47 |    14   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|                 |     1 |    47 |    14   (0)| 00:00:01 |     9 |     9 |
|*  2 |   TABLE ACCESS FULL    | TEST_TEMP_TIMES |     1 |    47 |    14   (0)| 00:00:01 |     9 |     9 |
----------------------------------------------------------------------------------------------------------

Clearly I haven't created any indexes yet. If you are looking for a whole month's worth of data, you would only need to query on a single ydate value, and ignore xdate; but presumably you'd need a mix at least some of the time.

Related Question