Text Index * Useful queries for text indexes: --Find text indexes: select * from dba_indexes where index_type = 'DOMAIN'; --Pay attention to idx_status, should be "INDEXED": col idx_owner for a30 col idx_name for a30 col idx_table_owner for a30 col idx_table for a30 select idx_owner, idx_name, idx_table_owner, idx_table, idx_status from ctxsys.ctx_indexes order by 1,2,3; --What indexes need to be sync'ed? select pnd_index_owner, pnd_index_name, count(*), to_char(min(pnd_timestamp), 'yyyymmdd hh24:mi:ss') from ctxsys.ctx_pending group by pnd_index_owner, pnd_index_name order by 1, 2; * The word that's being indexed is in dr$token, not dr$token_info. So, the correct query would be (I added an ORDER BY here, to make the result easier to read) SELECT DR$TOKEN, COUNT(*) FROM DR$MYTABLE_I_CTX$I WHERE ROWNUM < 1000000 GROUP BY DR$TOKEN HAVING COUNT(*) > 1000 order by 2; * Why can't you search for 'a' in text index? Because 'a' is a stop word? SQL> select first_name, last_name, concat_name 2 from appuser.people 3 where contains(concat_name, 'a', 1) > 0; no rows selected SQL> select * from ctxsys.ctx_index_values where IXV_INDEX_OWNER = 'APPUSER' and IXV_ATTRIBUTE = 'STOP_WORD' and IXV_VALUE = 'a'; IXV_INDEX_OWNER IXV_INDEX_NAME IXV_CLASS IXV_OBJECT IXV_ATTRIBUTE ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------- IXV_VALUE ----------------------------------------------------------------------------------------------------------------------------------------- APPUSER CONCAT_NAME_CONTEXT_I STOPLIST BASIC_STOPLIST STOP_WORD a * Simple test SQL> create table testctx as select rownum id, object_name name from user_objects; Table created. SQL> select count(*) from testctx; COUNT(*) ---------- 41 SQL> create index indctx_testctx on testctx (name) indextype is ctxsys.context; Index created. SQL> select table_name from user_tables where table_name like 'DR$%'; TABLE_NAME ------------------------------ DR$INDCTX_TESTCTX$I <-- token table (see Note:139979.1) DR$INDCTX_TESTCTX$K <-- docid mapping table DR$INDCTX_TESTCTX$N <-- negative row table DR$INDCTX_TESTCTX$R <-- rowid mapping table Note: The above four tables are also visible in xxx_secondary_objects where index_name='INDCTX_TESTCTX'. SQL> select count(*) from DR$INDCTX_TESTCTX$I; COUNT(*) ---------- 51 SQL> select count(*) from DR$INDCTX_TESTCTX$K; COUNT(*) ---------- 41 SQL> select count(*) from DR$INDCTX_TESTCTX$N; COUNT(*) ---------- 0 SQL> select count(*) from DR$INDCTX_TESTCTX$R; COUNT(*) ---------- 22 SQL> insert into testctx values (1000, 'Yong'); 1 row created. SQL> commit; Commit complete. SQL> select count(*) from DR$INDCTX_TESTCTX$I; COUNT(*) ---------- 51 SQL> select count(*) from DR$INDCTX_TESTCTX$K; COUNT(*) ---------- 41 SQL> select count(*) from DR$INDCTX_TESTCTX$N; COUNT(*) ---------- 0 SQL> select count(*) from DR$INDCTX_TESTCTX$R; COUNT(*) ---------- 22 SQL> select * from testctx where contains(name, 'yong') > 0; no rows selected SQL> select * from testctx where contains(name, 'Yong') > 0; no rows selected SQL> select * from testctx where contains(name, 't') > 0; ID NAME ---------- -------------------------------------------- 24 T 29 T_TESTAUDIT SQL> alter index indctx_testctx rebuild; <-- should really use ctx_ddl.sync_index instead Index altered. SQL> select * from testctx where contains(name, 'yong') > 0; ID NAME ---------- --------------------------------------------- 1000 Yong SQL> select count(*) from DR$INDCTX_TESTCTX$I; COUNT(*) ---------- 52 SQL> select count(*) from DR$INDCTX_TESTCTX$K; COUNT(*) ---------- 42 SQL> select count(*) from DR$INDCTX_TESTCTX$N; COUNT(*) ---------- 0 SQL> select count(*) from DR$INDCTX_TESTCTX$R; COUNT(*) ---------- 22 SQL> insert into testctx values (2000, 'YongHuang'); 1 row created. SQL> select * from testctx where contains(name, 'yonghuang') > 0; no rows selected SQL> exec ctx_ddl.sync_index('indctx_testctx') <-- 'owner_name.index_name' if you're not the owner PL/SQL procedure successfully completed. SQL> select * from testctx where contains(name, 'yonghuang') > 0; ID NAME ---------- -------------------------- 2000 YongHuang SQL> delete from testctx where id = 2000; 1 row deleted. SQL> select * from testctx where contains(name, 'yonghuang') > 0; no rows selected Only insert should be followed by sync_index; delete and update don't need it. According to Note:139979.1, we should not gather stats for dr$ tables and indexes or performance suffers. Stats on text indexes (indctx_testctx here) can be gathered. SQL> create index indctx_testctx2 on testctx (name) indextype is ctxsys.ctxcat; <-- this index creates procedure and trigger Index created. SQL> select * from dba_objects where created > sysdate - 1/240 order by created; OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S ---------- ------------------------------ ---------- ---------- -------------- ------------------ ----------------- ----------------- ------------------- ------- - - - YONG INDCTX_TESTCTX2 30991 INDEX 20070611 11:42:25 20070611 11:42:26 2007-06-11:11:42:25 VALID N N N YONG DR$INDCTX_TESTCTX2$I 30992 30992 TABLE 20070611 11:42:25 20070611 11:42:25 2007-06-11:11:42:25 VALID N N Y YONG DR$INDCTX_TESTCTX2$X 30993 30993 INDEX 20070611 11:42:25 20070611 11:42:25 2007-06-11:11:42:25 VALID N N Y YONG DR$INDCTX_TESTCTX2$R 30994 30994 INDEX 20070611 11:42:25 20070611 11:42:25 2007-06-11:11:42:25 VALID N N Y CTXSYS DR$1062$U 30995 PROCEDURE 20070611 11:42:25 20070611 11:42:26 2007-06-11:11:42:25 VALID N N Y YONG DR$INDCTX_TESTCTX2TC 30996 TRIGGER 20070611 11:42:26 20070611 11:42:26 2007-06-11:11:42:26 VALID N N Y 6 rows selected. SQL> select text from dba_source where name = 'DR$1062$U'; TEXT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- procedure DR$1062$U ( dr$rid in rowid, dr$txt in varchar2, reindex in boolean, updop in boolean ) as begin if (updop) then delete from "YONG"."DR$INDCTX_TESTCTX2$I" where DR$ROWID = dr$rid; end if; if (reindex) then dridml.c_rowid := dr$rid; dridml.c_text_vc2 := dr$txt; dridml.ctxcat_dml('YONG','INDCTX_TESTCTX2'); end if; exception when dr_def.textile_error then drue.raise; when others then drue.text_on_stack(sqlerrm,'DR$1062$U'); drue.raise; end; For better readability, I rewrite it as follows: procedure DR$1062$U (dr$rid in rowid, dr$txt in varchar2, reindex in boolean, updop in boolean) as begin if (updop) then delete from "YONG"."DR$INDCTX_TESTCTX2$I" where DR$ROWID = dr$rid; end if; if (reindex) then dridml.c_rowid := dr$rid; dridml.c_text_vc2 := dr$txt; dridml.ctxcat_dml('YONG','INDCTX_TESTCTX2'); end if; exception when dr_def.textile_error then drue.raise; when others then drue.text_on_stack(sqlerrm,'DR$1062$U'); drue.raise; end; SQL> select trigger_body from user_triggers where trigger_name = 'DR$INDCTX_TESTCTX2TC'; TRIGGER_BODY -------------------------------------------------------------------------------- begin if (inserting or updating('NAME')) then ctxsys.dr$1062$u( :new. rowid, :new."NAME", TRUE, (not inserting) ); end if; end ; SQL> select count(*) from testctx where lower(name) = 'houston'; COUNT(*) ---------- 0 SQL> insert into testctx values (100, 'houston'); 1 row created. SQL> commit; Commit complete. SQL> select count(*) from testctx where lower(name) = 'houston'; COUNT(*) ---------- 1 SQL> select * from testctx where catsearch(name, 'houston', '') > 0; <-- no need to exec ctx_ddl.sync_index ID NAME ---------- --------------------------------------------------------- 100 houston * Locks taken during ctx_ddl.sync_index (test in 10.2.0.1): alter session set events '10704 trace name context forever, level 10'; exec ctx_ddl.sync_index('INDCTX_TESTCTX') alter session set events '10704 trace name context off'; C:\oracle\product\10.2.0\admin\orcl10g\udump>grep ksqg orcl10g_ora_4040.trc | grep "*** [A-Z][A-Z]-" ksqgtl *** TM-0000110d-00000000 mode=3 flags=0x401 timeout=21474836 *** <-- SYS.DBMS_LOCK_ALLOCATED ksqgtl *** TX-00070021-00000152 mode=6 flags=0x401 timeout=0 *** ksqgtl *** UL-40000014-00000000 mode=6 flags=0x11 timeout=21474836 *** <-- user lock ksqgtl *** TM-00009fd8-00000000 mode=3 flags=0x401 timeout=21474836 *** <-- CTXSYS.DR$WAITING ksqgtl *** TM-00009fa7-00000000 mode=3 flags=0x401 timeout=21474836 *** <-- CTXSYS.DR$INDEX ksqgtl *** TX-00090026-00000176 mode=6 flags=0x401 timeout=0 *** ksqgtl *** TM-0000cda9-00000000 mode=3 flags=0x401 timeout=21474836 *** <-- YONG.DR$INDCTX_TESTCTX$I ksqgtl *** TM-0000cdac-00000000 mode=3 flags=0x401 timeout=21474836 *** <-- YONG.DR$INDCTX_TESTCTX$K ksqgtl *** TM-0000cdae-00000000 mode=3 flags=0x401 timeout=21474836 *** <-- YONG.DR$INDCTX_TESTCTX$R ksqgtl *** TM-0000cd9c-00000000 mode=3 flags=0x401 timeout=0 *** <-- YONG.TESTCTX ksqgtl *** TM-00009fd6-00000000 mode=3 flags=0x401 timeout=21474836 *** <-- CTXSYS.DR$PENDING ksqgtl *** TM-00009fd8-00000000 mode=3 flags=0x401 timeout=21474836 *** <-- CTXSYS.DR$WAITING The function ksqgtl is "KSQ: get an enqueue". If needed, can include ksqrcl (probably release locks). All TM locks are in mode 3. So there shouldn't be conflict with DML. * Other interesting stuff: What is the best way to physically change storage for oracle text objects? https://metalink.oracle.com/metalink/plsql/f?p=200:27:6613861603919800296::::p27_id,p27_show_header,p27_show_help:528446.996,1,1