Interested to Buy Any Domain ? << Click Here >> for more details...
How to delete duplicate records if we have huge volume of
records in a table ?
(rowid is not the correct approach)
- 6 Answers
- 10453 Views
- Puma, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / keyrun
Hi, There are many ways to delete the duplicates!
By Using the following Transformations you can delete the Duplicates:
Source Qualifier: Use SQ and check 'Distinct' property (If the Source is Relational)
Sorter: Use sorter and in Properties tab Check 'Distinct'(If the Source is Flat File)
Aggregator: Use Aggregator & Group by on the key port.
| Is This Answer Correct ? | 7 Yes | 0 No |
Answer / chandu
select * from from <table_name> where rowid not in(select
min(rowid) from <table_name> group by column(primary or
unique key column))
| Is This Answer Correct ? | 5 Yes | 0 No |
First we can count the no of records using aggregator
Transformation with group by (port) for ex, in emp table
take empno. Second we can take the Filter condition like
count=1. Other records are rejected not loaded into the target.
Hope it will help little bit...
| Is This Answer Correct ? | 1 Yes | 1 No |
Answer / cmanojkumar
Hi,
Thanks for your answer.
I am sorry this question i need to post in oracle forum not
informatica.
Any way your answer is usefull for me.
Could you please tell me how can we delete with our rowid in
oracle ?
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / ravikumar2614
DELETE FROM EMP E1 WHERE ROWID<
(SELECT MAX(ROWID) FROM EMP E2 WHERE E1.ROWID=E2.ROWID)
IF I AM WRONG PLEASE CORRECT ME ON ravi.info2614@gmail.com
| Is This Answer Correct ? | 1 Yes | 1 No |
Answer / dilip ingole
Delete from emp where rowid not in(select min(rowid) from emp group by eid,ename)
in group by column you need mention all column in table
| Is This Answer Correct ? | 0 Yes | 0 No |
Source --------- eno , ename, sal 001,ank,1000 002,behe,2000 003,raj,2500 Target ------- header :DDMMYYYY24MMSS timestamp body: 001,ank,1000 002,behe,2000 003,raj,2500 footer: count(eno),sum(sal) how to implement the same in a FF tgt?
Suppose we have two source qualifier transformations sq1 and sq2 connected to target tables tgt1 and tgt2 respectively. How do you ensure tgt2 is loaded after tgt1?
What are teh different tasks that can be created in workflow manager?
Q. We are the loading the table on daily basis it is incremental loading. and A person rahul slary was 10000, so if i check before run my salalr is 10000. but toay there is update that my sal is 15k but that will come to know after the load. braod crtiteria is we donot want to show downstream teams partial updated data. need aproad as etl developer
Performance wise which is better joiner or look up ? Give me with example?
Work flow failed when it is set to Bulk mode but it was successful when set to normal mode y??(All the connections are fine and exactly correct)?
What is data transformation manager process?
What is source qualifier transformation in informatica?
What are the types of caches in lookup?
COM components can be used in Informatica
how to calculate the optimum cache size in aggregator transformation?
how to design mapping half of source table records into one target and remaining half into another target
Teradata 
