Does anyone have the syntax for converting a Long Raw to a BLOB in a Trigger? Have a LONG RAW updating a BLOB on an INSERT in another 8.1.7 table (to_lob())but the response time is SLOW. Any suggestions appreciated. Thanks ! Steve.
most LOB performance issues I have seen have been to do with storage parameters. Check whether it is being stored inline, out of line or partially inline.
Having read the Oracle docs 100 times regarding LOB storage, I had thought that out of line was the performance optimum. Having then experimented I think the Oracle docs are misleading. I honestly believe that the partially in line option store no data at all inline, but that it allows the Oracle optimiser to assume it is. If that is the case it is credit to the Oracle developers and a right beating from me to the Oracle documenters :-)
A simple way to check is to mess around with your to_lob function (selecting from dual), again selecting from a table etc. This should give some idea of where the performance hit is, try and determine if the to_lob function is the hit, or the actual selects or inserts/updates are the hit.
I'm afraid I can't give you a simple answer but I did recently settle on the following for all tables containing clobs....
ALTER TABLE <tablename> MOVE TABLESPACE <tablestorage> LOB (lob column names) STORE AS (TABLESPACE <lob tablespace> ENABLE STORAGE IN ROW PCTVERSION 40 NOCACHE NOLOGGING);
(I don't run logging on this particular DB).
And no matter how many times I re-read the Oracle docs regarding LOB storage, I'm still not sure I fully understand the above in relation to LOBS, the docs tell me one thing but actual performance tells me another.
The tablespace parameters for <lob tablespace> mentioned above are....
Currently all tables with lobs have their lob storage in this tablespace. Clearly if there is a lot of lob activity you would want to create a number of (maybe smaller) tablespaces.
In summary, I think the Oracle docs are an invaluable source of info but the info you want is often buried so deep as to render the docs misleading. Experiment with the storage parameters, see how you get on.