Size of column Data of DR${index_name}$R table grows at a tremendous rate

perecd

Hi.

I have a text index that is 1.9 GB immediately after creation (using Ctx_Report.Index_Size).

The text column contains data in the form of digital lines of the same length (5 characters) with a separator chr(10) - e.g.: 11006||chr(10)|| 34008

This is DDL (select ctx_report.create_index_script('KUP.I_KUP_HRPXR_IDX') ddl from dual)  for the storage preference (other options by default ):

begin  ctx_ddl.create_preference('"I_KUP_HRPXR_IDX_STO"','BASIC_STORAGE');  ctx_ddl.set_attribute('"I_KUP_HRPXR_IDX_STO"','I_TABLE_CLAUSE','tablespace text_idx storage (initial 1K NEXT 8k)');  ctx_ddl.set_attribute('"I_KUP_HRPXR_IDX_STO"','K_TABLE_CLAUSE','tablespace text_idx storage (initial 1K NEXT 8k)');  ctx_ddl.set_attribute('"I_KUP_HRPXR_IDX_STO"','R_TABLE_CLAUSE','tablespace text_idx storage (initial 1K NEXT 8k) lob                         (data) store as (disable storage in row cache                          STORAGE  ( INITIAL 8K NEXT 8k))');  ctx_ddl.set_attribute('"I_KUP_HRPXR_IDX_STO"','N_TABLE_CLAUSE','tablespace text_idx storage (initial 1K next 8K)');  ctx_ddl.set_attribute('"I_KUP_HRPXR_IDX_STO"','I_INDEX_CLAUSE','tablespace text_idx storage (initial 1K NEXT 8k) compress 2');  ctx_ddl.set_attribute('"I_KUP_HRPXR_IDX_STO"','P_TABLE_CLAUSE','tablespace text_idx storage (initial 1K NEXT 8k)');  ctx_ddl.set_attribute('"I_KUP_HRPXR_IDX_STO"','S_TABLE_CLAUSE','tablespace text_idx storage (initial 1K NEXT 8k)');  ctx_ddl.set_attribute('"I_KUP_HRPXR_IDX_STO"','STAGE_ITAB','YES');  ctx_ddl.set_attribute('"I_KUP_HRPXR_IDX_STO"','G_TABLE_CLAUSE','tablespace TEXT_IDX storage (initial 10M next 8K)');  ctx_ddl.set_attribute('"I_KUP_HRPXR_IDX_STO"','G_INDEX_CLAUSE','tablespace TEXT_IDX storage (initial 10M NEXT 8k) compress 2');end;/create index "KUP"."I_KUP_HRPXR_IDX"   on "KUP"."I_KUP"      ("HRPXR")  indextype is ctxsys.context  parameters('    storage   "I_KUP_HRPXR_IDX_STO"    sync (on commit)  ')

After 12 hours I discovered an increase in size to 22Gb(using CTX_REPORT.INDEX_SIZE('KUP.I_KUP_HRPXR_IDX')) and degradation of performance in queries.

All tables except one ($R) changed insignificantly. In $R table lob column DATA has grown from about 500 MB to 20 GB.

TABLE:                                             KUP.DR$I_KUP_HRPXR_IDX$R

TABLESPACE NAME:                     TEXT_IDX

BLOCKS ALLOCATED:                   8

BLOCKS USED:                              8

BYTES ALLOCATED:                      65,536 (64.00 KB)

BYTES USED:                                 65,536 (64.00 KB)

LOB SEGMENT:                              KUP.SYS_LOB0000225014C00002$$

TABLE NAME:                                 KUP.DR$I_KUP_HRPXR_IDX$R

LOB COLUMN:                                DATA

TABLESPACE NAME:                     TEXT_IDX

BLOCKS ALLOCATED:                    2616856

BLOCKS USED:                               2616856

BYTES ALLOCATED:                       21,437,284,352 (20,444.19 MB)

BYTES USED:                                  21,437,284,352 (20,444.19 MB)

When I tried

select dbms_lob.getlength(data)/1024/1024 Gb from KUP.DR$I_KUP_HRPXR_IDX$R;

i get  about 500 GB.
Why is the column Data in $R table growing so fast? Why is the difference bewteen dbms_lob.getlength and CTX_REPORT.INDEX_SIZE?
Any help?

Roger Ford-Oracle

Can you run the following SQL?

select ixp_index_partition_name, ixp_docid_count from ctx_user_index_partitions where ixp_index_name = 'I_KUP_HRPXR_IDX';

That should return a number the same as (or close to) the number of rows in your table. If it comes up with a huge number then something has gone badly wrong with DOCID allocation during indexing, which would then explain the very large $R LOB.

perecd

select ixp_index_partition_name, ixp_docid_count from ctx_user_index_partitions where ixp_index_name = 'I_KUP_HRPXR_IDX';

No rows return.

I tried

select ixp_index_partition_name, ixp_docid_count from ctx_user_index_partitions

and nothing. As well i tried these queries as sys - the same.

perecd

I tried

select idx_docid_count from ctxsys.dr$index where idx_name='I_KUP_HRPXR_IDX';

and get 37004361.

After executing

select count(*) from kup.i_kup;

I get 36977300.

So the values are close.

Roger Ford-Oracle

That looks OK, so no explanation there.  Can you do:

select row_no, length(data) from KUP.DR$I_KUP_HRPXR_IDX$R

Have you reported this to Oracle Support?  I had a similar issue reported by them a couple of days ago, not sure if that was you or someone else.

perecd

I get 1 row.
length(data)  ~ 500 MB

I haven't reported to Oracle Support.

I've just tested the Text feature.