Forum Stats

  • 3,815,831 Users
  • 2,259,097 Discussions
  • 7,893,270 Comments

Discussions

Categories

How to Rebuild a Domain Index?

699346
699346 Member Posts: 72
edited Feb 24, 2010 3:46PM in General Database Discussions
Using Oracle 10g RAC +ASM

I've created a table with a domain index (TEXT). Is it possible to rebuild the index without dropping the index then recreating?
Attempts to REBUILD the index results in:

ALTER INDEX IW.IDXCLAIMSBINARY2 REBUILD NOCOMPRESS NOPARALLEL NOLOGGING
Error at line 1
ORA-29871: invalid alter option for a domain index


The table (in part):
CREATE TABLE CLAIM_SOURCE_REF
(
CLAIM_SOURCE_REF_ID CHAR(18 BYTE) NOT NULL,
CLAIM_SOURCE_REF_CODE VARCHAR2(4 BYTE),
CLAIM_SOURCE_REF_PR_DESC VARCHAR2(250 BYTE),
CLAIM_SOURCE_REF_DOC_REF VARCHAR2(250 BYTE),
CLAIM_SOURCE_REF_PATH VARCHAR2(250 BYTE),
CLAIM_SOURCE_REF_STUDY VARCHAR2(100 BYTE),
CLAIM_SOURCE_LAB_REF VARCHAR2(100 BYTE),
CLAIM_PUBLISHER_ID_FK CHAR(18 BYTE),
CLAIM_SOURCE_REF_YEAR VARCHAR2(4 BYTE),
TEXT BLOB,
TEXT_LOADED CHAR(1 BYTE),
FILE_NAME_UNC VARCHAR2(255 BYTE)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX IDXCLAIMSBINARY2 ON CLAIM_SOURCE_REF
(TEXT)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer pdf_lexer')
NOPARALLEL;
Tagged:
Yogendra Pahariya
· Share on TwitterShare on Facebook

Answers

  • 591186
    591186 Member Posts: 3,668 Silver Trophy
    Is it possible to rebuild the index without dropping the index then recreating?
    Rebuild is possible. But,You can't specify NOLOGGING.

    Try with,

    ALTER INDEX IW.IDXCLAIMSBINARY2 REBUILD PARAMETERS ('TABLESPACE=NEW_TBS');


    HTH
    -Anantha
    · Share on TwitterShare on Facebook
  • 699346
    699346 Member Posts: 72
    I tried without the NOLOGGING option, with the same results.
    I tried your method, the following resulted:

    ALTER INDEX IW.IDXCLAIMSBINARY2 REBUILD PARAMETERS ('TABLESPACE=INFOWH')
    Error at line 3
    ORA-29874: warning in the execution of ODCIINDEXALTER routine
    ORA-29960: line 1,
    DRG-10595: ALTER INDEX IDXCLAIMSBINARY2 failed
    DRG-11000: invalid keyword TABLESPACE=INFOWH
    · Share on TwitterShare on Facebook
  • Lakmal Rajapakse
    Lakmal Rajapakse Member Posts: 827 Silver Badge
    you need to create a preference first:
    begin
ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE', 'tablespace ...'); 
ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE','tablespace ...'); 
ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE','tablespace ...'); 
ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE','tablespace ...'); 
ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE','tablespace ...'); 
end;
/

alter index ... rebuild parameters ('storage mystore');
    · Share on TwitterShare on Facebook
  • 699346
    699346 Member Posts: 72
    I guess this was too simple. I just issued the following: ALTER INDEX IW.IDXCLAIMSBINARY2 REBUILD
    This is without any options.
    · Share on TwitterShare on Facebook
  • Lakmal Rajapakse
    Lakmal Rajapakse Member Posts: 827 Silver Badge
    You could do that ;) - I assumed you wanted to change the tablespace - my mistake...
    · Share on TwitterShare on Facebook
  • 699346
    699346 Member Posts: 72
    Is there a way to do this in a tirgger?
    · Share on TwitterShare on Facebook
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    Is there a way to do this in a tirgger?
    TRIGGER?
    Why?
    Trigger based upon what action?
    · Share on TwitterShare on Facebook
  • 699346
    699346 Member Posts: 72
    Here is the trigger that is design to load the document text into a blob.
    I need to then rebuild index the domain index on the blob (TEXT). The following results in a error message during
    the ALTER INDEX ... REBUILD :

    - TRIGGER CLAIM SOURCE REF [LOG] AFTER INSERT FOR EACH ROW
    CREATE OR REPLACE TRIGGER TRG_CLAIM_SOURCE_REF_LOG_AI
    AFTER INSERT ON CLAIM_SOURCE_REF_LOG FOR EACH ROW
    DECLARE
    vStr VARCHAR2(1000) := '';
    DEST_LOC BLOB;
    SRC_LOC BFILE := BFILENAME('CLAIMS_DIR', 'FAQ- NOROVIRUS.doc');
    vClaimKey_ID CLAIM_SOURCE_REF.CLAIM_SOURCE_REF_ID%TYPE;
    vFileName CLAIM_SOURCE_REF.FILE_NAME_UNC%TYPE;


    BEGIN
    vClaimKey_ID := :NEW.CLAIM_SOURCE_REF_ID_FK;
    -- UPDATE FILE NAME
    UPDATE IW.CLAIM_SOURCE_REF SET CLAIM_SOURCE_REF.FILE_NAME_UNC = REPLACE(CLAIM_SOURCE_REF.FILE_NAME_UNC,'\', '/')
    WHERE CLAIM_SOURCE_REF_ID = vClaimKey_ID;

    SELECT FILE_NAME_UNC INTO vFileName FROM IW.CLAIM_SOURCE_REF WHERE CLAIM_SOURCE_REF_ID = :NEW.CLAIM_SOURCE_REF_ID_FK;
    UPDATE IW.CLAIM_SOURCE_REF SET text = EMPTY_BLOB() WHERE CLAIM_SOURCE_REF_ID = :NEW.CLAIM_SOURCE_REF_ID_FK;
    DBMS_OUTPUT.PUT_LINE('- > ' || vClaimKey_ID || ' ->' || vFileName);

    -- LOAD TEXT
    SRC_LOC := BFILENAME('CLAIMS_DIR', vFileName);

    IF DBMS_LOB.FILEEXISTS (SRC_LOC) = 1 THEN
    SELECT text INTO DEST_LOC FROM CLAIM_SOURCE_REF
    WHERE CLAIM_SOURCE_REF_ID = vClaimKey_ID FOR UPDATE;
    DBMS_LOB.OPEN(SRC_LOC, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LOADFROMFILE(DEST_LOC, SRC_LOC, dbms_lob.getlength(SRC_LOC));
    DBMS_LOB.CLOSE(SRC_LOC);
    UPDATE IW.CLAIM_SOURCE_REF SET TEXT_LOADED = 'Y' WHERE CLAIM_SOURCE_REF_ID = vClaimKey_ID;
    END IF;

    -- REBUILD THE INDEX
    vStr := 'ALTER INDEX IW.IDXCLAIMSBINARY2 REBUILD ';
    EXECUTE IMMEDIATE (vStr);

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
    DBMS_OUTPUT.PUT_LINE('Possible error while trying to load: ' || vFileName);
    RAISE_APPLICATION_ERROR (-20001, 'ERROR OCCURED');
    END TRG_CLAIM_SOURCE_REF_LOG_AI;
    /
    · Share on TwitterShare on Facebook
  • Lakmal Rajapakse
    Lakmal Rajapakse Member Posts: 827 Silver Badge
    This is absolutely the wrong way of doing it. If you want to synchronize an index on commit then you can specifiy it at the time you create the index. 
    create index
...
...
parameters ('lexer my_lexer sync (on commit)');
    Note this option is only available from 10g and above.

    Or else you could opt to synhronizes the index periodically (again in 10g):
    create index
...
...
parameters ('lexer my_lexer sync (every ...)');
    In versions older than 10g you could opt to synhronizes the index periodically via a dbms_job by calling the ctx_ddl.sync_index.
    Lakmal RajapakseYogendra Pahariya
    · Share on TwitterShare on Facebook
  • 699346
    699346 Member Posts: 72
    Thanks!!
    Setting the parameter works greatly.

    Thanks.
    · Share on TwitterShare on Facebook
This discussion has been closed.