Forum Stats

  • 3,825,060 Users
  • 2,260,463 Discussions
  • 7,896,401 Comments

Discussions

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

perecd
perecd Member Posts: 7 Blue Ribbon
edited Jul 17, 2017 1:09PM in Text

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?

Answers

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Jul 17, 2017 8:06AM

    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
    perecd Member Posts: 7 Blue Ribbon
    edited Jul 17, 2017 10:57AM
    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
    perecd Member Posts: 7 Blue Ribbon
    edited Jul 17, 2017 11:40AM

    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
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Jul 17, 2017 11:54AM

    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
    perecd Member Posts: 7 Blue Ribbon
    edited Jul 17, 2017 1:09PM

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

    I haven't reported to Oracle Support.

    I've just tested the Text feature.

This discussion has been closed.