11-01-2016 11:43 PM
I have a teradata table with 1.5 billion rows of data. I need to filter this table on 1+ million account numbers, so I can only get those rows from the teradata table. The second table containing only the account numbers is in SAS locally obtained from a totally different DW.
I thought of using "intersect", but the issue is that the first table has several columns, and the second table got only one column containing account numbers, so it won't work. Then I thought of adding all account numbers into a macro and pass that in the explicit passthrough. But then the macro values will go beyond 32,767 character limit.
It seems I have to use implicit passthrough, but I want this to be done quick not taking hours.
Is there any other ways this can be accomplished within a matter of minutes vs. hours?
11-02-2016 12:04 AM
Thanks, I forgot to mention the teradata got only read access, no write access to this production db.
11-02-2016 12:14 AM
Most databases have special temporary space just for this purpose:
Check with your Teradata DBA to see if you can have access to this. This is the most efficient method - any other cross-database technique will be extremely slow.
11-02-2016 07:20 AM
You can use IN operator too. select * from tera where id in (select id form sas); OR Hash Table.
11-02-2016 10:28 AM
11-02-2016 10:32 AM
Thanks to both. I already tried that in clause using implicit passthrough like you stated, but it didn't finish and I fed up of it, then stopped. We do have write access to a different database within teradata, so I think I will load the 1+mil account numbers into a teradata db that we have access to write, then use that in the in clause filter via explicit passthrough.
I didn't know about the temporary table space - but that is a good info. Thanks again.
11-02-2016 01:04 PM
11-02-2016 03:58 PM
To speed up the database access use EXISTS statement in SQL rather than the IN statement
Select *from table_a a
where exists(select*from orders o
It is best to write a SQL statement with subquery.