Importing Subset of Records From Oracle.

[rodeomount]

I have been tasked with finding a way to import a subset of records from a table on a remote Oracle database. The table has 80 to 90 million records so I don't want to import the entire table because I will only need approximately 10,000 records. I have a table on SQL Server that has the same fields that the Oracle table has. This SQL table has the primary key values which match it's corresponding record in the oracle database. I will be wanting to import from oracle based on the primary key in the SQL table. The only way I know to do this is to use lookups to retrieve the Oracle data for each field as I transfer the records from the SQL table to another SQL staging table. I'm thinking this will take too long to run since there are around 50 lookups for each field in each record. Is there a faster way to do this? If I could just perform an inner join it would be great but I don't think thats a possible option. Thanks in advance.

 

[mrdenny]

Create a table on the Oracle server and transfer the primary key values to that table. Then do a select from the oracle table joining to the table you created and populated. Then insert the data from your recordset into your SQL Server table.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[rodeomount]

Thanks but unfortunately I'm only allowed read access to the remote server.

 

[mrdenny]

See if you can get the Oracle admin to create the table for you and give you insert, delete and select rights to the table. Explain to him/her what you are doing and that you are trying to improve the speed that the process runs while reducing the load on the Oracle database.

Oracle DBAs usually hate it when people put un-needed load on thier database (don't we all really).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[rodeomount]

Would it be possible to insert the needed records into a temp table then use a foreach container to cycle through the temp table updating each record with the values from the Oracle table?

 

[mrdenny]

I'm not sure how Oracle handles it's temp tables. If it does them like SQL then probably not as the temp table is only available to the session which created it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MDXer]

Is there some form of timestamp in the Oracle table that can be used to determine when a record has been modified or added?

If so you can base your processing on a date span and read in only the records that have been modified or added.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[rodeomount]

Is there a way to use parellel processing to accomplish this? I have a linked server and running the following query times out:

Update dbo.TPSVCREQ
SET dbo.TPSVCREQ.ResultsFlag = 1,
--dbo.TPSVCREQ.ClientID = V.ClientID,
--dbo.TPSVCREQ.SolutionID = V.SolutionID,
dbo.TPSVCREQ.ResendFlag = 0,
--dbo.TPSVCREQ.LogTime = getdate(),
dbo.TPSVCREQ.SVC_MS_REC_STS_CD = V.SVC_MS_REC_STS_CD,
dbo.TPSVCREQ.SMT_NR = V.SMT_NR,
dbo.TPSVCREQ.PKG_XPT_POR_CD = V.PKG_XPT_POR_CD,
dbo.TPSVCREQ.AC_PSL_CD = V.AC_PSL_CD,
dbo.TPSVCREQ.PKG_IPT_POR_CD = V.PKG_IPT_POR_CD,
dbo.TPSVCREQ.CNS_PSL_CD = V.CNS_PSL_CD,
dbo.TPSVCREQ.SVC_TYP_CD = V.SVC_TYP_CD,
dbo.TPSVCREQ.SMT_SHP_DT = V.SMT_SHP_DT,
dbo.TPSVCREQ.PKG_XDP_ACT_DT = V.PKG_XDP_ACT_DT,
dbo.TPSVCREQ.PKG_TNS_DY_ACT_QY = V.PKG_TNS_DY_ACT_QY,
dbo.TPSVCREQ.PKG_TNS_DY_PLN_QY = V.PKG_TNS_DY_PLN_QY,
dbo.TPSVCREQ.PKG_DEL_DT = V.PKG_DEL_DT,
dbo.TPSVCREQ.PKG_XCP_TYP_CD = V.PKG_XCP_TYP_CD,
dbo.TPSVCREQ.PKG_XCP_DT = V.PKG_XCP_DT,
dbo.TPSVCREQ.PKG_DDY_ACT_TOT_QY = V.PKG_DDY_ACT_TOT_QY,
dbo.TPSVCREQ.PKG_DDY_PLN_TOT_QY = V.PKG_DDY_PLN_TOT_QY,
dbo.TPSVCREQ.MPS_PKG_IR = V.MPS_PKG_IR,
dbo.TPSVCREQ.PKG_ORG_SN_SF_IR = V.PKG_ORG_SN_SF_IR,
dbo.TPSVCREQ.PKG_TNS_SF_IR = V.PKG_TNS_SF_IR,
dbo.TPSVCREQ.PKG_DTN_SF_IR = V.PKG_DTN_SF_IR,
dbo.TPSVCREQ.PKG_SF_SMT_STS_IR = V.PKG_SF_SMT_STS_IR,
dbo.TPSVCREQ.PKG_HLD_RSN_DSC_TE = V.PKG_HLD_RSN_DSC_TE,
dbo.TPSVCREQ.PKG_TOT_SF_IR = V.PKG_TOT_SF_IR,
dbo.TPSVCREQ.SMT_SHP_DT_NVL_IR = V.SMT_SHP_DT_NVL_IR,
dbo.TPSVCREQ.AC_PSL_CD_NVL_IR = V.AC_PSL_CD_NVL_IR,
dbo.TPSVCREQ.ORG_SN_DT_NVL_IR = V.ORG_SN_DT_NVL_IR,
dbo.TPSVCREQ.CNS_PSL_CD_NVL_IR = V.CNS_PSL_CD_NVL_IR,
dbo.TPSVCREQ.DTN_SN_DT_NVL_IR = V.DTN_SN_DT_NVL_IR,
dbo.TPSVCREQ.PKG_DEL_DT_NVL_IR = V.PKG_DEL_DT_NVL_IR,
dbo.TPSVCREQ.AC_NR = V.AC_NR,
dbo.TPSVCREQ.IPR_AC_NR = V.IPR_AC_NR,
dbo.TPSVCREQ.PKG_NOT_SN_IR = V.PKG_NOT_SN_IR,
dbo.TPSVCREQ.PKG_TCK_NR = V.PKG_TCK_NR,
dbo.TPSVCREQ.PKG_PLN_DEL_TM = V.PKG_PLN_DEL_TM,
dbo.TPSVCREQ.SVC_GUA_IR = V.SVC_GUA_IR,
dbo.TPSVCREQ.SMT_SAT_DEL_IR = V.SMT_SAT_DEL_IR,
dbo.TPSVCREQ.PKG_DEL_TM = V.PKG_DEL_TM,
dbo.TPSVCREQ.PKG_RCV_CRF_SIG_TE = V.PKG_RCV_CRF_SIG_TE,
dbo.TPSVCREQ.PKG_SAT_DEL_SVC_IR = V.PKG_SAT_DEL_SVC_IR,
dbo.TPSVCREQ.PKG_XCL_TYP_CD = V.PKG_XCL_TYP_CD,
dbo.TPSVCREQ.PKG_XCL_DT = V.PKG_XCL_DT,
dbo.TPSVCREQ.IMS_UDT_TS = V.IMS_UDT_TS,
dbo.TPSVCREQ.SMT_DCL_VLU_A = V.SMT_DCL_VLU_A,
dbo.TPSVCREQ.CCY_CD = V.CCY_CD,
dbo.TPSVCREQ.SMT_DOC_TYP_CD = V.SMT_DOC_TYP_CD,
dbo.TPSVCREQ.SMT_TYP_CD = V.SMT_TYP_CD,
dbo.TPSVCREQ.AC_POL_DIV_2_NA = V.AC_POL_DIV_2_NA,
dbo.TPSVCREQ.CNS_POL_DIV_2_NA = V.CNS_POL_DIV_2_NA,
dbo.TPSVCREQ.PKG_WGT_QY = V.PKG_WGT_QY,
dbo.TPSVCREQ.WGT_MS_UNT_TYP_CD = V.WGT_MS_UNT_TYP_CD,
dbo.TPSVCREQ.AC_NA = V.AC_NA,
dbo.TPSVCREQ.IPR_NA = V.IPR_NA,
dbo.TPSVCREQ.AC_SHR_SMT_REF_NR = V.AC_SHR_SMT_REF_NR,
dbo.TPSVCREQ.AC_CNS_SMT_REF_NR = V.AC_CNS_SMT_REF_NR,
dbo.TPSVCREQ.SMT_WGT_QY = V.SMT_WGT_QY,
dbo.TPSVCREQ.SMT_WGT_MS_UNT_CD = V.SMT_WGT_MS_UNT_CD,
dbo.TPSVCREQ.PKG_MS_IR = V.PKG_MS_IR,
dbo.TPSVCREQ.ORG_REG_NR = V.ORG_REG_NR,
dbo.TPSVCREQ.ORG_DIS_NR = V.ORG_DIS_NR,
dbo.TPSVCREQ.DTN_REG_NR = V.DTN_REG_NR,
dbo.TPSVCREQ.DTN_DIS_NR = V.DTN_DIS_NR,
dbo.TPSVCREQ.PKG_ORG_CNY_CD = V.PKG_ORG_CNY_CD,
dbo.TPSVCREQ.ORG_OGZ_NR = V.ORG_OGZ_NR,
dbo.TPSVCREQ.ORG_FAC_LOC_NR = V.ORG_FAC_LOC_NR,
dbo.TPSVCREQ.PKG_ORG_SN_DT = V.PKG_ORG_SN_DT,
dbo.TPSVCREQ.DTN_CNY_CD = V.DTN_CNY_CD,
dbo.TPSVCREQ.DTN_OGZ_NR = V.DTN_OGZ_NR,
dbo.TPSVCREQ.DTN_FAC_LOC_NR = V.DTN_FAC_LOC_NR,
dbo.TPSVCREQ.PKG_SCH_DEL_DT = V.PKG_SCH_DEL_DT,
dbo.TPSVCREQ.PU_CRR_NA = V.PU_CRR_NA,
dbo.TPSVCREQ.DEL_CRR_NA = V.DEL_CRR_NA,
dbo.TPSVCREQ.ORG_ARA_NO_PU_IR = V.ORG_ARA_NO_PU_IR,
dbo.TPSVCREQ.DTN_ARA_NO_PU_IR = V.DTN_ARA_NO_PU_IR,
dbo.TPSVCREQ.HAZ_MAT_IR = V.HAZ_MAT_IR
from dbo.TPSVCREQ
inner join InfoLibOracleLinkedServer..O858IA.VSVCPAK V On dbo.TPSVCREQ.TrackingNum = V.PKG_TCK_NR
where dbo.TPSVCREQ.ResultsFlag = 0 OR (dbo.TPSVCREQ.ResultsFlag = 1 and dbo.TPSVCREQ.ResendFlag = 1)

 

[mrdenny]

Try creating a temp table, copying the data into this temp table (copy the smallest set of data over that you can), then index the temp table and then run the update off of the temp table instead of the linked server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[rodeomount]

Unfortunately I don't have a timstamp nor a primary key to keep up with which fields I have copied over.

 

[mrdenny]

Try creating a temp table on the Oracle server, which you can then push the keys to that you need to pull across, then query back using that table to filter the data which you process back.

How often does this process need to run? If it's once a day just pull over all the needed records each time the process runs.

How many records are we talking about in these tables?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog