lh, April 11, 2019 - 10:53 am UTC
Hi
Thank You for Your prompt answer which explained a lot and raised some new points.
Did I understood correctly
- if we create in sql-statement lob column containing json/xml document it won't be using 'vector' transfer.
Is there any way how we could make it happen ?
- if we store json/xml document to global temporary table and fetch it from there, it won't be using 'vector' transfer. Or is there way to define lob in global temporary table as secure file lob?
lh
April 12, 2019 - 2:24 am UTC
The clobs will obey the database-wide rules for sercurefiles
SQL> create table t ( x xmltype );
Table created.
SQL>
SQL> select column_name, securefile
2 from user_lobs
3 where table_name = 'T';
COLUMN_NAME SEC
------------------------------ ---
SYS_NC00002$ YES
SQL> sho parameter secure
NAME TYPE VALUE
------------------------------------ ----------- ------------
db_securefile string PREFERRED
If its already a basicfile you could convert it as per
I am accusing You
lh, April 11, 2019 - 11:31 am UTC
Hi
You give such a great service that first one asks from You and only after that tries himself.
create global temporary table t2 ( x clob)
lob ( x) store as securefile;
ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace ""
lh
virtual lobs
Racer I., April 12, 2019 - 7:02 am UTC
Hi,
I think lh means what happens with LOBs that are created on-the-fly in a statement with XMLTABLE or JSONTable or similar. Can this be speeded up? Would it be necessary to store it in an intermediate table in a securefile field and select from that to get the benefit? Could this be a GTT or must it be a regular table?
regards,
April 15, 2019 - 4:36 am UTC
I don't read it that way :-)
But with any LOB, whether explicit or implicit created, you'll only get the SQLNet transfer benefit if it was created as securefile.
other way 'round
Racer I., April 12, 2019 - 7:23 am UTC
Hi,
Actually the opposite of XMLTable. Whatever returns LOBs. XMLQuery? CAST AS JSON?
regards,
Global temporary table as securefile
lh, April 15, 2019 - 10:29 am UTC
Hi
I interpret error message received when trying to create clob column as securefile to global temporary table, that is not possible.
Can one create lob column created in select statement as securefile lob ?
If securefile option is not possible to enhance network traffic what other possibilities there are ? DEFAULT_SDU_SIZE ?
lh
speedy gonzales is a pedestrian
Racer I., April 16, 2019 - 6:45 am UTC
Hi,
@lh : There is no "SELECT ... CAST(XMLQuery() AS LOB (STORE AS SECUREFILE)) ..." (I guess) and, as you discovered, GTTs can't have securefile lob fields. So how about a real table? You need to isolate parallel sessions manually (say via sequence/pk) when using this table and clean it out yourself or have some housekeeping on it.
regards,
back to basic(file)s
Racer I., April 16, 2019 - 6:52 am UTC
Hi,
@lh : another point : how do such LOBs get so big as to benefit from this? Normally you send small JSON/XML messages around. Instead of sending massive amounts of data as one LOB maybe stick to regular row sets? The potential boost for securefiles would be offset by the tags and string encoding.
regards,
April 16, 2019 - 11:48 pm UTC
Agreed. I don't think the network improvements would justify the cost of storing a lob in a permanent table, unless the network was really bad....at which point it would seem better to tackle that root cause.
lh, May 13, 2019 - 10:48 am UTC
Hi
Application and database are communication with each other by using json messages.
Messages can be quite large, because message is 'hierarcal'. e.g. if one would look at some specific order, this message would contain order row, order line rows, delivery instruction rows etc. The whole concept.
I am interested are there any methods, parameters etc, which would make accessing these lob columns faster. Even a little bit. This is big system and cumulative effects might be consirable for even small changes.