Database Software

Hi,

I am creating basic text index on a table. Buf after inserting rows index is not updating automatically. Do we need to rebuild index after each update?

Here is my practice :

 

SQL> CREATE TABLE quick (
  2               quick_id NUMBER PRIMARY KEY,
  3               text VARCHAR(80)
  4             );

Table created.

SQL> CREATE INDEX quick_text ON quick ( text )
  2     INDEXTYPE IS ctxsys.CONTEXT;

Index created.

SQL> INSERT INTO quick (quick_id, text )
  2       VALUES (1, 'The cat sat on the mat');

1 row created.

SQL> INSERT INTO quick (quick_id, text )
  2       VALUES (2, 'The quick brown fox jumped over the lazy dog');

1 row created.

SQL> COMMIT ;

Commit complete.

SQL> SELECT quick_id
  2    FROM quick
  3   WHERE contains (text, 'cat') > 0;

no rows selected

SQL> ALTER INDEX  quick_text REBUILD;

Index altered.

SQL> SELECT quick_id
  2    FROM quick
  3   WHERE contains (text, 'cat') > 0;

  QUICK_ID
----------
         1

1 row selected.

SQL> INSERT INTO quick (quick_id, text )
  2       VALUES (3, 'The fish is big.');

1 row created.

SQL> SELECT quick_id
  2    FROM quick
  3   WHERE contains (text, 'fish') > 0;

no rows selected

SQL> ALTER INDEX  quick_text REBUILD;

Index altered.

SQL> SELECT quick_id
  2    FROM quick
  3   WHERE contains (text, 'fish') > 0;

  QUICK_ID
----------
         3

1 row selected.

Please clarify my doubt.