June 23, 2009 at 9:17 am
I have two tables.
Table 1 resides on SQL server 2005 and has two columns which contain composite primary key for records that need extracted from Table 2.
Table 2 resides on Oracle Server and has a composite primary key. I need to extract records from Table 2 into a database based on the keys provided in Table 1.
The problem is, Table #2 has over 100 million rows, so moving it into a staging table on SQL server is very time-consuming. what can i use in SSIS to create a package for this?
June 23, 2009 at 1:23 pm
My first question for you would be is if this is a one time process or if you have to continually do this? If one time, it would be easiest to have a SSIS package that copied the data to a staging table and then work with it in SQL.
I don't know how efficient it would be, but I believe it would be possible to craete a view against the Oracle table and join it to your SQL table to get the rows you want.
No matter how you do it, its not going to be a quick process when you are talking that many rows and linked servers.
John
June 24, 2009 at 1:12 am
John is absolutely right on this one. I don't know Oracle, but perhaps it is possible to setup replication to push\pull the records from Oracle to SQL as they get added to your Oracle table. That way you would only have to query the SQL tables which should be a lot quicker.
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
June 24, 2009 at 9:31 am
Being much more a SQL guy and than an Oracle guy, i'm going to go out on a limb here and say the opposite - if your 'selection' records (ie table #1) count is significantly smaller than the >100 million in the primary Oracle table, then I would argue that a process that pushes these to Oracle (or allows Oracle to read directly from the table) and then let Oracle do the heavy lifting of the record selection from the 100MM+ rows, is going to be much more performant that any solution that tries to drag the records back into the SQL realm (where the only real justification is that we all like to work there rather than in Oracle :-D).
If the row count was large coming back from that process, I'd prob get Oracle to dump (with a specified format) to text and then either SSIS or BCP the result in to the target (or staging, depending on your process and preference).
Steve.
June 25, 2009 at 6:01 am
Thanks everyone for the ideas. A couple of things I should've mentioned is that I have read-only access to the oracle table, the oracle side will not change any of their processes and this process will need to be performed on a regular basis. Back to the drawing board:)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply