1

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?

2

Re: and a multitabular insertion

ultrasonic7 wrote:

increases

With 999 to 1000?

3

Re: and a multitabular insertion

Elic , the operating time increases on 6 %, an amount of latches on 30 %. To me therefore and interesting, someone used  in a wrapper and if used somehow cached values ?

4

Re: and a multitabular insertion

-2 , as a rake any it is not dug, it simply returns value . I assume that a rake - in plural switching of a context.

5

Re: and a multitabular insertion

ultrasonic7;
How there with switching of contexts if to use the help-2 (nextval in values, instead of in subqueries)?

6

Re: and a multitabular insertion

I and this variant tried, directly nextval caused in section VALUES. But it strange conducts itself(himself), for my purposes unacceptably. The same value  registers as ID both in principal, and in child tables. In child tables is actually ID the document and PARENT_ID, equal ID the parent document. This method breaks hierarchy of documents. So it is necessary any variant another. Perhaps someone cached values  in a collection?

7

Re: and a multitabular insertion

Type such?

declare
sq sys.odcinumberlist;
begin
select sequence.nextval
bulk collect into sq
from dual
connect by level <=10;
for i in (select * from table (cast (sq as sys.odcinumberlist))) loop
dbms_output.put_line (i.column_value);
end loop;
end;

8

Re: and a multitabular insertion

ultrasonic7 wrote:

But it strange conducts itself(himself), for my purposes unacceptably. The same value  registers as ID both in principal, and in child tables.

He - INSERT ALL - behaves as  3.2 Sequence Pseudocolumns :

Oracle Doc wrote:

For each INSERT... [ALL | FIRST] statement (multitable insert). A multitable insert is considered a single SQL statement. Therefore, a reference to the NEXTVAL of a sequence will increase the sequence only once for each input record coming from the SELECT portion of the statement. If NEXTVAL is specified more than once in any part of the INSERT... [ALL | FIRST] statement, then the value will be the same for all insert branches, regardless of how often a given record might be inserted.
For each input row in a multitable INSERT ALL statement. NEXTVAL is incremented once for each row returned by the subquery, regardless of how many occurrences of the insert_into_clause map to each row.

SY.

9

Re: and a multitabular insertion

ultrasonic7;
-Show the rules of formation from one sequence, for example, ID for 3 tables
-Or use for child tables the sequences
-Or do not specify them for child values, let from triggers undertake
And on an example it is possible to show that does not arrange

insert all
when (gid> =:b1) then into top_10 (dt, id, someattr) values (dt, S01_SEQ.NEXTVAL, someattr)
when (gid>:b2) then into totals (dt, id, parent_id) values (dt, S02_SEQ.NEXTVAL, S01_SEQ.NEXTVAL)
when (gid =:b3) then into totalx (dt, id, parent_id) values (dt, S03_SEQ.NEXTVAL, S01_SEQ.NEXTVAL)
------------------ Test data -
WITH t AS (SELECT...
FROM dual CONNECT BY LEVEL <101)
-------------------------------------
select...

10

Re: and a multitabular insertion

Though and enough time transited, I will unsubscribe) All councils took into consideration. Led a series of experiments. Including created for child tables triggers on an insertion, but afterwards refused this idea since the logic of operation is spread on several objects of a DB. Now the packet works on , there are already filled tables and consequently to pass to usage for child tables of the sequences will be specific in this case  since it is a lot of child tables. For everyone  it will be necessary to calculate start value, and the. Though as a whole the idea is quite good enough and originally I and wanted to make - for tables of the first nesting level one sequence, for tables of the second level - another etc. Therefore has been decided to manage with small losses and to apply that council which offered MazoHist, only in the simplified type. More precisely to use properties most .