Skip to Main Content
  • Questions
  • table with 900 million records with 2 clob fields and weighing 5tera and without indexes

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Adelmo.

Asked: April 28, 2022 - 3:50 pm UTC

Last updated: May 16, 2022 - 2:10 am UTC

Version: 19c

Viewed 100+ times

You Asked

Greetings oracle DB gurus,
On this subject I want a recommendation, the database weighs 7 teras in total but 5 of that 7 teras is only the audit table, that table only has 3 years of data (The business needs to keep all the data) and it has more than 900 million record and 2 clob fields, it is a move table,
We have had several incidents related to this table, slowness in the Database for inserting that table, as it has clob fields that sometimes save 10 million characters, not if that is related, apart from that we have run out of disk space, tablespace or data file, the log is filling up very fast, it doesn't even let the alerts arrive before the disk is full, for example.

This table is used by several applications at the same time and saves all the activities that users perform, the clob fields are the details of the activities

The business wants to pull reports from this table when that table only has one index. here I leave the structure of the table

CREATE TABLE EBTDEV.ADMIN_AUDIT
(
ID NUMBER NOT NULL
, EVENT_TYPE NUMBER(1, 0)
, OWNER_ID NUMBER
, OWNER VARCHAR2(100 BYTE)
, OWNER_PERMISSIONS CLOB
, EVENT_DESCRIPTION VARCHAR2(200 BYTE)
, OBJECT_TYPE VARCHAR2(100BYTE)
, OBJECT_ID NUMBER
, BEFORE CLOB
, AFTER CLOB
, TERMINAL VARCHAR2(100 BYTE)
, EVENT_DATE TIMESTAMP(6)
, AGENCY VARCHAR2(10 BYTE)
,PORTAL VARCHAR2(20 BYTE)
, UPD_FILE_DW TIMESTAMP(6)
)
and this is the only index it has

CREATE INDEX EBTDEV.IX_EVENT_DT_UPD_FILE_DW ON EBTDEV.ADMIN_AUDIT (EVENT_DATE ASC, UPD_FILE_DW ASC)


my question is what is your recommendation to improve performance regarding the creation of reports and optimize the table so as not to have more issues of DB space and slowness in the DB

and Connor said...

Well, that is going to depend a LOT on the types of queries that you need to run, but some general guidelines for large tables and LOBs in them are:

1) look at partitioning. If reports are coming in via date ranges, then partitioning on that date can yield big gains. More details here:



2) How often are the CLOBs accessed in your reports? If not often, then look at storing them out of line which reduces the size of your table, at the expense of making the clobs a little bit more expensive to access.

3) Look at compression of the data and and also using securefiles to potentially compress the LOBs. Do some testing on a smaller scale, and if it helps, then here's a post showing examples of doing a migration online

https://connor-mcdonald.com/2015/07/03/continuous-delivery-moving-to-securefile/

Rating

  (1 rating)

Comments

Updates to CLOBS

Dan, May 11, 2022 - 4:27 pm UTC

As I recall, when a CLOB is updated, the entire CLOB is copied to a new location. (Usual caveats apply here...). If that is [still] true, and it is a log of multiple interactions, you might consider storing the interaction, one per database row, so as time goes on, you are just inserting new rows, not updating old ones. You also might find that you can use a normal VARCHAR2 column for each interaction.
Connor McDonald
May 16, 2022 - 2:10 am UTC

when a CLOB is updated, the entire CLOB is copied to a new location

Very much depends on the operation being performed, but its common from a *development* perpsective, ie, people read the whole LOB, manipulate it, and then write the whole thing back.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.