Terry的Blog

------ Test Oracle text
CREATE TABLE hdocs (
ID NUMBER PRIMARY KEY,
fmt VARCHAR2(10),
text VARCHAR2(80)
);

CREATE INDEX hdocsx ON hdocs(text) INDEXTYPE IS ctxsys.CONTEXT
PARAMETERS ('datastore ctxsys.file_datastore
 filter ctxsys.inso_filter
 format column fmt
');

--  charset column cset // can set charset

INSERT INTO hdocs VALUES(1, 'binary', 'D:\OracleText\Oracle.pdf');
INSERT INTO hdocs VALUES(2, 'text', 'D:\OracleText\1.txt');
INSERT INTO hdocs VALUES(3, 'binary', 'D:\OracleText\mydoc.doc');
COMMIT;

select t.*, score(1) from hdocs t WHERE contains(text,'索引タイプの構文', 1) > 0

-- 索引の同期化
BEGIN
  ctx_ddl.sync_index('hdocsx', '2M');
END;
/

-- sync
SET SERVEROUTPUT ON
DECLARE
  job NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(job, 'ctx_ddl.sync_index(''hdocsx'');', SYSDATE, 'SYSDATE + (3/1440)');
  DBMS_OUTPUT.PUT_LINE('job '||job||' has been submitted.');
END;
/

-- optimizer
DECLARE
  VARIABLE jobno NUMBER; 
BEGIN 
  DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''hdocsx'',''FULL'');', SYSDATE, 'SYSDATE + 1'); 
 
  COMMIT; 
END;
/

create or replace procedure syncidx
is
begin
execute immediate
'alter index hdocsx rebuild online' ||
' parameters ( ''sync'' )' ;
execute immediate
'alter index hdocsx rebuild online' ||
' parameters ( ''optimize full maxtime unlimited'' )' ;
end syncidx;
/

SET SERVEROUTPUT ON
DECLARE
  job NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(job, 'syncidx;', SYSDATE, 'SYSDATE + (3/1440)');
  DBMS_JOB.RUN(job);
  DBMS_OUTPUT.PUT_LINE('job '||job||' has been submitted.');
END;
/