Checking Huge Volume of records in Oracle DB .shellscript job times out

Question

I am working on a daily automated job which checks the records in a text file against oracle database. We receive the text file everyday from an external team which contains records around 100,000. The text file will be in unix format which has 6 columns seperated by | symbol.

eg, HDR 1

home/sample/file|testvalue1|testvalue2|testval3|testval4|testval5

TRL

I need to check whether the values in testval3 and testval5 exists in my table in oracle database. The table has around 10 million records. I am currently processing it through shellscript. Inside the shellscript, i am reading the text file and iterating through each line in a loop. Inside the loop I am passing the value from each line and running the query against DB. If the records don’t exist in DB, I have to output those to a csv file. The below query is used:

select ‘testval3’,’testval5’ from dual 
where not exists (select primarykeycolumn 
from mytable where mycolumn=testval3 and mycolumn2=testval5)

Since the input file has 100000 entries, my loop will run the query 100000 times and every time it will check the table with 10 million records. This is making my batch job to run for many hours and I have to terminate it. Is there a better way to handle this situation? I can also use java if there is no better way to do this via shellscript.

Answer 1

Below is one of the simple solution that will ensure that timeout will be not there and even you don't need to scan the millions of record for 100K times.

One time setup: Create a staging Temporary table:

create table a_staging_table(
testvalue1 varchar2(255),
testvalue2 varchar2(255),
testval3 varchar2(255),
testval4 varchar2(255),
testval5 varchar2(255)
);

----Recurring process

Load your "CSV/TEXT" data into staging table:

some_file_name.ctl: this file contains below load data command.

load data
INFILE 'home/sample/file.csv'
INTO TABLE a_staging_table
APPEND FIELDS TERMINATED BY '|'
(testvalue1,testvalue2,testval3,testval4,testval5);

Now, run the SQL loader to load your data into a staging table form .

sqlldr userid=dbUserName/dbUserPassword control=some_file_name.ctl log=some_file_name.log

Your data is loaded into staging tables. Now join the staging table and your_original_table to identify the record that does not exist.

First Way: Spool the output from the below SQL using SQL*PLUS:

select s.testval3,testval5
        from (select distinct testval3,testval5 
            from a_staging_table) s 
        where not exists
        (select 1
          from your_original_table
           where mycolumn1=s.testval3
             and mycolumn2=s.testval5);

Second Way:

Begin
for x in (
    select s.testval3,testval5
    from (select distinct testval3,testval5 
        from a_staging_table) s 
    where not exists
    (select 1
      from your_original_table
       where mycolumn1=s.testval3
         and mycolumn2=s.testval5)
    ) loop

    DBMS_OUTPUT.put_line('testval3: '||x.testval3 || '      ------     '||'testval5: '||x.testval5);
    --write all these values into another file saying that these are not matching values, using UTL_FILE.

--Then finally truncate the table "a_staging_table"
--so that this data will not available next time, and next time again process will run with different file
end loop;

Answer 2

A fast way to do this would be to gather all available testval3 and testval5 combinations from the table at the beginning of the script, store them in a hashtable or similar structure, so that when you read each line you'd easily query a local in memory data structure.

It will use more memory, for sure, but it will run a single validation query and speed the program up many times.

The query to run would be select distinct mycolumn,mycolumn2 from mytable or an equivalent.

See

• How do I (or can I) SELECT DISTINCT on multiple columns? and

• ORACLE Select Distinct return many columns and where

So, in summary, the mechanism I'm proposing is:

1. Run a query to select all the distinct pairs of testval3 and testval5 from the table

2. Create a hash table and a specific Pair structure that you store on it. For example, in Java you could use A Java collection of value pairs? (tuples?) and then, if the types of your columns are strings, use something like

HashMap<Pair<String,String>, boolean> pairMap

Make sure to implement the hashCode and equals methods as they are in the other example answer so you can use it as keys on the map properly (if using Java or similar)

3. Store the result of the query in your hash table, having the pairs of testval3 and testval5 as keys on the table and true as values (how to iterate over the resultset is left as an exercise for the reader):

pairMap.put(new Pair<String,String>(testval3,testval5),true)

4. Read the file line by line
5. Foreach line, find if a pair testval3 and testval5 exists on your hash table, if it doesn't then output the line to the CSV. To do that, you can simply query your map and check for null (Key existence check in HashMap)

For example:

 if (pairMap.get(new Pair<String,String>(testval3,testval5)) == null) {
 //output to CSV
 }

Finally, another option as @Kaushik Nayak is saying and @Vivek is implying in the comments, is to load the file onto Oracle with its data loading tools and then run a single query for the values that don't exist.