Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to Rebuild a Domain Index?

699346Feb 24 2010 — edited Feb 24 2010
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;

Comments

591186
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
699346
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
Lakmal Rajapakse
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');
699346
I guess this was too simple. I just issued the following: ALTER INDEX IW.IDXCLAIMSBINARY2 REBUILD
This is without any options.
Lakmal Rajapakse
You could do that ;) - I assumed you wanted to change the tablespace - my mistake...
699346
Is there a way to do this in a tirgger?
sb92075
Is there a way to do this in a tirgger?
TRIGGER?
Why?
Trigger based upon what action?
699346
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;
/
Lakmal Rajapakse
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.
699346
Thanks!!
Setting the parameter works greatly.

Thanks.
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 24 2010
Added on Feb 24 2010
10 comments
13,954 views