Re: Uploading data for star schema

I have used the PL/SQL procedure approach before and it worked well. In my case, it was called within a C program that was doing the mapping.

Depending upon how many of your rows are new, how many are changes to existing dimensional rows and how many are duplicates, an alternate approach may be useful. Where there are high numberes of new and duplicate rows, first run everything through a C program to check what kind of row you are processing. If it is a duplicate row, skip it; if it is an update, do the update; if it is a new row, add it to a data file for SQL*Loader to work on later. After the process is done, do a direct path load on the SQL*Loader data file you built.

HTH Barry

In article <0soX5.115$VM5.135443456_at_news.telia.no>,   "Randi Wølner" <rw_at_computas.no> wrote:
> Hello,
> I have a situation that seems to be a bit the same.
> What I do is that I create a large table, and use SQL*Loader with the
 Direct
> Path Option (makes the load run very fast!) to load the whole file
 into this
> table. Then I have packages of PL/SQL stored procedures that I run to
> perform all the logic. These procedures loops through the large table
 with
> cursors (I process 3000 rows in every cursor), and perform the
 inserts and
> updates to the 'real' tables.
>
> This is the best way I have found so far, maybe you can use some of
 it.
>
> Best regards,
> Randi Wølner
>
> <sw_at_weinerfamily.org> wrote in message
> news:3A2DCAEF.81A8587B_at_weinerfamily.org...
> > Hi,
> >
> > This won't work for several reasons. For clarification:
> > 1) I will be uploading several million rows and Access would explode
> > 2) This process will be done regularly and some of the data will
 already
 be
> > in the database so if the record is already there I need to get the
 id.
 If
> > the records isn't there I need to pull the next valid id from a
 sequence
 in
> > Oracle.
> > 3) That process is not automated (realized I didn't specify this)
 and this
> > will be done weekly in an automated fashion
> > 4) This process will be far slower performance wise than what I am
 already
> > doing with VB.
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 06 2000 - 07:38:41 CST