Shell Programming and Scripting

Hi All,

I have a flat file like this on unix AIX server:

MXBOFO CSWP 5340 3794499 MXBOBIS CSWP 5340 3581124
MXBOFO CSWP 5340 3794531 MXBOBIS CSWP 5340 3583720
MXBOFO CSWP 5340 3794514 MXBOBIS CSWP 5340 3580763
MXBOFO CSWP 5340 3795578 MXBOBIS CSWP 5340 3794995
MXBOFO CSWP 5340 3710835 MXBOBIS CSWP 5340 3733963
MXBOFO CSWP 5340 3733963 MXBOBIS CSWP 5340 3710835
MXBOFO CSWP 5340 3749157 MXBOBIS CSWP 5340 3749208
MXBOFO CSWP 5340 3749208 MXBOBIS CSWP 5340 3749157
..........

Now i want to write a shell script which will check this rows (values) in a table in database (oracle) and if row exists it will delete the row else do nothing.

please help!!

what have you tried?

You don't have to check if a row exists, just delete it. Oracle will not raise an error if you delete a not existing row.

The query-then-delete approach amounts to the following:
When the row exists, doing a select is just overhead; when the row is not there, you do only a select but then the cost is about the same as doing straigth a delete.

You didn't specify what are the fields you want to use as a key into the table, so I am assuming that the table has a primary key column F4 to be matched with field 4.

Code:

while read f1 f2 f3 f4 f5 f6 f7 f8
do
  sqlplus -s /nolog << SQLEND
  var f4 varchar2
  connect ${user}/${password}@${servicename}
  exec :f4 := '$f4'
  delete TABLE where F4 = :f4;
  commit;
  quit
SQLEND
done

The above code is simple, but it has too much overhead because for every input line it forks a new sqlplus process, which in turn connects to the Oracle database instance and therefore another new process (Oracle server) is forked (unless the Oracle instance is configured as shared server).

Altough a bit more complex, the following approach is better:

Code:

{
echo "var f4 varchar2\nconnect ${user}/${password}@${servicename}"
while read f1 f2 f3 f4 f5 f6 f7 f8
do
  echo "exec :f4 := '$f4'\ndelete TABLE where F4 = :f4;"
done
echo "commit;\nquit"
} | sqlplus -s /nolog

I am using :f4 (bind variable) instead of '$f4' (literal value) inside the delete statement to allow the Oracle optimizer to do less parsing.
Read here for more explanations:
http://asktom.oracle.com/pls/asktom/...:2444907911913

Executing a delete statemet for each row to be deleted leaves no choice to Oracle besides separately locate each single row (hopefully using a suitable index). We are in fact emulating a join method named nested loop.
There is a break even point where the number of rows to delete is large enough (in respect to the number of rows in the table) that an hash join is more efficient. This point is about 1% of the number of rows in the table, more or less depending on the average size of the rows.

To give to the Oracle optimizer the option to use an hash join, we must execute a single delete statement for all the rows to be deleted. This means that the rows from the input file must be loaded into a table (say SOURCE), so that it can be joined to the target TABLE.
We better also tell the Oracle optimizer about the number of rows in both the tables so that it can choose the best join method. This can be accomplished by collecting statistics using a procedure in the system package DBMS_STATS.

Code:

{ echo "var f4 varchar2\nconnect ${user}/${password}@${servicename}"
  echo "create table SOURCE (ID varchar2(99));"
  while read f1 f2 f3 f4 f5 f6 f7 f8
  do
    echo "exec :f4 := '$f4'\ninsert into SOURCE (ID) values (:f4);"
  done
  echo "delete TABLE where F4 in (select ID from SOURCE);"
  echo "drop table SOURCE;\nquit"
} | sqlplus -s /nolog

The commit statement is implied by the drop statement.
There is no performance advantage in doing a commit after every insert (this is just a myth); the converse is true, as a matter of fact every commit forces Oracle to flush the redo log buffer.

If ID is the primary key for the table SOURCE then the delete statement can be formulated differently, and this form most of the times is executed by Oracle using a more efficient plan:

Code:

delete (
  select null -- any value would do
  from TABLE a, SOURCE b
  where a.F4 = b.ID);

If ID is not the primary key for the table SOURCE, Oracle will object to this statement with a rather obscure error message about "non key preserved table". Worse, since F4 is the primary key for TABLE, Oracle will assume that you want to delete from SOURCE instead! You better make sure to have a primary key on ID and to put table TABLE first in the from list. Caveat emptor.
Of course to provide the primary key on ID the create statement above has to be enhanced.
More on this subject: http://asktom.oracle.com/pls/asktom/...:5050838809412

Instead of loading the file into a normal table (heap table), we can create a sort of virtual table whose contents is taken on demand from the external file.
This kind of table is the external table. The SQL code is more involved and some administrative burden is required to make the thing possible, therefore I am omitting a code sample.