oracle10g创建数据表的索引

以下sql语句中，以fx作为oracle的用户进行操作的，请复制代码的同学进行修改。

一、设置词法分析器

BEGIN
  ctx_ddl.create_preference ('fx_lexer', 'chinese_vgram_lexer');
END;
/

二、针对数据表的特定字段建索引

CREATE INDEX  I_DM_FILE ON DM_FILE (FILEBODY) indextype is ctxsys.context 
parameters('lexer fx_lexer ');

三、创建存储过程同步更新与优化索引

prompt
prompt Creating procedure OPTIMIZE_I_DM_FILE
prompt =====================================
prompt
CREATE OR REPLACE PROCEDURE FX.optimize_i_dm_file
as

begin
execute immediate 'begin ctx_ddl.optimize_index(''i_dm_file'',''FULL'');end;';

END optimize_i_dm_file;
/

prompt
prompt Creating procedure SYNC_I_DM_FILE
prompt =================================
prompt
CREATE OR REPLACE PROCEDURE FX.sync_i_dm_file
as

begin
begin
execute immediate 'begin ctx_ddl.sync_index(''i_dm_file'',''2M'');end;';
end;

END sync_i_dm_file;
/

四、新建作业，定时执行上面创建的存储过程

begin
  sys.dbms_job.submit(job => :job,
                      what => 'SYNC_I_DM_FILE;',
                      next_date => to_date('22-04-2009 17:17:39', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'SYSDATE + (1/24/4)');
  commit;
end;
/
begin
  sys.dbms_job.submit(job => :job,
                      what => 'optimize_i_dm_file;',
                      next_date => to_date('22-04-2009 17:20:13', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'SYSDATE+1');
  commit;
end;
/

五、赋予作业运行的权限

grant execute any procedure to fx;

六、测试
在pl/sql developer的Job上点右键-->run