Problem with Missing Data Procedure

From: Bryan Guilliams <bguilliams_at_commonvision.com>
Date: Mon, 6 Dec 1999 12:33:48 -0600
Message-ID: <yiT24.1020$y3.32357@insync>

Oracle gurus,

I am writing a stored procedure who's job is to find missing data from a table. In order to this, I populate a staging table with one row for every value that SHOULD be there and mark a 'DATA_EXISTS' field to '0'.

Next, I create a cursor that runs through the real table and pulls out all the proper data. Then, for every record in the cursor that matches a row in the staging table, I change the 'DATA_EXISTS' field value to '1'.

Finally, I can do a select statement to pull out all the '0s' and know which data is not in the table.

Unfortunately, this is very slow and tedious as the table involved has tens of thousands of rows. My little brain coupled with my lack of Oracle experience is preventing me from coming up with a better process. Obviously, I'm not breaking any new ground with this type of procedure....Does anyone out there have any hints or suggestions as to how I can speed this up?

Thanks in advance for any help you can give me.

B.
--

                         Bryan Guilliams
                 Consultant - CommonVision
                  (713) 785-8300 ext. 1816

bguilliams_at_commonvision.com - Go 'Pods! Ex Ignorantia ad Sapientiam, e Luce ad Tenebras

                Undefeated in the Octagon!


_________________________________________

Received on Mon Dec 06 1999 - 12:33:48 CST