Oracle Index – Handling Columns Exceeding Maximum VARCHAR2 Length

oracle

Description:

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

create table cstb_temp_log 
(
id NUMBER PRIMARY KEY,
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 .

Problems:

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) ?

Best Answer

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)

Related Question