What is the best way to search in a CLOB, in batch operation ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

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

  1. #1
    Join Date
    May 2012
    Posts
    2

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Try rebuilding you context index. Context indexes require updating on a regular basis.
    this space intentionally left blank

  3. #3
    Join Date
    May 2012
    Posts
    2
    tried, but of no avail.
    even parallel execution (HINT) no helping.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002

    Cool

    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
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width