All Forums Database
KVB 124 posts Joined 09/12
13 May 2015
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:
dnoeth 4628 posts Joined 11/04
13 May 2015

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

 

Dieter

You must sign in to leave a comment.