Topic: and a multitabular insertion
Hello! There is a procedure which does parsing of a difficult XML document and uses a multitabular insertion by means of INSERT ALL in one principal and some the subordinate tables. In this procedure some subqueries and a condition on which depends, record will be interposed into the principal table or one of subordinates. There is the general which generates ID-shniki for records in these tables. To use in subqueries it is forbidden, but if for it to make function-wrapper this restriction manages. But if to lead performance test on a method of Thom Kajta, with packet usage runstats absolutely desirable moments come to light not. In comparison with the first method of analysis when for parsing of a principal element one procedure was used, and for parsing of nested elements from it embedded procedures were caused, the operating time, an amount of latches and other parameters increases: file io wait time, cache table scan latch, session pga memory. I can assume that the magnification of an overhead charge is connected by that because of necessity to apply function-wrapper the amount of switchings of a context with SQL on PL/SQL increased and is reverse.
On one of blogs I met a method where it was supposed beforehand values in a collection. But the method underfulfilled enough also leaves set of questions, and the author already hammered for a long time on the blog.
Question in the following: who faced such problem and how it solved?