全文检索 Oracle Text, data load by partitation, async refresh index

zhu1

6538.#### x ###

玉面飞龙

连接到:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> create table part_tab(a int,b varchar2(4000)) partition by range(a) (partit
ion p_tab1 values less than (10),partition p_tab2 values less than (20),partitio
n p_tab3 values less than (30));

表已创建。

SQL> Execute ctx_ddl.drop_preference('mystore1');
BEGIN ctx_ddl.drop_preference('mystore1'); END;

*
ERROR 位于第 1 行:
ORA-20000: Oracle Text 错误:
DRG-10700: 首选项不存在: mystore1
ORA-06512: 在"CTXSYS.DRUE", line 157
ORA-06512: 在"CTXSYS.CTX_DDL", line 80
ORA-06512: 在line 1


SQL> Begin
  2  ctx_ddl.create_preference('mystore1', 'BASIC_STORAGE');
  3  ctx_ddl.set_attribute('mystore1', 'I_TABLE_CLAUSE',
  4                          'tablespace zhangbin8k ');
  5   ctx_ddl.set_attribute('mystore1', 'I_INDEX_CLAUSE',
  6                          'tablespace zhangbin8k compress 2 ');
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> CREATE INDEX part_idx on part_tab(b) INDEXTYPE IS CTXSYS.CONTEXT
  2  LOCAL (partition p_idx1 parameters('storage mystore1'), partition p_idx2 pa
rameters('storage mystore1'), partition p_idx3 parameters('storage mystore1'));

索引已创建。

SQL> set autotrace trace stat exp
SQL> alter table part_tab nologging;

表已更改。

SQL> insert /*+ append */into select 1,title from app_Assetbasicinfo where categ
oryid=1 and rownum<10000;
insert /*+ append */into select 1,title from app_Assetbasicinfo where categoryid
=1 and rownum<10000
                         *
ERROR 位于第 1 行:
ORA-00903: 表名无效


SQL> insert /*+ append */into part_tab select 1,title from app_Assetbasicinfo wh
ere categoryid=1 and rownum<10000;

已创建9999行。

Statistics
----------------------------------------------------------
      11634  recursive calls
      22320  db block gets
       1063  consistent gets
        259  physical reads
    3654276  redo size
        619  bytes sent via SQL*Net to client
        611  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         25  sorts (memory)
          0  sorts (disk)
       9999  rows processed

SQL> execute ctx_ddl.sync_index('part_idx','50M','p_idx1');


看来load慢的原因是因为索引存在的情况下nologging表append 插入的时候产生了过多的redo

SQL> ALTER INDEX part_idx modify partition p_idx3 unusable;

索引已更改。

SQL> insert/*+ append */ into part_tab select 23,title from app_Assetbasicinfo w
here categoryid=1 and rownum<10000;
insert/*+ append */ into part_tab select 23,title from app_Assetbasicinfo where
categoryid=1 and rownum<10000
*
ERROR 位于第 1 行:
ORA-29954: 域索引分区被标记为 LOADING/FAILED/UNUSABLE

SQL> alter session set SKIP_UNUSABLE_INDEXES =true;

会话已更改。

SQL> insert into  part_tab values(23,'abc');
insert into  part_tab values(23,'abc')
*
ERROR 位于第 1 行:
ORA-29954: 域索引分区被标记为 LOADING/FAILED/UNUSABLE


http://download-west.oracle.com/ ... d_pclxut.htm#996816

d.c.b.a

不要起ctxsrv就不会自动同步了。

zhu1

10g  怎么停下 ctxsrv.

zhu1

1. load into temp table
2. sync text index
3. exchange temp table into the partition table