Q: How do we "manage" the size of a table's associated SYS_LOB files?

Background: I have a table [SRCH_CACHE] that was setup as a lookup table, because the two base tables could not be joined/filtered with sufficient speed. The table is pretty simple. A redacted CREATE TABLE is provided here with the pertinent columns, for clarity:

CREATE TABLE APEX_EBS_DEV.SRCH_CACHE 
  (
    INVENTORY_ITEM_ID NUMBER, 
    SHORT_DESCRIPTION VARCHAR2(240 BYTE), 
    LONG_DESCRIPTION VARCHAR2(4000 BYTE), 
    PRIMARY KEY (INVENTORY_ITEM_ID)
  );
    
   CREATE INDEX APEX_EBS_DEV.SRCH_CACHE_TXT1 ON APEX_EBS_DEV.SRCH_CACHE (SHORT_DESCRIPTION) 
   INDEXTYPE IS CTXSYS.CONTEXT ;

  CREATE INDEX APEX_EBS_DEV.SRCH_CACHE_TXT2 ON APEX_EBS_DEV.SRCH_CACHE (LONG_DESCRIPTION) 
   INDEXTYPE IS CTXSYS.CONTEXT ;

This table fills up with 950K records, with an average Short Description length of 71 characters and Long Description length of 138 characters. Mostly, the Long Description is null.

All told, there are 65,600,598 characters in the Short Description and 11,029,843 characters in the Long Description. So, about 77MB worth of data, if I calculated it correctly.

The problem is, we end up with table, index and LOB files that are 70GB+ in size. This only happens in PRODUCTION. Our DEV and TEST environments see the table created, the indexes populated and then they remain reasonably stable is size. In production, with the same number of records, they seems unreasonably large and continue to grow. In particular, we don't understand why the SYS_LOB table is growing so big.

T-SPACE   SEGMENT_NAME              SEGMENT_TYPE      SIZE(GB)
APEX        SYS_LOB0012174458C00002$$     LOBSEGMENT        38.644710
APEX        DR$SRCH_CACHE_TXT1$I          TABLE             17.901370
APEX        DR$SRCH_CACHE_TXT1$X          INDEX              4.817383
APEX        DR$SRCH_CACHE_TXT2$I          TABLE              2.958008
APEX        SYS_LOB0012174445C00002$$     LOBSEGMENT         2.890808
APEX        SYS_IOT_TOP_12174462          INDEX              2.812500
APEX        SYS_IOT_TOP_12174449          INDEX              2.800781
APEX        DR$SRCH_CACHE_TXT2$X          INDEX              0.756836
APEX        SRCH_CACHE                    TABLE              0.218750

Can you provide any insight into why the LOB tables are so big? And, is there a way to periodically "compress" the indexes, as they are creeping up in size on a regular basis as data is merged?

Rob


Application Express 5.1.0.00.45
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production