We had a user who needed to run an adhoc report for 24k account numbers. The structure of the account table was as follows:
COL_A NUMBER(3,0) DEFAULT ON NULL 0 NOT NULL ENABLE
COL_B NUMBER(3,0) DEFAULT ON NULL 0 NOT NULL ENABLE
COL_C NUMBER(4,0) DEFAULT ON NULL 0 NOT NULL ENABLE
About 100 other columns
The table had 60 million rows and occupied about 34 gigabytes of space.
There was an index on COL_A, COL_B, COL_C plus other trailing columns.
The list of account numbers was provided as
0020510043
1024100289
And so on
A new list of account numbers may be provided every day or multiple times a day and hence the process needed to be repeatable. The first attempt was to loop through this list and execute SQL statement such as:
select col_01, col_02, col_03, col_04, col_05, col_06, col_07 from tbowner.tbname where col_a = 002 and col_b = 051 and col_c = 0043 ; select col_01, col_02, col_03, col_04, col_05, col_06, col_07 from tbowner.tbname where col_a = 102 and col_b = 410 and col_c = 0289 ;
and so on 24k times. The run time for this execution was about 300 minutes.
I changed the code to use bind variables as shown below and the execution time dropped to 22 minutes.
#!/bin/ksh PATH=$PATH:/oracle_home/12.1.0.2 export ORACLE_SID=PROD4 function run_the_sql { #echo 'run_the_sql - start' `date` /oracle_home/12.1.0.2/bin/sqlplus -s / as sysdba << EOF >> report.txt set linesize 150 set heading off set pagesize 50000; set echo off; set feedback off; var col_a_bind number; var col_b_bind number; var col_c_bind number; exec :col_a_bind :=${col_a_os}; exec :col_b_bind :=${col_b_os}; exec :col_c_bind :=${col_c_os}; select col_01, col_02, col_03, col_04, col_05, col_06, col_07 from tbowner.tbname where col_a = :col_a_bind and col_b = :col_b_bind and col_c = :col_c_bind ; exit EOF #echo 'run_the_sql - end ' `date` } # ## Main processing # clear echo 'start : ' `date` `rm report.txt` temp_ctr=0 while IFS=',' read -r -u3 acct_num do col_a_os=`expr substr $acct_num 1 3` col_b_os=`expr substr $acct_num 4 3` col_c_os=`expr substr $acct_num 7 4` run_the_sql temp_ctr=temp_ctr+1 if [temp_ctr -gt 1000 ]; then print '1000 done' `date` temp_ctr=0 fi done 3< list_of_tf.txt echo 'end : ' `date`