Oracle Partition by 2 columns Interval

Im trying to find on google my situation but no one talk about this situation.

i have a table thats is gonna be partitionized with 2 columns. for 2 columns partitions can anyone show an example for the interval?

In this case i have only one. For this example how do i use an interval with 2 columns

 INTERVAL( NUMTODSINTERVAL(1,'DAY'))

My table:

create table TABLE_TEST
(
PROCESS_DATE DATE GENERATED ALWAYS AS (TO_DATE(SUBSTR("CHARGE_DATE_TIME",1,10),'yyyymmdd')),
PROCESS_HOUR VARCHAR(10) GENERATED ALWAYS AS     (SUBSTR("CHARGE_DATE_TIME",12,2)),
ANUM varchar(100),
SWTICH_DATE_TIME varchar(100), 
CHARGE_DATE_TIME varchar(100),
CHARGE varchar(100),
)
TABLESPACE TB_LARGE_TAB
PARTITION BY RANGE (PROCESS_DATE, PROCESS_HOUR)
INTERVAL( NUMTODSINTERVAL(1,'DAY'))

Many Thanks, Macieira

Solution

You can't use an interval if your range has more than one column; you'd get: ORA-14750: Range partitioned table with INTERVAL clause has more than one column. From the documentaion:

You can specify only one partitioning key column, and it must be of NUMBER, DATE, FLOAT, or TIMESTAMP data type.

I'm not sure why you're splitting the date and hour out into separate columns (since a date has a time component anyway), or why you're storing the 'real' date and number values as strings; it would be much simpler to just have columns with the correct data types in the first place. But assuming you are set on storing the data that way and need the separate process_date and process_hour columns as you have them, you can add a third virtual column that combines them:

create table TABLE_TEST
(
  PROCESS_DATE DATE GENERATED ALWAYS AS (TO_DATE(SUBSTR(CHARGE_DATE_TIME,1,10),'YYYYMMDD')),
  PROCESS_HOUR VARCHAR2(8) GENERATED ALWAYS AS (SUBSTR(CHARGE_DATE_TIME,12,2)),
  PROCESS_DATE_HOUR DATE GENERATED ALWAYS AS (TO_DATE(CHARGE_DATE_TIME, 'YYYYMMDDHH24')),
  ANUM VARCHAR2(100),
  SWTICH_DATE_TIME VARCHAR2(100), 
  CHARGE_DATE_TIME VARCHAR2(100),
  CHARGE VARCHAR2(100)
)
PARTITION BY RANGE (PROCESS_DATE_HOUR)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(
  PARTITION TEST_PART_0 VALUES LESS THAN (DATE '1970-01-01')
);

Table table_test created.

I've also changed your string data types to varchar2 and added a made-up initial partition. process_hour probably wants to be a number type, depending on how you'll use it. As I don't know why you're choosing your current data types it's hard to tell what would really be more appropriate.

I don't really understand why you'd want the partition range to be hourly and the interval to be one day though, unless you want the partitions to be from, say, midday to midday; in which case the initial partition (test_part_0) would have to specify that time, and your range specification is still wrong for that.