SQL Language (MOSC)

MOSC Banner

XMLtype column update very slow

edited Aug 12, 2020 5:00AM in SQL Language (MOSC) 2 commentsAnswered

Any one has experience with XMLType column update? I have this piece of simple test code. The Xquery in the update slows down significantly as the record count increases. Is there alternative method to optimize this? Any help is greatly appreciated.

CREATE TABLE test_append_xml (tran_recon_xml XMLTYPE)

XMLTYPE COLUMN tran_recon_xml   STORE AS BINARY XML   ALLOW NONSCHEMA       

/

CREATE TABLE test_append_timing (

rec_num                  NUMBER,

seconds                  NUMBER)

/

-- ----------------------------------------------------------------------------

-- Insert XML node (TrxnRec) 20,000 times to same XML parent (TrxnRecon) and time each iteration.

-- ----------------------------------------------------------------------------

DECLARE

   ln_start          NUMBER;

   lv_temp           VARCHAR2(4000);

BEGIN

   INSERT INTO test_append_xml ( tran_recon_xml ) VALUES (

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center