Skip to Main Content
  • Questions

  • how can I partition a table based on Persian months format?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Masoud.

Asked: February 26, 2017 - 10:47 am UTC

Last updated: February 28, 2017 - 2:13 am UTC

Version: 12.2.0.1.0

Viewed 1000+ times

You Asked

Hello Dear Tom
As you know in oracle 12c Iran territory is supported.

col PARAMETER format a30;
col VALUE format a30;
select * from v$nls_valid_values where lower(VALUE) in ('iran','persian');
PARAMETER                      VALUE                          ISDEP     CON_ID
------------------------------ ------------------------------ ----- ----------
LANGUAGE                       PERSIAN                        FALSE          0
TERRITORY                      IRAN                           FALSE          0


I intend to create a partitioned table based on Persian months interval with DDL below:

create table 
pos_data ( 
   start_date DATE
) 
PARTITION BY RANGE (start_date) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(  
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-12-1395', 'DD-MM-YYYY', 'nls_calendar=persian'))
);
Table created.


and I inset 1000 rows in the table

begin

  for i in 0..1000 loop
    insert into pos_data values (sysdate + i);
  end loop;
  commit;
  
end;
/


and when I select high_value, I expect the output is nls_calndar=persian but the real output is:
select high_value from USER_TAB_PARTITIONS;
TO_DATE(' 1395-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-03-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-04-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-05-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-06-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-07-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-08-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-09-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
.
.
.


and current nls_database_parameters and nls_instance_parameters is set to:
select * from NLS_DATABASE_PARAMETERS;
PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_RDBMS_VERSION              12.2.0.1.0
NLS_NCHAR_CONV_EXCP            FALSE
NLS_LENGTH_SEMANTICS           BYTE
NLS_COMP                       BINARY
NLS_DUAL_CURRENCY              $
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_SORT                       BINARY
NLS_DATE_LANGUAGE              AMERICAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_CALENDAR                   GREGORIAN
NLS_NUMERIC_CHARACTERS         .,
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               AL32UTF8
NLS_ISO_CURRENCY               AMERICA
NLS_CURRENCY                   $
NLS_TERRITORY                  AMERICA
NLS_LANGUAGE                   AMERICAN
20 rows selected.

select * from NLS_instance_parameters;
PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  IRAN
NLS_SORT
NLS_DATE_LANGUAGE              PERSIAN
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR                   PERSIAN
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
17 rows selected.


I would like to know, where is the problem ?
best regards,
Masoud

and Connor said...

The key thing there is your NLS_DATABASE_PARAMETERS

NLS_CALENDAR                   GREGORIAN


This is the calendar that was set when your database was *created*.

Your instance level parameters will always override this, but since you can *change* the instance parameters whenever you want, it would appear that the internal dictionary structures are going to store/represent their values in the base database calendar so that they always remain consistent.

As far as I can tell (ie, my persian calendar knowledge is not great), the definitions look correct, interval or not


SQL> create table pos_data (
  2     start_date DATE
  3  )
  4  PARTITION BY RANGE (start_date)
  5  (
  6     PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-12-1395', 'DD-MM-YYYY', 'nls_calendar=persian'))
  7  );

Table created.

SQL>
SQL> col high_value format a90
SQL> set lines 200
SQL>
SQL> select partition_name, high_value
  2  from user_tab_partitions where table_name = 'POS_DATA';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------
POS_DATA_P2                    TO_DATE(' 2017-02-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

1 row selected.

SQL>
SQL> drop table pos_Data purge;

Table dropped.

SQL>
SQL> create table pos_data (
  2     start_date DATE
  3  )
  4  PARTITION BY RANGE (start_date)
  5  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  6  (
  7     PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-12-1395', 'DD-MM-YYYY', 'nls_calendar=persian'))
  8  );

Table created.

SQL>
SQL> select partition_name, high_value
  2  from user_tab_partitions where table_name = 'POS_DATA';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------
POS_DATA_P2                    TO_DATE(' 2017-02-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

1 row selected.

SQL>


Rating

  (1 rating)

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

Comments

Masoud Haji Hassan Pour, February 27, 2017 - 8:07 am UTC

Dear Connor
Thanks for your answer. actually the interval is not correct. in the Gregorian calendar, interval is different from Persian calendar.

For example in this year (2017) Gregorian interval is:
31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31
but in the Persian calendar, in this year (1395) interval is:
31, 31, 31, 31, 31, 31, 30, 30, 30, 30, 30, 30

Relying on mentioned example I will have problem in my partitions. I recreate database with dbca with NLS_CALENDAR=Persian, nls_territory=IRAN and nls_language=PERSIAN and create the partitioned table but the problem remain intact.

what can i do ?
Connor McDonald
February 28, 2017 - 2:13 am UTC

Sorry, I was mistaken.

MOS note 227334.1 talks about which SQL functions are sensitive to the nls_calendar and NUMTOYMINTERVAL isnt one of them.

I think your options are:

a) dont use interval partitioning, but precreate the range partitions like the way we used to before interval partitioning was invented.

b) add a virtual column to derive the persian year/month and partition on that, eg

SQL> create table pos_data (
  2    start_date date,
  3    persian_month as (cast(to_char(start_date, 'YYYYMM', 'nls_calendar=persian') as number))
  4  )
  5  partition by range(persian_month)
  6  interval(1)
  7  (partition p0 values less than (130000))
  8  /

Table created.

SQL> insert into pos_data (start_Date) values ( sysdate );

1 row created.

SQL> insert into pos_data (start_Date) values ( sysdate - 100);

1 row created.

SQL> insert into pos_data (start_Date) values ( sysdate + 100);

1 row created.

SQL> select partition_name, high_value from user_tab_partitions
  2  where table_name = 'POS_DATA'
  3  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ---------------------------------------------------------------------
P0                             130000
SYS_P21492                     139513
SYS_P21493                     139509
SYS_P21494                     139604


But be aware, there would be other implications here, for example, to get partition elimination on queries, they would need to *specify* PERSIAN_MONTH as a predicate.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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.