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`