Exceed Resource Limit

[rhernand]

I am updating a 800k record Oracle table. My Update Query runs for a few hours, then dies with a Exceeded Resource Limit error. Can I built a Macro that will run maybe 10 update queries updating 100k records each. How can I limit each update query to 100k rcords and how do I start the next query with the 100k + 1 record?

 

[pbaldy]

I'd be curious about the process (or the SQL of the update query). I have no experience with Oracle, but quite a bit with SQL Server. I have tables with well over a million records, and I'd be peeved if an update query took several minutes to run, forget about several hours.

I wonder if your process is trying to transfer the whole table to the client. I assume Oracle has the equivelant of SQL Server's stored procedures, which would enable you to have the whole process run on the server? Have you tried that?

 

[rhernand]

Oracle

For some reason, even an APPEND query to Oracle takes hours, if it is over 250k records. I do not have access to run in the Oracle server, which is a Windows 2003 server. Therefore, I have a Link to the Oracle table thru an ODBC data source. The SQL is a simple Update:

UPDATE [New Addr] INNER JOIN EPE_TVDADDRESS ON ([New Addr].ADDR_STREET_NO = EPE_TVDADDRESS.ADDR_STREET_NO) AND ([New Addr].ADDR_STREET_NAME = EPE_TVDADDRESS.ADDR_STREET_NAME) AND ([New Addr].ADDR_CITY = EPE_TVDADDRESS.ADDR_CITY) AND ([New Addr].ADDR_STATE = EPE_TVDADDRESS.ADDR_STATE) AND ([New Addr].ADDR_ZIP_CODE = EPE_TVDADDRESS.ADDR_ZIP_CODE) SET EPE_TVDADDRESS.ADDR_ROUTE = [New Addr]!ACCT_ROUTE, EPE_TVDADDRESS.ADDR_FOLIO = [New Addr]!ACCT_FOLIO, EPE_TVDADDRESS.ADDR_TENANT = [New Addr]!ACCT_TENANT;

Maybe, I should try to get access to run on the Oracle server. This is a one time run, to update three new columns on the Oracle table. Or, even limiting my Update Query to do 100k records at a time, which I need help in doing.

 

[pbaldy]

If it's a one-time deal, I suppose working around the problem isn't too bad. I assume both those tables are in Oracle? Have you tried limiting the records updated with some sort of logical criteria, like states or zip code ranges?

 

[rhernand]

Thanks

Great idea, the join field is the CustomerName, I am just going to break it down by alphabetical limits, A,B, etc.

Thanks