Partition

Database
KVB
Enthusiast

Partition

Hi ,

 I have a partitioned table from 2013 and having almost 550 partitions.Most of the data looks redundant except the load date.So ,planning to identify the changes and remove the same attribute records and dump into a non-partitioned table.

What's the best approach to workout?

Example:

CREATE TABLE HODS

(

ACCNO INTEGER,

ACCNAME VARCHAR2(10),

SAL DECIMAL(7,2),

EFFDT DATE

);

 

 

INSERT INTO HODS VALUES(1,'KVB',100,'01-JAN-15');

INSERT INTO HODS VALUES(1,'KVB',100,'02-JAN-15');

INSERT INTO HODS VALUES(1,'KVB',100,'03-JAN-15');

INSERT INTO HODS VALUES(1,'KVB',200,'04-JAN-15');

INSERT INTO HODS VALUES(1,'KVB',200,'05-JAN-15');

INSERT INTO HODS VALUES(1,'KVB',100,'06-JAN-15');

INSERT INTO HODS VALUES(1,'KVB',100,'07-JAN-15');

INSERT INTO HODS VALUES(1,'KVB',200,'08-JAN-15');

COMMIT;

 

Expected Output:

 

1,KVB,100,01-JAN-15

1,KVB,200,04-JAN-15

1,KVB,100,06-JAN-15

1,KVB,200,08-JAN-15

Regards

KVB

Tags (1)
1 REPLY
Junior Contributor

Re: Partition

What's your Teradata release?

In TD14.10 this could be done with an INS/SEL into a normalized table or using 

SELECT ACCNO, ACCNAME, SAL, BEGIN(pd)
FROM
(
SELECT NORMALIZE ACCNO, ACCNAME, SAL, PERIOD(EFFDT, EFFDT + 1) AS pd
FROM HODS
) AS dt

Before TD14.10 it's more complicated, e.g. using the TD_NORMALIZE_MEET function:

WITH cte(ACCNO, ACCNAME, SAL, pd) AS
(
SELECT ACCNO, ACCNAME, SAL
,PERIOD(EFFDT, EFFDT + 1) AS pd
FROM HODS
)
SELECT ACCNO, ACCNAME, SAL
,BEGIN(Pd)
FROM
TABLE
(TD_NORMALIZE_MEET
(NEW VARIANT_TYPE(cte.ACCNO, cte.ACCNAME, cte.SAL)
,cte.pd)
RETURNS (ACCNO INTEGER
,ACCNAME VARCHAR(10)
,SAL DECIMAL(7,2)
,pd PERIOD(DATE))
HASH BY ACCNO, ACCNAME, SAL
LOCAL ORDER BY ACCNO, ACCNAME, SAL, pd
) AS dt