连接到:
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
