Advice for optimizing CLOB use?

[DougCMH]

We are about to have a table (log of web communication transactions) with thousands and thousands of rows.
One of the columns in the table will be a CLOB (containing logged JSON request string).

Any advice on how best to optimize the design so that processing CLOB data does not slow things down ?
Should the CLOB column go in a separate table?
Will a view that breaks up the CLOB have an effect on performance?
Will indices help to speed up access to the table as normal?

Thanks

[Scott Klement]

It's really hard to answer these questions without understanding how the table and it's data are used.

Is there something in particular that you are doing that is performing poorly? Or, what prompted this question?

[Vectorspace]

As Soctt says, it will depend a lot on what you will do with the data in this table.

But one thing to consider, if when you read the records you will also be decoding the JSON and extracting data from it. Rather than storing the raw JSON string in a CLOB, you can store the parsed JSON in a binary format (BSON) in a BLOB. Because it is parsed the system does not need to do as much work to decode it, which means reading values from it is quicker. SQL function JSON_TO_BSON() converts a JSON string to BSON, BSON_TO_JSON() does the reverse.