indexing - insert whole table types in Oracle into database without index -

-

i have new question must solved! had created table type

type ebrbktable table of ebtdccrbk%rowtype index pls_integer; rbktable        ebrbktable;

and insert data table following statements

rbktable(inserttable).bdadduserid             := 'ft_rbk_tdcc';

the last insert following statement

forall in 1..inserttable - 1 insert ebtdccrbk values rbatable(i);

i ask whether there ways insert type once without count(i)

thanks!

a forall statement not loop such.

what send elements of array sql engine in 1 go, enables sql insert rows without having go pl/sql engine more data.

in other words forall removes context switching have in regular loop.

we can show simple trace. consider: 

sql> alter session set sql_trace=true;  session altered.  sql> declare   2    type ebrbktable table of ebtdccrbk%rowtype index pls_integer;   3    rbktable        ebrbktable;   4  begin   5   6    idx in 1..100000   7    loop   8      rbktable(idx).bdadduserid := dbms_random.string('a', 10);   9    end loop;  10  11    forall idx in 1..rbktable.count  12    insert ebtdccrbk values rbktable(idx);  13    commit;  14  15  end;  16  /

in sql trace see:

insert ebtdccrbk values  (:b1 )   call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- parse        1      0.00       0.00          0          0          0           0 execute      1      0.07       0.07          1        724       3066      100000 fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2      0.07       0.07          1        724       3066      100000

vs regluar loop:

sql> alter session set sql_trace=true;  session altered.  sql> declare   2    type ebrbktable table of ebtdccrbk%rowtype index pls_integer;   3    rbktable        ebrbktable;   4  begin   5   6    idx in 1..100000   7    loop   8      rbktable(idx).bdadduserid := dbms_random.string('a', 10);   9    end loop;  10  11    idx in 1..rbktable.count  12    loop  13      insert ebtdccrbk values rbktable(idx);  14    end loop;  15    commit;  16  17  end;  18  /  pl/sql procedure completed.  sql> alter session set sql_trace=false;

and trace file shows:

insert ebtdccrbk values  (:b1 )   call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- parse        1      0.00       0.00          0          0          0           0 execute 100000      3.33       3.24          1        689     104216      100000 fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total   100001      3.33       3.24          1        689     104216      100000

notice trace file in forall version had 1 execute, i.e. sql engine did work in 1 hit. in for loop variant, sql engine had 100k executes, , used lot more cpu same job (as had lot of single operations , session context switching pl/sql->sql each inserted row.


Comments

Post a Comment

Popular posts from this blog

Hatching array of circles in AutoCAD using c# -

-
i beginner in autocad plugins .i have created array of circle using following code. now, have hatch circles in array .how that??how hatch circles @ once? moreover, how can return hatch method , use hatch objects.sorry language.thanks in advance. [assembly: commandclass(typeof(beamsection.class1))] namespace beamsection { public class class1 { //class1 obj = new class1(); [commandmethod("beamatsupport")] public void addlightweightpolyline() { // current document , database document acdoc = application.documentmanager.mdiactivedocument; database accurdb = acdoc.database; promptpointresult ppr = acdoc.editor.getpoint("\nselect starting point "); var ucs = acdoc.editor.currentusercoordinatesystem; point3d startingpt = ppr.value.transformby(ucs); list<double> radius = new list<double>(); radius.add(0.5);
Read more

ios - UITEXTFIELD InputView Uipicker not working in swift -

-
hello trying pop uipickerview view programmatically when user clicks on textfield. have tried doesn't doing anything. isn't working. nothing happening when click textfield class userprofiletableviewcontroller: uitableviewcontroller,uitextfielddelegate,uipickerviewdelegate,uipickerviewdatasource { var itempicker: uipickerview! = uipickerview() @iboutlet weak var gendertxtfield: uitextfield! var gender = ["male","female"] override func viewdidload() { super.viewdidload() gendertxtfield.delegate = self itempicker!.delegate = self itempicker!.datasource = self itempicker!.backgroundcolor = uicolor.blackcolor() self.gendertxtfield.inputview = itempicker } func numberofcomponentsinpickerview(pickerview: uipickerview) -> int{ return 1 } // returns # of rows in each component.. func pickerview(pickerview: uipickerview, numberofrowsincomponent component:
Read more

Python Pig Latin Translator -

-
so have been working on @ codecademy.com while trying learn python. don't know if blind , can't see why won't work or what. here's code won't let me use. function print variable if had typed in , print "empty" if didn't type anything. print "welcome english pig latin translator!" original = raw_input("what's name?") if len(original) > 0 print original else print "empty" if len(original) > 0 else the above lines contains syntax errors. try: if len(original) > 0: else: note added colons, ( : ).
Read more