Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rodney.

Asked: November 11, 2020 - 1:15 pm UTC

Last updated: November 18, 2020 - 11:55 pm UTC

Version: Oracle 18c

Viewed 1000+ times

You Asked

I haven't worked much with LOBs until now and I'm trying to make sure I take the right approach as I design my code and table structures. The functionality I am designing is for flexfields. I plan to store the results in a JSON document in a BLOB column. I have several design questions as it relates to this.

1. As I will be implementing flexfields in several areas of the application, I was wondering if you thought it is better to have a flexfield column (BLOB) on each table where flexfields are to be made available, or is it better to have one table (something like doc_id NUMBER, flex BLOB) and then have flex_doc_id (FK to my BLOB table) on each table where I require flexfields? I have seen where some people generally recommend having a single LOB table like this but I'm unsure of the reasoning behind it.

OPTION 1
FLEX_DOCS
doc_id NUMBER PRIMARY KEY
flex BLOB

PURCHASE_ORDERS
...
flex_doc_id NUMBER FK REFERENCES flex_docs.doc_id

SALES_ORDERS
...
flex_doc_id NUMBER FK REFERENCES flex_docs.doc_id

OPTION 2
PURCHASE ORDERS
...
flex BLOB

SALES_ORDERS
...
flex BLOB


2. In developing my code I find myself passing BLOBs as parameters and using local variables to work with the BLOBs. My understanding is that I am really just passing pointers, not actual potentially large BLOB files. Is that correct? I just want to make sure I understand if there are any performance issues to consider when writing my code dealing with the BLOBs. In particular, I am using an APEX front-end (APEX 20.1) and while I try to write most of my PL/SQL code on the backend I am working out how to best interact with my flexfield API particularly as it relates to the json doc. My flexfield API is in a database package and currently returns the values entered on the screen in a JSON doc BLOB to a local variable in the screen that I can then save in whatever table I am working with (again, passing to a package TAPI on the backend). So I'm just looking for any advice on whether there are performance considerations in this approach.

and Connor said...

1) I would have blobs in the tables where they are appropriate. Think of the mess it creates whenever someone wants a subset or copy of data from (say) Dev to Test, or Production to Test. You'd need to copy your true table PLUS the "blob table" (and then sift through the bits you want etc). That advice is a hangover from LONG RAW's which were a pain to manage so we dumped them into separate tables. From CLOB/BLOB, you now can put them right were they need to be. The only real decision to make is based on usage/volume and thus whether you'll got inline or out of line storage. From what you've said in the question, inline is *probably* the way to go here.

2) Thats correct. Unless we need to "do" something with the blob contents, we're typically just dealing with locators. The only thing I'd take a closer look at a the BLOB/CLOB decision. Don't get me wrong - we recommend BLOB for JSON because its the most efficient and APEX should be fine with that. But we don't live in a vaccuum, and there may be other tools that your business uses to look at the data (Excel, reporting tools, etc etc). Some of these may function better with CLOBS instead of BLOBS. Just make sure you cover all your bases.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect. Thanks!

Rodney Bailey, November 18, 2020 - 11:34 am UTC

Thanks for your response. I suspected that maybe the "LOBs in one table" approach may have been old advice.

As far as the CLOB/BLOB choice, I'll admit I was a little unsure of the ramifications of the decision to follow Oracle's advice and go with BLOB. Since I do not want my API to be aware of specific tables (like PURCHASE_ORDERS or SALES_ORDERS in my example), I query the row in Apex and then pass in the BLOB to my API which reads my configuration setup from a setup table and builds each APEX item, populating the value in the item (using JSON_VALUE) and returns the items (html script) as a row in a pipelined function. (This passing of the BLOB was my big concern and the reason for the original question).
As far as exposing the data to reports or other processes, I envision providing views that expose the needed attributes using Oracle's JSON API's for querying. I don't envision the need to hand over the entire document to an outside process. But if I did, is it not as simple as using the TO_CLOB() function? Are there some complexities or severe performance issues I should be aware of? Like I said, I think my current use cases can be served with a BLOB but I would like to have an understanding for future use cases.
Connor McDonald
November 18, 2020 - 11:55 pm UTC

as simple as using the TO_CLOB() function


Yes, but many tools you can just point at the table and you're done. Now you're adding the requirement of an SQL and/or a view for every access. Maybe those tools when looking at the SQL or view won't see the relationships that pointing directly at a table would see (foreign keys etc).


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.