Up to date text indexes

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.

3 thoughts on “Up to date text indexes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s