Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 590
  • Last Modified:

INDEXING clob

In order to use CLOB datatype with CONTAINS it should be indexed as CTXSYS.CONTEXT.
Is that trye that I need to drop and recreate this index each time after new value is inserted.

I found that if index has been created as a part of table definition and insert data later "CONTAINS" doesn't work.

I tested this as
1. drop/create table with CTXSYS.CONTEXT index for CLOB datatype
2. inserted data into the table using stored procedure
3. use CONTAINS, but no row returned

After I drop and re-create index "CONTAINS" returns data

Please advise
0
smena
Asked:
smena
1 Solution
 
arperumalCommented:

Try this. I think you have missed ALTER INDEX



SQL> create table ctx_demo
  2      ( id number primary key,
  3       text clob
  4*    )
  5  /

Table created.

SQL> create index search_idx
  2      on ctx_demo(text)
  3      indextype is ctxsys.context
  4  
SQL> /

Index created.

SQL> insert into ctx_demo (id, text) values (1, 'How Now Brown Cow');

1 row created.

SQL> commit;

Commit complete.

SQL> select *
  2        from ctx_demo
  3       where contains (text, 'Now') > 0
  4  
SQL> /

no rows selected

SQL> alter index search_idx
  2      rebuild parameters ( 'sync' )
  3  /

Index altered.

SQL> select *
  2        from ctx_demo
  3       where contains( text, 'Now' ) > 0
  4  /

        ID                                                                      
----------                                                                      
TEXT                                                                            
--------------------------------------------------------------------------------
         1                                                                      
How Now Brown Cow                                                              
                                                                               

SQL> insert into ctx_demo ( id, text ) values (2,'This must show');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ctx_demo where contains(text,'must') > 0;

no rows selected

SQL> alter index search_idx
  2      rebuild parameters( 'sync' )
  3  /

Index altered.

SQL> select * from ctx_demo where contains(text,'must') > 0;

        ID                                                                      
----------                                                                      
TEXT                                                                            
--------------------------------------------------------------------------------
         2                                                                      
This must show                                                                  


Hope this will help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now