How to write huge table data to file | Informatica 10.x

yyy62103 0
Viewed 34

I have created a Informatica flow

where I need to read data from table that to only one column which contain empids.

But the column might contain duplicate need to write distinct values to file from below query

Query :

select distinct
   emp_id 
from
   employee 
where
   empid not in 
   (
      select distinct
         custid 
      from
         customer
   );

I have added the above query in Source Qualifier

employee table contains : 5 million records and customer table contains : 20 billion records

My Informatica is still running not got completed - 6 hours over till now and nothing is written to file because of huge data in both tables

Following is my query plan

--------------------------------------------------------------------
Id   | Operation                                     |  Name       |
--------------------------------------------------------------------
0    | SELECT STATEMENT                              |             |
1    |   AX COORDINATOR                              |             |
2    |     AX SEND QC (RANDOM)                       | :AQ10002    |
3    |       HASH UNIQUE                             |             |
4    |          AX RECEIVE                           |             |
5    |             AX SEND HASH                      | :AQ10001    |
6    |               HASH UNIQUE                     |             |
7    |                  HASH JOIN ANTI               |             |
8    |                     AX RECEIVE                |             |
9    |                       AX SEND PARTITION (KEY) | :AQ10000    |
10   |                          AX SELECTOR          |             |
11   |              INDEX FAST FULL SCAN             | PK_EMP_ID   |
12   |           AX PARTITION RANGE ALL              |             |
13   |              INDEX FAST FULL SCAN             | PK_CUST_ID  |
--------------------------------------------------------------------

Sample table data :

employee

111
123
145
1345
111
123
145
678
....

customer

111
111
111
1345
111
145
145
145
145
145
145
....

Expected output :

123
678

Any solution is much appreciated !!!

sql-execution-plan informatica oracle-sqldeveloper informatica-powercenter oracle
2 Answers

It seems to me the SQL is the problem. If you dont have anything like sorter/aggregator, you dont have to worry about infa. SQL seems to be having expensive operations. You can try below -

select emp_id 
from employee 
where not exists
(select 1 from customer where custid =emp_id)

This should be little faster because

  1. you arent doing a subquery to get distinct from a 20billion customer table.
  2. you dont need to use any distinct in first select because you are selecting from emp table where that emp id is unique. And not exist will make sure no duplicates coming out of first select.

You can also use left join +where but i think it will be expensive because of join-induced duplicates.

Koushik Roy0

I would start with partitioning the customer table by hash or range(customer_id) or insert_date, this would speed up your inline select substantially.

Also try this:

select emp_id from employee
minus 
select emp_id from employee e, customer c
where e.emp_id=c.custid;
access_granted0
Related