Posted by If you really need your text indexes to be “super dooper” up to date, that is, always in sync with your data, you can set the SYNC level to be ON COMMIT. For example,
SQL> create table t1 (
2 x int,
3 y varchar2(1000) )
4 ;
Table created.
SQL>
SQL> create index t1_iz on T1 ( y )
2 indextype is ctxsys.context
3 parameters ('sync (on commit)');
Index created.
SQL>
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');
1 row created.
SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');
1 row created.
SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');
1 row created.
SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1
2 where contains(y,'quick') > 0 ;
X
----------
Y
----------------------------------------------------------------------------------------------------
1
The quick brown fox jumped over the lazy dog
3
Several fabulous dixieland jazz groups played with quick tempo
But don’t forget. There is no such thing as a free lunch. As has always been the case, the more you can index “in batch” the less fragmentation you get with your text index, and obviously, if you’re planning on doing a stack of activities when you commit…then your commit performance will be impacted. Let’s compare the insert’s above, with and without a text index in place.
--
-- With index
--
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');
1 row created.
SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');
1 row created.
SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');
1 row created.
SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');
1 row created.
SQL> set timing on
SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
--
-- Without index
--
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');
1 row created.
SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');
1 row created.
SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');
1 row created.
SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');
1 row created.
SQL> set timing on
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
Not too shabby…but noticeable.
Learning is not a spectator sport 
I’m sure there are better metrics than elapsed time to demonstrate performance advantages 🙂
This is true….but when blogging on Easter Saturday, you’ll forgive me for being “concise” 🙂