4 Replies Latest reply: Oct 8, 2012 7:11 AM by 919560 RSS

    mass insert from xmltype table

    919560
      Hi guys!

      A have a problem with inserting records from xmltype table to er table.

      I have a 150MB xml, wich i first insert into temporary xmltype table.

      CREATE TABLE table OF XMLType XMLType STORE AS SECUREFILE BINARY XML

      Then i want to insert the data into ordinary er table.

      insert into er_table
      (org_id, name, ...)
      SELECT id, name
      FROM table t
      , XMLTABLE ('xml/schema/path'
      PASSING t.object_value
      COLUMNS id number PATH 'id',
      sifra varchar2(255) PATH 'name',
      ) xtab';


      It works if xml is not big, but otherwise ...

      First, it doesn't free memory when it is done, and it uses it a lot.
      Second, it lasts an eternety to insert all the data.

      Does anybody have any idea how to optimize it?

      thanks in advance,

      Grega
        • 1. Re: mass insert from xmltype table
          odie_63
          Hi,
          First, it doesn't free memory when it is done, and it uses it a lot.
          Second, it lasts an eternety to insert all the data.

          Does anybody have any idea how to optimize it?
          I've already loaded 100MB files in less than 30s using this technique.

          Please post execution plan and exact db version.

          How many rows are there? Is the SELECT alone slow as well?

          Is the XQuery expression really as simple as 'xml/schema/path'?
          • 2. Re: mass insert from xmltype table
            919560
            Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
            select clause is really fast, so that is not the issue.

            It looks like insert clause is creating this delay. What do you think?

            i have aprox. 500 000 records in xml and 10 er tables.

            Why is inserting this records also creating memory leak?
            • 3. Re: mass insert from xmltype table
              odie_63
              It looks like insert clause is creating this delay. What do you think?
              Any trigger on the table?

              How many blocks for the table segment? Empty blocks?

              Does the APPEND hint help?
              Why is inserting this records also creating memory leak?
              Which memory area are you talking about?
              How do you monitor this leak?
              • 4. Re: mass insert from xmltype table
                919560
                yes, i have a before insert trigger. this could be stopping it. I will test the trigger code..

                Append hint doesn't make any big difference. It tok 1:36 min for 12000 records.

                I have observerd memory usage in task manager and i can see clearly that useg is rising while inserting and not going back to starting point.