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




Reply With Quote