Hello,
partitioning on a DATE Column for partiztion Pruning not possible ?
But it should:
2416490 - FAQ: SAP HANA Data Aging in SAP S/4HANA
Got a Table:
CREATE TABLE MYPART ( XINTEGER INTEGER
, YDATE DATE
, ZVARCHAR2 VARCHAR(2)
);
...add a Primary Key:
ALTER TABLE MYPART add constraint MYPART Primary KEY( XINTEGER
, YDATE );
...add a partition on the YDATE , the DATE Column
ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION '01.01.2000' <= VALUES < 'DD.MM.YYYY')
...and get:
Could not execute 'ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION '01.01.2000' <= VALUES < 'DD.MM.YYYY')' in 32 ms 321 µs . SAP DBTech JDBC: [2048]: column store error: fail to alter partition: [2594] General partitioning error;Error when handling partition specification. Value '01.01.2000' could not be converted into an integer.
Other try with OTHERS
ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION '01.01.2000' <= VALUES < 'DD.MM.YYYY' , PARTITION OTHERS)
...and get the same:
Could not execute 'ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION '01.01.2000' <= VALUES < 'DD.MM.YYYY' , ...' in 35 ms 629 µs . SAP DBTech JDBC: [2048]: column store error: fail to alter partition: [2594] General partitioning error;Error when handling partition specification. Value '01.01.2000' could not be converted into an integer.
Might think formating wiill help:
ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION to_DATE('01.01.2000', 'DD.MM.YYYY') <= VALUES < to_DATE('DD.MM.YYYY', 'DD.MM.YYYY') , PARTITION OTHERS)
...and get:
Could not execute 'ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION to_DATE('01.01.2000', 'DD.MM.YYYY') <= ...' SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "to_DATE": line 1 col 59 (at pos 59)
No Range Partitioning on a DATE Column ???
Best Martin