DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: syncronizing CLOB indexes

  1. #1
    Join Date
    Feb 2002
    Posts
    5
    We are running Oracle 9i and we have an application requirement to syncronize a CLOB index so that, as soon as a user inserts a new row that contains a CLOB column, they can search the CLOB column using the CONTAINS clause.

    My solution is to create a AFTER trigger on the table for INSERT and MODIFY that will execute a job to sync the index so that users can perform searches.

    I setup the jobs as follows

    declare
    jobno number;
    begin
    dbms_job.submit(jobno, 'ctx_ddl.sync_index(''SA.PRD_PRD_EVAL_IX'');', sysdate, NULL, TRUE);
    end;

    After executing this command the job is submitted without error, but when I attempt to execute the job using the
    declare job_exist number;

    DBMS_JOB.RUN(jobno);

    I get the following error msg.

    The following error has occurred:

    ORA-12011: execution of 1 jobs failed
    ORA-06512: at "SYS.DBMS_IJOB", line 406
    ORA-06512: at "SYS.DBMS_JOB", line 270
    ORA-06512: at line 8

    I can run the SQL successfully via SQLPlus using

    BEGIN
    ctx_ddl.sync_index('SA.PRD_PRD_EVAL_IX');
    END;

    What am I doing wrong, and is there an easier solution to syncronize the COLB indexes for searches on newly entered rows.

    Thanks

    Fred

  2. #2
    Join Date
    Feb 2002
    Posts
    5

    Thumbs up

    I have resolved the problem. Thanks

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    What are you using Oracle TEXT for. I mean what sort of data are you storing in the CLOBS? Just out of interest sake.
    OCP 8i, 9i DBA
    Brisbane Australia

  4. #4
    Join Date
    Feb 2002
    Posts
    5
    We used the Migration workbench to migrate a schema from SQL Server to Oracle 9i. The Text datatype columns in SQL server were all converted to CLOB columns. We have text data in excess of 4000 bytes and an applications requirement to provide search capabilities after new rows are inserted.
    I have these trigger processes aforementioned working now and there is an understanding that latency will increase as these tables grow exceedingly.

    I do have a few other problems maybe you can assist me.

    How do you ALTER the PCTincrease on a CLOB index? It is currently set at 50% which is much to high and I suspect additional overhead allocating extents. I think I need to reset this to 0

    We also have discovered that some complex queries run thru TOAD execute in 19 seconds, but running the same queries thru the web server via I-planet, either timeout or execute in excess of 2 minutes and 30 sec. I suspect it is a JDBC thin client problem. I have already optimized all of the queries using Oracle SQL Analyze utility. Do you have any suggestions

    thanks

    Fred

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