Re: What's FASTEST way to IMPORT Massive amounts of TRANSACTIONAL DATA? (Non-SQL*Loader)

Papaya Paradise wrote:
>
> SQL*Loader is fastest for raw pumping of data straight to tables but we have
> to do queries as we import to see if an Entity is already in the database.
<snip>
> My question is this already highly efficient? Should we...
>
> a - Use direct method sql*loader to load into a temp table then do queries and
> insert/update into the 2 main tables? Would that save us much time since we
> still have to do queries and insert after data pumped into temp table?
<snip>
> I really don't know if these alternatives will improve performance as a whole
> that much.
>

Without knowing your existing data volumes, the disk space you have available, your machine architecture or the current data availability requirements it's a bit difficult to advise.

However, if you have the space to do it, can get any degree of parallelism on your hardware, and don't mind taking the data offline for a while to switch tables around, I'd personally go for option a), i.e

1. Direct load the data into one or more a staging tables using SQL*Loader.
2. Use SQL (avoid PL/SQL if at all possible), particularly CREATE TABLE AS SELECT, to merge the two sets of data into the result you want. Depending on the complexity of your insert/update logic this may get a bit hairy, but should be doable. Don't be afraid to use intermediate tables to get the results you want. 3) When you get the data you want, switch the new table with the current one
3. Use parallelism and no logging as much as possible on step 2. It's all scratch work and doesn't need to be recovered.

This will be (read should) MUCH, MUCH faster than your current approach.

Roll on UPSERT/UPLETE !

Cross posts deleted from follow up.
--
Regards,

Mark Townsend                         

Senior Product Manager				Ph: 	(650) 633 5764
Server Division					Fx: 	(650) 506 7222
Oracle Corporation				Email:	mtownsen_at_us.oracle.com