We have a table on PostgreSQL 9.3 and we migrate it (data & structure) to Oracle Here is our table:

create table cstb_temp_log 
log_info data_type(max_length) -- max_length < 10000
-- log_info : data is inserted/updated every second (**)
-- data_type: may be we can use "clob" because max_length of varchar2 in Oracle is 4000 (SQL).
-- cstb_temp_log : size = 1 GB, row = 400000

We want to index and search "text" on log_info column, so we tried Oracle Text 11g .


If we use "data_type" is "clob", we can use "context index" and it have to synchronize after DML . ( we can not use this way because of (**) )

If any, how can we index and search on "log_info" column (max_length < 10000 and data is changed every second) ?

  • 3
    You can try moving to 12c and use extended char setting so your varchar2 column could be (32k-1) . just a suggestion. No such option in 11gr2 – Raj Dec 16 '15 at 18:20
  • well, it's hard to do. – Luan Huynh Dec 17 '15 at 6:57
  • if database can not handle it, we will use "lucene" on our app. – Luan Huynh Dec 17 '15 at 16:21

I think its a good suggestion done by @raj moving to 12C as 11g is nearing EOSL. If you are considering using CLOB with Oracle Text Search, consider below.

Maintaining Indexes and Synchronization In 11g users can specify at index creation the index update preference: manually, on commit, or at regular intervals. Users can also specify a transnational text index, where documents are searchable immediately after being inserted or updated. Note that the catalog index type - designed specifically for the short pieces of text typically found in eBusiness catalogs – is always transactional and needs no synchronization.

Pleaese read: http://www.oracle.com/technetwork/testcontent/11goracletexttwp-133192.pdf (Page 12)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.