How to oracle partition by date range Unix Timestamp-CodePudding

I have below table in oracle, I want Partition Range by Date in Oracle monthly on MyTimestamp column(Data type is number). Can I partition with this column or do I need another column? If I need a new column, what is the data type of the new column and how do I partition with the new column(convert MyTimestamp to new data type and partition )?

----------------------------------------------------------------------------------------------------
| id  | MyTimestamp  |    Name        | etc ...    
----------------------------------------------------------------------------------------------------
|   0 |  1657640396  |    John        |  ...    
|   1 |  1657638832  |    Tom         |  ...    
|   2 |  1657640265  |    Tom         |  ...    
|   3 |  1657640292  |    John        |  ...    
|   4 |  1657640005  |    Jack        |  ...    
--------------------------------------------------------------------------------------------------

CodePudding user response：

If you want to PARTITION by DATE, you need a date column. Below is an example with some dummy data.

When new PARTITIONs are automatically added they will have system GENERATED names. I have code to RENAME them to something meaningful if you like.

In addition, you will probably want to implement a RETENTION period for the PARTITION, how long to keep them around. I also implemented that too.


CREATE TABLE t2 (     
 seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   dt   DATE
)
PARTITION BY RANGE (dt)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
   PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);
/

INSERT into t2 (dt)
with dt (dt, interv) as (
select date '2022-01-01', numtodsinterval(1,'DAY') from dual
union all
select dt.dt   interv, interv from dt
where dt.dt   interv < date '2022-07-31')
select dt from dt;
/

By timestamp 

CREATE TABLE t3 (
seq_num NUMBER  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt TIMESTAMP)
  PARTITION BY RANGE (dt) 
  INTERVAL ( NUMTODSINTERVAL (1, 'MONTH') ) ( 
    PARTITION OLD_DATA VALUES LESS THAN (TIMESTAMP '2022-01-01 00:00:00.000000')
  );
/

INSERT into t3 (dt)
SELECT TIMESTAMP '2022-01-01 00:00:00'
           (LEVEL - 1) * INTERVAL '5' MINUTE
           MOD(LEVEL - 1, 10) * INTERVAL '0.1' SECOND
FROM   DUAL
CONNECT BY
       TIMESTAMP '2022-01-01 00:00:00'
           (LEVEL - 1) * INTERVAL '5' MINUTE
           MOD(LEVEL - 1, 10) * INTERVAL '0.1' SECOND < DATE '2022-01-15';
/