What is the best way to search in a CLOB, in batch operation ?

Hi All,
My application runs batch procedure for searching 'text' into clobs (column notes in the below query) and accordingly inserts into another table-
code snippet is like this -

FORALL i IN 1 .. l_snme_sn_temp.COUNT
INSERT INTO snme_rmanotes_out
(report_request_id, serial_number, scrubbed_sn, product_id, pk1_value, last_update_date, event_date, event)
SELECT pi_report_request_id
, l_snme_sn_temp_orig (i).
, l_snme_sn_temp (i)..
, '' product_id
, pk1_value
, TO_CHAR (last_update_date, 'DD-MON-YYYY') last_update_date
, TO_CHAR (last_update_date, 'DD-MON-YYYY') event_date
, 'IN RMA NOTES' event
FROM c3_rma_notes
WHERE contains (notes, l_snme_sn_temp (i)) > 0; **

here - l_snme_sn_temp contains the text that the query searches ( also evident from the where clause, marked **), and is a variable of a TYPE.
I am populating l_snme_sn_temp in a chunk of 500 ( using bulk collect limit 500) from a table having at least 1 million records (and the upper cap will not be more than 1.5 M).
The procedure that does this, takes 24+ hours to complete.
If you guys have any better way to do this or any idea to tune to reduce time kindly suggest.
I'd appreciate your kind response.
regards,
Raj Pandit

Try rebuilding you context index. Context indexes require updating on a regular basis.

tried, but of no avail.
even parallel execution (HINT) no helping.

Here is a shell script that I have used to optimize Text based indexes. If you just look by clob based indexes the results will be more expensive than if you look by some other column and then by the text based index.

Code:

---

#!/bin/bash
# set -x
# Set the ORACLE_SID with the first parameter.
if [ $# -lt 2 ]; then
echo "usage: sid name, schema name"
exit 1
else
SID=$1
fi
. /oracle/bin/set_oracle_env.sh ${SID}
export USER=$2
# Log into the DB through SQLPLUS.  You must be the oracle user.
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' << EOF
SET TERM ON
SET FEEDBACK OFF
SPOOL /tmp/ctx_indexes_${ORACLE_SID}_${USER}.sql
DECLARE
cmdLine VARCHAR2(256);
CURSOR ctx_indexes IS
SELECT idx_owner, idx_name
FROM ctxsys.ctx_indexes
WHERE idx_owner=UPPER('${USER}');
BEGIN
FOR r IN ctx_indexes
LOOP
cmdLine := 'exec ctxsys.ctx_ddl.optimize_index('''||r.idx_owner||'.'||r.idx_name||''', ''FAST'');';
DBMS_OUTPUT.PUT_LINE(cmdLine);
cmdLine := 'exec ctxsys.ctx_ddl.sync_index    ('''||r.idx_owner||'.'||r.idx_name||''', ''8M'');';
DBMS_OUTPUT.PUT_LINE(cmdLine);
END LOOP;
END;
/
SPOOL OFF;
SET ECHO ON
SET TIMING ON
SET FEEDBACK ON
SET SERVEROUTPUT ON
@/tmp/ctx_indexes_${ORACLE_SID}_${USER}.sql
exit
EOF

---