I had this script to write to correct some data. The script was going to affect about 20k records. One part of the script needed to mine through a table with 1B records. This needed to be done for each of the affected 20k records. So I knew that a brute force approach would never complete.So I thought perhaps I could preprocess those 1B records. There were only a few thousand of those records that were of interest. If I could just load the couple thousand relevant records into memory, I could use that data to go though my batch.
This seemed to be a good candidate for some type of collection. But what kind should I use? I don't do enough PL/SQL to be very comfortable with collections. I read around and though maybe it could be a VARRAY. But I only had experience with PL/SQL tables. What is a developer to do?
In the end I needed to get this script coded ASAP. So I did the simplest programmatic approach. I created a temporary table with a few thousand records. This table represented the important data mined from the 1B record raw table. I am hoping that SQL against this small temporary table will be very fast. We shall see when I ship the fix out to the production environment where the volumes are huge.
