Oracle Database - Multi Row INSERT、バインド変数を使うとリテラル値を使う場合では見える景色が変わるんだよね #2 Tweet
Previously on Mac De Oracle
前回は、
Oracle Database - Multi Row INSERT、バインド変数を使うと、リテラル値を使う場合では見える景色が変わるんだよね #1 - バグなのか現時点の仕様なのか?
でした。本題からちょっと脱線気味でしたがw、今日は、Multi Row INSERT、バインド変数を使うとリテラル値を使う場合では見える景色が変わるんだよね。の本題。（やっとw）
前々回、帰ってきた！ 標準はあるにはあるが癖の多いSQL #22 - Multi Row INSERTのようにリテラル値のままでMulti row Insertしちゃうと言う無茶なことやっていると、いくらメモリ(メモリだけでもないけども)があったも足らなくなるよなーーー。というのはイメージできたと思うので、今日はバインド変数化すると景色がどう変わるか（良い方に）見てみましょう。
Use The Bind Variable, Luke!
メモリ消費も抑えられますし。ハードパースコストも下げられますし。:)
特にOracle Databaseのようにハードパースなど比較的重量級かつカーソルシェアリングなど含むキャッシュ機能満載のRDBMSでは絶大な効果を発揮します。はい。
リテラル値のままのmulti row insertとバインド変数化した場合のPGA周り含めた景色、どうかわるでしょうねぇ。。。。（そんなことわかっとる！ と言う方は見なくて良いです ;)
前回色々ハマりながら作ってたw 無名PL/SQLブロックを含むスクリプトを利用（Geminiくんにもお手伝いしてもらいw）して、ローカル表（リモート表の話は別ネタにてw）へアクセスして、バインド変数有無でそのような景色の違いがでるかを見てみましょう。
23aiでサポートされたMulti row Insert構文で、10行、100行、1000行まとめてインサートする文のPGA/UGAの最大サイズを比較。
バインド変数を利用せず、1000行まとめたMulti row Insertでは、SQL文をCLOBで保持した影響で、PGAから溢れて、一時LOBを利用した影響で、log write/readが発生。PGA/UGAは多少減り、変わり一時表領域へのIO増加ということからパース時間が随分伸びたことがわかります！
バインド変数を利用した場合とのPGA/UGA消費サイズ、および、パースタイムは大幅に軽減されていて、各種リソースにも優しくなっているのが見えますよね！(ハードパース回数でも一目瞭然)
あと、傾向として、100行毎ぐらいにまとめるのが全体的にリーズナブルな傾向はありますよね。（この前後がリーズナブルって肌感覚とも一致してるんですよね。何行ぐらいにしようかなー。と探る時もだいだいこれぐらいの前後一桁ずらしてってことは多いかな）
パース時間とPGA/UGAの最大サイズ比較
以下、ログの一部を載せておきます。
Multi row Insertでバインド変数を利用しない（リテラル値） - 10行ごと、100,000行登録(10 row Insert * 10,000回)
SCOTT@localhost:1521/freepdb1> @multi_row_insert_nobind 100000 10
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
201 CPU used by this session 8
201 CPU used when call started 7
201 SQL*Net roundtrips to/from client 9
...略...
201 execute count 1383
...略...
201 parse count (hard) 144
201 parse count (total) 322
201 parse time cpu 7
201 parse time elapsed 8
...略...
201 session pga memory 4385784
201 session pga memory max 5189280
201 session uga memory 2035656
201 session uga memory max 3119464
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:09.98
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
201 CPU used by this session 1003
201 CPU used when call started 1003
201 SQL*Net roundtrips to/from client 16
...略...
201 execute count 21481
...略...
201 parse count (hard) 10149
201 parse count (total) 20348
201 parse time cpu 854
201 parse time elapsed 883
...略...
201 session pga memory 4647928
201 session pga memory max 5189280
201 session uga memory 2232024
201 session uga memory max 3119464
...略...
201 user commits 10000
COUNT(1)
----------
100000
SEGMENT_NAME MB
------------------------------ ----------
MROWS_INS_TAB 45
表が切り捨てられました。
Multi row Insertでバインド変数を利用しない（リテラル値） - 100行ごと、100,000行登録(100 row Insert * 1,000回)
SCOTT@localhost:1521/freepdb1> @multi_row_insert_nobind 100000 100
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
48 CPU used by this session 3
48 CPU used when call started 2
48 SQL*Net roundtrips to/from client 9
...略...
48 execute count 410
...略...
48 parse count (hard) 52
48 parse count (total) 204
48 parse time cpu 3
48 parse time elapsed 3
...略...
48 session pga memory 3681952
48 session pga memory max 3681952
48 session uga memory 1577072
48 session uga memory max 1642592
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:08.34
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
48 CPU used by this session 827
48 CPU used when call started 827
48 SQL*Net roundtrips to/from client 16
...略...
48 execute count 2465
...略...
48 parse count (hard) 1060
48 parse count (total) 2227
48 parse time cpu 695
48 parse time elapsed 733
...略...
48 session pga memory 207547384
48 session pga memory max 207547384
48 session uga memory 201509624
48 session uga memory max 201509624
...略...
48 user commits 1000
COUNT(1)
----------
100000
...略...
Multi row Insertでバインド変数を利用しない（リテラル値） - 1,000行ごと、100,000行登録(1000 row Insert * 100回)
SCOTT@localhost:1521/freepdb1> @multi_row_insert_nobind 100000 1000
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
48 CPU used by this session 6
48 CPU used when call started 6
48 SQL*Net roundtrips to/from client 9
...略...
48 execute count 1357
...略...
48 parse count (hard) 144
48 parse count (total) 329
48 parse time cpu 5
48 parse time elapsed 8
...略...
48 session pga memory 4254712
48 session pga memory max 5123744
48 session uga memory 1970280
48 session uga memory max 3054144
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:34.79
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
48 CPU used by this session 3481
48 CPU used when call started 3481
48 SQL*Net roundtrips to/from client 16
...略...
48 execute count 1677
48 lob reads 831888
48 lob writes 415994
48 lob writes unaligned 415994
...略...
48 parse count (hard) 252
48 parse count (total) 557
48 parse time cpu 3015
48 parse time elapsed 3019
...略...
48 session pga memory 66841592
48 session pga memory max 109177848
48 session uga memory 64872848
48 session uga memory max 69655680
...略...
48 user commits 100
COUNT(1)
----------
100000
...略...
Multi row Insertでバインド変数を利用 - 10行ごと、100,000行登録(10 row Insert * 10,000回)
SCOTT@localhost:1521/freepdb1> @multi_row_insert_bind_10 100000 10
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
48 CPU used by this session 6
48 CPU used when call started 6
48 SQL*Net roundtrips to/from client 9
...略...
48 execute count 1383
...略...
48 parse count (hard) 144
48 parse count (total) 322
48 parse time cpu 5
48 parse time elapsed 9
...略...
48 session pga memory 4385784
48 session pga memory max 5189280
48 session uga memory 2035760
48 session uga memory max 3119544
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.97
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
48 CPU used by this session 99
48 CPU used when call started 99
48 SQL*Net roundtrips to/from client 16
...略...
48 execute count 11535
...略...
48 parse count (hard) 153
48 parse count (total) 356
48 parse time cpu 6
48 parse time elapsed 9
...略...
48 session pga memory 3533816
48 session pga memory max 5189280
48 session uga memory 2101240
48 session uga memory max 3119544
...略...
48 user commits 10000
COUNT(1)
----------
100000
...略...
Multi row Insertでバインド変数を利用 - 100行ごと、100,000行登録(100 row Insert * 1,000回)
SCOTT@localhost:1521/freepdb1> @multi_row_insert_bind_100 100000 100
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
176 CPU used by this session 5
176 CPU used when call started 5
176 SQL*Net roundtrips to/from client 6
...略...
176 execute count 831
...略...
176 parse count (hard) 107
176 parse count (total) 229
176 parse time cpu 6
176 parse time elapsed 3
...略...
176 session pga memory 4123640
176 session pga memory max 5123744
176 session uga memory 1904800
176 session uga memory max 3119472
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.77
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
176 CPU used by this session 82
176 CPU used when call started 82
176 SQL*Net roundtrips to/from client 10
...略...
176 execute count 2109
...略...
176 parse count (hard) 130
176 parse count (total) 289
176 parse time cpu 9
176 parse time elapsed 5
...略...
176 session pga memory 3468280
176 session pga memory max 12119032
176 session uga memory 2101240
176 session uga memory max 3119472
...略...
176 user commits 1000
COUNT(1)
----------
100000
...略...
Multi row Insertでバインド変数を利用 - 1000行ごと、100,000行登録(1000 row Insert * 100回)
SCOTT@localhost:1521/freepdb1> @multi_row_insert_bind_1000 100000 1000
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
201 CPU used by this session 1
201 CPU used when call started 1
201 SQL*Net roundtrips to/from client 6
...略...
201 execute count 10
...略...
201 parse count (total) 11
...略...
201 session pga memory 1715872
201 session pga memory max 1715872
201 session uga memory 529464
201 session uga memory max 594992
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:01.30
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
201 CPU used by this session 130
201 CPU used when call started 130
201 SQL*Net roundtrips to/from client 10
...略...
201 execute count 115
...略...
201 parse count (hard) 2
201 parse count (total) 17
201 parse time cpu 42
201 parse time elapsed 42
...略...
201 session pga memory 3730424
201 session pga memory max 48049824
201 session uga memory 2232032
201 session uga memory max 2297536
...略...
201 user commits 100
COUNT(1)
----------
100000
...略...
Use The Bind Variable, Luke!
サーバーの電力消費節約にもなるかな。。
では、また。
いつものように、今回使ったスクリプトとかコードとかは以下。(めんどくさくなって、Geminiくんにお願いしたりしてますがww)
show_mystats.sql
SELECT
s.sid,
n.name,
s.value
FROM
v$mystat s
INNER JOIN v$statname n
ON
s.statistic# = n.statistic#
WHERE
s.value > 0
AND (
n.name LIKE '%memory%'
OR n.name LIKE '%CPU%'
OR n.name LIKE '%I/O%'
OR n.name LIKE '%write%'
OR n.name LIKE '%read%'
OR n.name LIKE 'redo%'
OR n.name LIKE 'SQL*Net%'
OR n.name LIKE '%commit%'
OR n.name LIKE 'execute count'
OR n.name LIKE 'parse%'
)
ORDER BY
n.name;
show_mrows_ins_tab_size.sql
select
segment_name
, bytes/1024/1024 as "MB"
from
dba_segments
where
owner='SCOTT'
and segment_name = upper('mrows_ins_tab')
/
multi_row_insert_nobind.sql
set veri off
@show_mystats
DECLARE
c_max_rows CONSTANT NUMBER := &1;
c_rows_per_stmt CONSTANT NUMBER := &2;
--
sql_stmt CLOB := EMPTY_CLOB();
start_row NUMBER := 1;
end_row NUMBER := c_rows_per_stmt;
--
BEGIN
FOR k IN 1..c_max_rows / c_rows_per_stmt LOOP
sql_stmt := 'INSERT INTO mrows_ins_tab VALUES';
--
FOR i IN start_row..end_row LOOP
sql_stmt :=
sql_stmt
|| CASE WHEN i - (c_rows_per_stmt * (k - 1)) > 1 THEN ', ' END
|| '('
|| TO_CHAR(i)
|| ', null, null, null, null, null, null, null, ''' || LPAD(TO_CHAR(i),373,'x') || ''')';
END LOOP;
--
start_row := start_row + c_rows_per_stmt;
end_row := end_row + c_rows_per_stmt;
--
EXECUTE IMMEDIATE sql_stmt;
COMMIT;
sql_stmt := EMPTY_CLOB();
END LOOP;
END;
/
set veri on
UNDEFINE 1
UNDEFINE 2
@show_mystats
SELECT COUNT(1) FROM scott.mrows_ins_tab
/
@show_mrows_ins_tab_size
truncate table scott.mrows_ins_tab
/
multi_row_insert_bind_10.sql
set veri off
SET SERVEROUTPUT ON
@show_mystats
DECLARE
c_max_rows CONSTANT NUMBER := &1;
c_rows_per_stmt CONSTANT NUMBER := &2;
--
sql_stmt CLOB := EMPTY_CLOB();
start_row NUMBER := 1;
end_row NUMBER := c_rows_per_stmt;
n NUMBER;
o NUMBER;
TYPE c1_type IS VARRAY(c_rows_per_stmt) OF NUMBER;
TYPE c8_type IS VARRAY(c_rows_per_stmt) OF VARCHAR2(500);
c1 c1_type := c1_type();
c8 c8_type := c8_type();
m NUMBER;
--
BEGIN
--
c1.extend(c_rows_per_stmt);
c8.extend(c_rows_per_stmt);
--
sql_stmt := 'INSERT INTO mrows_ins_tab VALUES';
FOR i IN 1..c_rows_per_stmt LOOP
--
sql_stmt :=
sql_stmt
|| CASE WHEN i > 1 THEN ', ' END
|| '('
|| ':c1'||TO_CHAR(i)||', null, null, null, null, null, null, null, :c8'||TO_CHAR(i)|| ')';
END LOOP;
sql_stmt := sql_stmt || ';';
--
FOR l IN 1..c_max_rows / c_rows_per_stmt LOOP
FOR j IN start_row..end_row LOOP
o := l * c_rows_per_stmt;
n := MOD(j, c_rows_per_stmt);
m := CASE WHEN MOD(l, c_rows_per_stmt) > c_rows_per_stmt
THEN j - o
ELSE CASE WHEN n = 0
THEN c_rows_per_stmt
ELSE n
END
END;
c1(m) := j;
c8(m) := LPAD(TO_CHAR(j),373,'x');
END LOOP;
start_row := start_row + c_rows_per_stmt;
end_row := end_row + c_rows_per_stmt;
--
EXECUTE IMMEDIATE sql_stmt
USING
c1(1), c8(1), c1(2), c8(2), c1(3), c8(3), c1(4), c8(4), c1(5), c8(5), c1(6), c8(6), c1(7), c8(7), c1(8), c8(8), c1(9), c8(9), c1(10), c8(10)
;
COMMIT;
END LOOP;
END;
/
SET SERVEROUTPUT OFF
set veri on
UNDEFINE 1
UNDEFINE 2
@show_mystats
SELECT COUNT(1) FROM scott.mrows_ins_tab
/
@show_mrows_ins_tab_size
truncate table scott.mrows_ins_tab
/
multi_row_insert_bind_100.sql
set veri off
@show_mystats
DECLARE
c_max_rows CONSTANT NUMBER := &1;
c_rows_per_stmt CONSTANT NUMBER := &2;
--
sql_stmt CLOB := EMPTY_CLOB();
start_row NUMBER := 1;
end_row NUMBER := c_rows_per_stmt;
n NUMBER;
o NUMBER;
TYPE c1_type IS VARRAY(c_rows_per_stmt) OF NUMBER;
TYPE c8_type IS VARRAY(c_rows_per_stmt) OF VARCHAR2(500);
c1 c1_type := c1_type();
c8 c8_type := c8_type();
m NUMBER;
--
BEGIN
--
c1.extend(c_rows_per_stmt);
c8.extend(c_rows_per_stmt);
--
sql_stmt := 'INSERT INTO mrows_ins_tab VALUES';
FOR i IN 1..c_rows_per_stmt LOOP
--
sql_stmt :=
sql_stmt
|| CASE WHEN i > 1 THEN ', ' END
|| '('
|| ':c1'||TO_CHAR(i)||', null, null, null, null, null, null, null, :c8'||TO_CHAR(i)|| ')';
END LOOP;
sql_stmt := sql_stmt || ';';
--
FOR l IN 1..c_max_rows / c_rows_per_stmt LOOP
FOR j IN start_row..end_row LOOP
o := l * c_rows_per_stmt;
n := MOD(j, c_rows_per_stmt);
m := CASE WHEN MOD(l, c_rows_per_stmt) > c_rows_per_stmt
THEN j - o
ELSE CASE WHEN n = 0
THEN c_rows_per_stmt
ELSE n
END
END;
c1(m) := j;
c8(m) := LPAD(TO_CHAR(j),373,'x');
END LOOP;
start_row := start_row + c_rows_per_stmt;
end_row := end_row + c_rows_per_stmt;
--
EXECUTE IMMEDIATE sql_stmt
USING
c1(1) ,c8(1) ,c1(2) ,c8(2) ,c1(3) ,c8(3) ,c1(4) ,c8(4) ,c1(5) ,c8(5) ,c1(6) ,c8(6) ,c1(7) ,c8(7) ,c1(8) ,c8(8) ,c1(9) ,c8(9) ,c1(10) ,c8(10)
, c1(11) ,c8(11) ,c1(12) ,c8(12) ,c1(13) ,c8(13) ,c1(14) ,c8(14) ,c1(15) ,c8(15) ,c1(16) ,c8(16) ,c1(17) ,c8(17) ,c1(18) ,c8(18) ,c1(19) ,c8(19) ,c1(20) ,c8(20)
, c1(21) ,c8(21) ,c1(22) ,c8(22) ,c1(23) ,c8(23) ,c1(24) ,c8(24) ,c1(25) ,c8(25) ,c1(26) ,c8(26) ,c1(27) ,c8(27) ,c1(28) ,c8(28) ,c1(29) ,c8(29) ,c1(30) ,c8(30)
, c1(31) ,c8(31) ,c1(32) ,c8(32) ,c1(33) ,c8(33) ,c1(34) ,c8(34) ,c1(35) ,c8(35) ,c1(36) ,c8(36) ,c1(37) ,c8(37) ,c1(38) ,c8(38) ,c1(39) ,c8(39) ,c1(40) ,c8(40)
, c1(41) ,c8(41) ,c1(42) ,c8(42) ,c1(43) ,c8(43) ,c1(44) ,c8(44) ,c1(45) ,c8(45) ,c1(46) ,c8(46) ,c1(47) ,c8(47) ,c1(48) ,c8(48) ,c1(49) ,c8(49) ,c1(50) ,c8(50)
, c1(51) ,c8(51) ,c1(52) ,c8(52) ,c1(53) ,c8(53) ,c1(54) ,c8(54) ,c1(55) ,c8(55) ,c1(56) ,c8(56) ,c1(57) ,c8(57) ,c1(58) ,c8(58) ,c1(59) ,c8(59) ,c1(60) ,c8(60)
, c1(61) ,c8(61) ,c1(62) ,c8(62) ,c1(63) ,c8(63) ,c1(64) ,c8(64) ,c1(65) ,c8(65) ,c1(66) ,c8(66) ,c1(67) ,c8(67) ,c1(68) ,c8(68) ,c1(69) ,c8(69) ,c1(70) ,c8(70)
, c1(71) ,c8(71) ,c1(72) ,c8(72) ,c1(73) ,c8(73) ,c1(74) ,c8(74) ,c1(75) ,c8(75) ,c1(76) ,c8(76) ,c1(77) ,c8(77) ,c1(78) ,c8(78) ,c1(79) ,c8(79) ,c1(80) ,c8(80)
, c1(81) ,c8(81) ,c1(82) ,c8(82) ,c1(83) ,c8(83) ,c1(84) ,c8(84) ,c1(85) ,c8(85) ,c1(86) ,c8(86) ,c1(87) ,c8(87) ,c1(88) ,c8(88) ,c1(89) ,c8(89) ,c1(90) ,c8(90)
, c1(91) ,c8(91) ,c1(92) ,c8(92) ,c1(93) ,c8(93) ,c1(94) ,c8(94) ,c1(95) ,c8(95) ,c1(96) ,c8(96) ,c1(97) ,c8(97) ,c1(98) ,c8(98) ,c1(99) ,c8(99) ,c1(100) ,c8(100)
;
COMMIT;
END LOOP;
END;
/
set veri on
UNDEFINE 1
UNDEFINE 2
@show_mystats
SELECT COUNT(1) FROM mrows_ins_tab
/
@show_mrows_ins_tab_size
truncate table scott.mrows_ins_tab
/
multi_row_insert_bind_1000.sql
set veri off
@show_mystats
DECLARE
c_max_rows CONSTANT NUMBER := &1;
c_rows_per_stmt CONSTANT NUMBER := &2
; --
sql_stmt CLOB := EMPTY_CLOB();
start_row NUMBER := 1;
end_row NUMBER := c_rows_per_stmt;
n NUMBER;
o NUMBER;
TYPE c1_type IS VARRAY(c_rows_per_stmt) OF NUMBER;
TYPE c8_type IS VARRAY(c_rows_per_stmt) OF VARCHAR2(500);
c1 c1_type := c1_type();
c8 c8_type := c8_type();
m NUMBER;
--
BEGIN
--
c1.extend(c_rows_per_stmt);
c8.extend(c_rows_per_stmt);
--
sql_stmt := 'INSERT /*+ REMOTE_MAPPED(link2scott) */ INTO mrows_ins_tab VALUES';
FOR i IN 1..c_rows_per_stmt LOOP
--
sql_stmt :=
sql_stmt
|| CASE WHEN i > 1 THEN ', ' END
|| '('
|| ':c1'||TO_CHAR(i)||', null, null, null, null, null, null, null, :c8'||TO_CHAR(i)|| ')';
END LOOP;
sql_stmt := sql_stmt || ';';
--
FOR l IN 1..c_max_rows / c_rows_per_stmt LOOP
FOR j IN start_row..end_row LOOP
o := l * c_rows_per_stmt;
n := MOD(j, c_rows_per_stmt);
m := CASE WHEN MOD(l, c_rows_per_stmt) > c_rows_per_stmt
THEN j - o
ELSE CASE WHEN n = 0
THEN c_rows_per_stmt
ELSE n
END
END;
c1(m) := j;
c8(m) := LPAD(TO_CHAR(j),373,'x');
END LOOP;
start_row := start_row + c_rows_per_stmt;
end_row := end_row + c_rows_per_stmt;
--
EXECUTE IMMEDIATE sql_stmt
USING
c1(1) ,c8(1) ,c1(2) ,c8(2) ,c1(3) ,c8(3) ,c1(4) ,c8(4) ,c1(5) ,c8(5) ,c1(6) ,c8(6) ,c1(7) ,c8(7) ,c1(8) ,c8(8) ,c1(9) ,c8(9) ,c1(10) ,c8(10)
, c1(11) ,c8(11) ,c1(12) ,c8(12) ,c1(13) ,c8(13) ,c1(14) ,c8(14) ,c1(15) ,c8(15) ,c1(16) ,c8(16) ,c1(17) ,c8(17) ,c1(18) ,c8(18) ,c1(19) ,c8(19) ,c1(20) ,c8(20)
, c1(21) ,c8(21) ,c1(22) ,c8(22) ,c1(23) ,c8(23) ,c1(24) ,c8(24) ,c1(25) ,c8(25) ,c1(26) ,c8(26) ,c1(27) ,c8(27) ,c1(28) ,c8(28) ,c1(29) ,c8(29) ,c1(30) ,c8(30)
, c1(31) ,c8(31) ,c1(32) ,c8(32) ,c1(33) ,c8(33) ,c1(34) ,c8(34) ,c1(35) ,c8(35) ,c1(36) ,c8(36) ,c1(37) ,c8(37) ,c1(38) ,c8(38) ,c1(39) ,c8(39) ,c1(40) ,c8(40)
, c1(41) ,c8(41) ,c1(42) ,c8(42) ,c1(43) ,c8(43) ,c1(44) ,c8(44) ,c1(45) ,c8(45) ,c1(46) ,c8(46) ,c1(47) ,c8(47) ,c1(48) ,c8(48) ,c1(49) ,c8(49) ,c1(50) ,c8(50)
, c1(51) ,c8(51) ,c1(52) ,c8(52) ,c1(53) ,c8(53) ,c1(54) ,c8(54) ,c1(55) ,c8(55) ,c1(56) ,c8(56) ,c1(57) ,c8(57) ,c1(58) ,c8(58) ,c1(59) ,c8(59) ,c1(60) ,c8(60)
, c1(61) ,c8(61) ,c1(62) ,c8(62) ,c1(63) ,c8(63) ,c1(64) ,c8(64) ,c1(65) ,c8(65) ,c1(66) ,c8(66) ,c1(67) ,c8(67) ,c1(68) ,c8(68) ,c1(69) ,c8(69) ,c1(70) ,c8(70)
, c1(71) ,c8(71) ,c1(72) ,c8(72) ,c1(73) ,c8(73) ,c1(74) ,c8(74) ,c1(75) ,c8(75) ,c1(76) ,c8(76) ,c1(77) ,c8(77) ,c1(78) ,c8(78) ,c1(79) ,c8(79) ,c1(80) ,c8(80)
, c1(81) ,c8(81) ,c1(82) ,c8(82) ,c1(83) ,c8(83) ,c1(84) ,c8(84) ,c1(85) ,c8(85) ,c1(86) ,c8(86) ,c1(87) ,c8(87) ,c1(88) ,c8(88) ,c1(89) ,c8(89) ,c1(90) ,c8(90)
, c1(91) ,c8(91) ,c1(92) ,c8(92) ,c1(93) ,c8(93) ,c1(94) ,c8(94) ,c1(95) ,c8(95) ,c1(96) ,c8(96) ,c1(97) ,c8(97) ,c1(98) ,c8(98) ,c1(99) ,c8(99),c1(100),c8(100)
, c1(101),c8(101),c1(102),c8(102),c1(103),c8(103),c1(104),c8(104),c1(105),c8(105),c1(106),c8(106),c1(107),c8(107),c1(108),c8(108),c1(109),c8(109),c1(110),c8(110)
, c1(111),c8(111),c1(112),c8(112),c1(113),c8(113),c1(114),c8(114),c1(115),c8(115),c1(116),c8(116),c1(117),c8(117),c1(118),c8(118),c1(119),c8(119),c1(120),c8(120)
, c1(121),c8(121),c1(122),c8(122),c1(123),c8(123),c1(124),c8(124),c1(125),c8(125),c1(126),c8(126),c1(127),c8(127),c1(128),c8(128),c1(129),c8(129),c1(130),c8(130)
, c1(131),c8(131),c1(132),c8(132),c1(133),c8(133),c1(134),c8(134),c1(135),c8(135),c1(136),c8(136),c1(137),c8(137),c1(138),c8(138),c1(139),c8(139),c1(140),c8(140)
, c1(141),c8(141),c1(142),c8(142),c1(143),c8(143),c1(144),c8(144),c1(145),c8(145),c1(146),c8(146),c1(147),c8(147),c1(148),c8(148),c1(149),c8(149),c1(150),c8(150)
, c1(151),c8(151),c1(152),c8(152),c1(153),c8(153),c1(154),c8(154),c1(155),c8(155),c1(156),c8(156),c1(157),c8(157),c1(158),c8(158),c1(159),c8(159),c1(160),c8(160)
, c1(161),c8(161),c1(162),c8(162),c1(163),c8(163),c1(164),c8(164),c1(165),c8(165),c1(166),c8(166),c1(167),c8(167),c1(168),c8(168),c1(169),c8(169),c1(170),c8(170)
, c1(171),c8(171),c1(172),c8(172),c1(173),c8(173),c1(174),c8(174),c1(175),c8(175),c1(176),c8(176),c1(177),c8(177),c1(178),c8(178),c1(179),c8(179),c1(180),c8(180)
, c1(181),c8(181),c1(182),c8(182),c1(183),c8(183),c1(184),c8(184),c1(185),c8(185),c1(186),c8(186),c1(187),c8(187),c1(188),c8(188),c1(189),c8(189),c1(190),c8(190)
, c1(191),c8(191),c1(192),c8(192),c1(193),c8(193),c1(194),c8(194),c1(195),c8(195),c1(196),c8(196),c1(197),c8(197),c1(198),c8(198),c1(199),c8(199),c1(200),c8(200)
, c1(201),c8(201),c1(202),c8(202),c1(203),c8(203),c1(204),c8(204),c1(205),c8(205),c1(206),c8(206),c1(207),c8(207),c1(208),c8(208),c1(209),c8(209),c1(210),c8(210)
, c1(211),c8(211),c1(212),c8(212),c1(213),c8(213),c1(214),c8(214),c1(215),c8(215),c1(216),c8(216),c1(217),c8(217),c1(218),c8(218),c1(219),c8(219),c1(220),c8(220)
, c1(221),c8(221),c1(222),c8(222),c1(223),c8(223),c1(224),c8(224),c1(225),c8(225),c1(226),c8(226),c1(227),c8(227),c1(228),c8(228),c1(229),c8(229),c1(230),c8(230)
, c1(231),c8(231),c1(232),c8(232),c1(233),c8(233),c1(234),c8(234),c1(235),c8(235),c1(236),c8(236),c1(237),c8(237),c1(238),c8(238),c1(239),c8(239),c1(240),c8(240)
, c1(241),c8(241),c1(242),c8(242),c1(243),c8(243),c1(244),c8(244),c1(245),c8(245),c1(246),c8(246),c1(247),c8(247),c1(248),c8(248),c1(249),c8(249),c1(250),c8(250)
, c1(251),c8(251),c1(252),c8(252),c1(253),c8(253),c1(254),c8(254),c1(255),c8(255),c1(256),c8(256),c1(257),c8(257),c1(258),c8(258),c1(259),c8(259),c1(260),c8(260)
, c1(261),c8(261),c1(262),c8(262),c1(263),c8(263),c1(264),c8(264),c1(265),c8(265),c1(266),c8(266),c1(267),c8(267),c1(268),c8(268),c1(269),c8(269),c1(270),c8(270)
, c1(271),c8(271),c1(272),c8(272),c1(273),c8(273),c1(274),c8(274),c1(275),c8(275),c1(276),c8(276),c1(277),c8(277),c1(278),c8(278),c1(279),c8(279),c1(280),c8(280)
, c1(281),c8(281),c1(282),c8(282),c1(283),c8(283),c1(284),c8(284),c1(285),c8(285),c1(286),c8(286),c1(287),c8(287),c1(288),c8(288),c1(289),c8(289),c1(290),c8(290)
, c1(291),c8(291),c1(292),c8(292),c1(293),c8(293),c1(294),c8(294),c1(295),c8(295),c1(296),c8(296),c1(297),c8(297),c1(298),c8(298),c1(299),c8(299),c1(300),c8(300)
, c1(301),c8(301),c1(302),c8(302),c1(303),c8(303),c1(304),c8(304),c1(305),c8(305),c1(306),c8(306),c1(307),c8(307),c1(308),c8(308),c1(309),c8(309),c1(310),c8(310)
, c1(311),c8(311),c1(312),c8(312),c1(313),c8(313),c1(314),c8(314),c1(315),c8(315),c1(316),c8(316),c1(317),c8(317),c1(318),c8(318),c1(319),c8(319),c1(320),c8(320)
, c1(321),c8(321),c1(322),c8(322),c1(323),c8(323),c1(324),c8(324),c1(325),c8(325),c1(326),c8(326),c1(327),c8(327),c1(328),c8(328),c1(329),c8(329),c1(330),c8(330)
, c1(331),c8(331),c1(332),c8(332),c1(333),c8(333),c1(334),c8(334),c1(335),c8(335),c1(336),c8(336),c1(337),c8(337),c1(338),c8(338),c1(339),c8(339),c1(340),c8(340)
, c1(341),c8(341),c1(342),c8(342),c1(343),c8(343),c1(344),c8(344),c1(345),c8(345),c1(346),c8(346),c1(347),c8(347),c1(348),c8(348),c1(349),c8(349),c1(350),c8(350)
, c1(351),c8(351),c1(352),c8(352),c1(353),c8(353),c1(354),c8(354),c1(355),c8(355),c1(356),c8(356),c1(357),c8(357),c1(358),c8(358),c1(359),c8(359),c1(360),c8(360)
, c1(361),c8(361),c1(362),c8(362),c1(363),c8(363),c1(364),c8(364),c1(365),c8(365),c1(366),c8(366),c1(367),c8(367),c1(368),c8(368),c1(369),c8(369),c1(370),c8(370)
, c1(371),c8(371),c1(372),c8(372),c1(373),c8(373),c1(374),c8(374),c1(375),c8(375),c1(376),c8(376),c1(377),c8(377),c1(378),c8(378),c1(379),c8(379),c1(380),c8(380)
, c1(381),c8(381),c1(382),c8(382),c1(383),c8(383),c1(384),c8(384),c1(385),c8(385),c1(386),c8(386),c1(387),c8(387),c1(388),c8(388),c1(389),c8(389),c1(390),c8(390)
, c1(391),c8(391),c1(392),c8(392),c1(393),c8(393),c1(394),c8(394),c1(395),c8(395),c1(396),c8(396),c1(397),c8(397),c1(398),c8(398),c1(399),c8(399),c1(400),c8(400)
, c1(401),c8(401),c1(402),c8(402),c1(403),c8(403),c1(404),c8(404),c1(405),c8(405),c1(406),c8(406),c1(407),c8(407),c1(408),c8(408),c1(409),c8(409),c1(410),c8(410)
, c1(411),c8(411),c1(412),c8(412),c1(413),c8(413),c1(414),c8(414),c1(415),c8(415),c1(416),c8(416),c1(417),c8(417),c1(418),c8(418),c1(419),c8(419),c1(420),c8(420)
, c1(421),c8(421),c1(422),c8(422),c1(423),c8(423),c1(424),c8(424),c1(425),c8(425),c1(426),c8(426),c1(427),c8(427),c1(428),c8(428),c1(429),c8(429),c1(430),c8(430)
, c1(431),c8(431),c1(432),c8(432),c1(433),c8(433),c1(434),c8(434),c1(435),c8(435),c1(436),c8(436),c1(437),c8(437),c1(438),c8(438),c1(439),c8(439),c1(440),c8(440)
, c1(441),c8(441),c1(442),c8(442),c1(443),c8(443),c1(444),c8(444),c1(445),c8(445),c1(446),c8(446),c1(447),c8(447),c1(448),c8(448),c1(449),c8(449),c1(450),c8(450)
, c1(451),c8(451),c1(452),c8(452),c1(453),c8(453),c1(454),c8(454),c1(455),c8(455),c1(456),c8(456),c1(457),c8(457),c1(458),c8(458),c1(459),c8(459),c1(460),c8(460)
, c1(461),c8(461),c1(462),c8(462),c1(463),c8(463),c1(464),c8(464),c1(465),c8(465),c1(466),c8(466),c1(467),c8(467),c1(468),c8(468),c1(469),c8(469),c1(470),c8(470)
, c1(471),c8(471),c1(472),c8(472),c1(473),c8(473),c1(474),c8(474),c1(475),c8(475),c1(476),c8(476),c1(477),c8(477),c1(478),c8(478),c1(479),c8(479),c1(480),c8(480)
, c1(481),c8(481),c1(482),c8(482),c1(483),c8(483),c1(484),c8(484),c1(485),c8(485),c1(486),c8(486),c1(487),c8(487),c1(488),c8(488),c1(489),c8(489),c1(490),c8(490)
, c1(491),c8(491),c1(492),c8(492),c1(493),c8(493),c1(494),c8(494),c1(495),c8(495),c1(496),c8(496),c1(497),c8(497),c1(498),c8(498),c1(499),c8(499),c1(500),c8(500)
, c1(501),c8(501),c1(502),c8(502),c1(503),c8(503),c1(504),c8(504),c1(505),c8(505),c1(506),c8(506),c1(507),c8(507),c1(508),c8(508),c1(509),c8(509),c1(510),c8(510)
, c1(511),c8(511),c1(512),c8(512),c1(513),c8(513),c1(514),c8(514),c1(515),c8(515),c1(516),c8(516),c1(517),c8(517),c1(518),c8(518),c1(519),c8(519),c1(520),c8(520)
, c1(521),c8(521),c1(522),c8(522),c1(523),c8(523),c1(524),c8(524),c1(525),c8(525),c1(526),c8(526),c1(527),c8(527),c1(528),c8(528),c1(529),c8(529),c1(530),c8(530)
, c1(531),c8(531),c1(532),c8(532),c1(533),c8(533),c1(534),c8(534),c1(535),c8(535),c1(536),c8(536),c1(537),c8(537),c1(538),c8(538),c1(539),c8(539),c1(540),c8(540)
, c1(541),c8(541),c1(542),c8(542),c1(543),c8(543),c1(544),c8(544),c1(545),c8(545),c1(546),c8(546),c1(547),c8(547),c1(548),c8(548),c1(549),c8(549),c1(550),c8(550)
, c1(551),c8(551),c1(552),c8(552),c1(553),c8(553),c1(554),c8(554),c1(555),c8(555),c1(556),c8(556),c1(557),c8(557),c1(558),c8(558),c1(559),c8(559),c1(560),c8(560)
, c1(561),c8(561),c1(562),c8(562),c1(563),c8(563),c1(564),c8(564),c1(565),c8(565),c1(566),c8(566),c1(567),c8(567),c1(568),c8(568),c1(569),c8(569),c1(570),c8(570)
, c1(571),c8(571),c1(572),c8(572),c1(573),c8(573),c1(574),c8(574),c1(575),c8(575),c1(576),c8(576),c1(577),c8(577),c1(578),c8(578),c1(579),c8(579),c1(580),c8(580)
, c1(581),c8(581),c1(582),c8(582),c1(583),c8(583),c1(584),c8(584),c1(585),c8(585),c1(586),c8(586),c1(587),c8(587),c1(588),c8(588),c1(589),c8(589),c1(590),c8(590)
, c1(591),c8(591),c1(592),c8(592),c1(593),c8(593),c1(594),c8(594),c1(595),c8(595),c1(596),c8(596),c1(597),c8(597),c1(598),c8(598),c1(599),c8(599),c1(600),c8(600)
, c1(601),c8(601),c1(602),c8(602),c1(603),c8(603),c1(604),c8(604),c1(605),c8(605),c1(606),c8(606),c1(607),c8(607),c1(608),c8(608),c1(609),c8(609),c1(610),c8(610)
, c1(611),c8(611),c1(612),c8(612),c1(613),c8(613),c1(614),c8(614),c1(615),c8(615),c1(616),c8(616),c1(617),c8(617),c1(618),c8(618),c1(619),c8(619),c1(620),c8(620)
, c1(621),c8(621),c1(622),c8(622),c1(623),c8(623),c1(624),c8(624),c1(625),c8(625),c1(626),c8(626),c1(627),c8(627),c1(628),c8(628),c1(629),c8(629),c1(630),c8(630)
, c1(631),c8(631),c1(632),c8(632),c1(633),c8(633),c1(634),c8(634),c1(635),c8(635),c1(636),c8(636),c1(637),c8(637),c1(638),c8(638),c1(639),c8(639),c1(640),c8(640)
, c1(641),c8(641),c1(642),c8(642),c1(643),c8(643),c1(644),c8(644),c1(645),c8(645),c1(646),c8(646),c1(647),c8(647),c1(648),c8(648),c1(649),c8(649),c1(650),c8(650)
, c1(651),c8(651),c1(652),c8(652),c1(653),c8(653),c1(654),c8(654),c1(655),c8(655),c1(656),c8(656),c1(657),c8(657),c1(658),c8(658),c1(659),c8(659),c1(660),c8(660)
, c1(661),c8(661),c1(662),c8(662),c1(663),c8(663),c1(664),c8(664),c1(665),c8(665),c1(666),c8(666),c1(667),c8(667),c1(668),c8(668),c1(669),c8(669),c1(670),c8(670)
, c1(671),c8(671),c1(672),c8(672),c1(673),c8(673),c1(674),c8(674),c1(675),c8(675),c1(676),c8(676),c1(677),c8(677),c1(678),c8(678),c1(679),c8(679),c1(680),c8(680)
, c1(681),c8(681),c1(682),c8(682),c1(683),c8(683),c1(684),c8(684),c1(685),c8(685),c1(686),c8(686),c1(687),c8(687),c1(688),c8(688),c1(689),c8(689),c1(690),c8(690)
, c1(691),c8(691),c1(692),c8(692),c1(693),c8(693),c1(694),c8(694),c1(695),c8(695),c1(696),c8(696),c1(697),c8(697),c1(698),c8(698),c1(699),c8(699),c1(700),c8(700)
, c1(701),c8(701),c1(702),c8(702),c1(703),c8(703),c1(704),c8(704),c1(705),c8(705),c1(706),c8(706),c1(707),c8(707),c1(708),c8(708),c1(709),c8(709),c1(710),c8(710)
, c1(711),c8(711),c1(712),c8(712),c1(713),c8(713),c1(714),c8(714),c1(715),c8(715),c1(716),c8(716),c1(717),c8(717),c1(718),c8(718),c1(719),c8(719),c1(720),c8(720)
, c1(721),c8(721),c1(722),c8(722),c1(723),c8(723),c1(724),c8(724),c1(725),c8(725),c1(726),c8(726),c1(727),c8(727),c1(728),c8(728),c1(729),c8(729),c1(730),c8(730)
, c1(731),c8(731),c1(732),c8(732),c1(733),c8(733),c1(734),c8(734),c1(735),c8(735),c1(736),c8(736),c1(737),c8(737),c1(738),c8(738),c1(739),c8(739),c1(740),c8(740)
, c1(741),c8(741),c1(742),c8(742),c1(743),c8(743),c1(744),c8(744),c1(745),c8(745),c1(746),c8(746),c1(747),c8(747),c1(748),c8(748),c1(749),c8(749),c1(750),c8(750)
, c1(751),c8(751),c1(752),c8(752),c1(753),c8(753),c1(754),c8(754),c1(755),c8(755),c1(756),c8(756),c1(757),c8(757),c1(758),c8(758),c1(759),c8(759),c1(760),c8(760)
, c1(761),c8(761),c1(762),c8(762),c1(763),c8(763),c1(764),c8(764),c1(765),c8(765),c1(766),c8(766),c1(767),c8(767),c1(768),c8(768),c1(769),c8(769),c1(770),c8(770)
, c1(771),c8(771),c1(772),c8(772),c1(773),c8(773),c1(774),c8(774),c1(775),c8(775),c1(776),c8(776),c1(777),c8(777),c1(778),c8(778),c1(779),c8(779),c1(780),c8(780)
, c1(781),c8(781),c1(782),c8(782),c1(783),c8(783),c1(784),c8(784),c1(785),c8(785),c1(786),c8(786),c1(787),c8(787),c1(788),c8(788),c1(789),c8(789),c1(790),c8(790)
, c1(791),c8(791),c1(792),c8(792),c1(793),c8(793),c1(794),c8(794),c1(795),c8(795),c1(796),c8(796),c1(797),c8(797),c1(798),c8(798),c1(799),c8(799),c1(800),c8(800)
, c1(801),c8(801),c1(802),c8(802),c1(803),c8(803),c1(804),c8(804),c1(805),c8(805),c1(806),c8(806),c1(807),c8(807),c1(808),c8(808),c1(809),c8(809),c1(810),c8(810)
, c1(811),c8(811),c1(812),c8(812),c1(813),c8(813),c1(814),c8(814),c1(815),c8(815),c1(816),c8(816),c1(817),c8(817),c1(818),c8(818),c1(819),c8(819),c1(820),c8(820)
, c1(821),c8(821),c1(822),c8(822),c1(823),c8(823),c1(824),c8(824),c1(825),c8(825),c1(826),c8(826),c1(827),c8(827),c1(828),c8(828),c1(829),c8(829),c1(830),c8(830)
, c1(831),c8(831),c1(832),c8(832),c1(833),c8(833),c1(834),c8(834),c1(835),c8(835),c1(836),c8(836),c1(837),c8(837),c1(838),c8(838),c1(839),c8(839),c1(840),c8(840)
, c1(841),c8(841),c1(842),c8(842),c1(843),c8(843),c1(844),c8(844),c1(845),c8(845),c1(846),c8(846),c1(847),c8(847),c1(848),c8(848),c1(849),c8(849),c1(850),c8(850)
, c1(851),c8(851),c1(852),c8(852),c1(853),c8(853),c1(854),c8(854),c1(855),c8(855),c1(856),c8(856),c1(857),c8(857),c1(858),c8(858),c1(859),c8(859),c1(860),c8(860)
, c1(861),c8(861),c1(862),c8(862),c1(863),c8(863),c1(864),c8(864),c1(865),c8(865),c1(866),c8(866),c1(867),c8(867),c1(868),c8(868),c1(869),c8(869),c1(870),c8(870)
, c1(871),c8(871),c1(872),c8(872),c1(873),c8(873),c1(874),c8(874),c1(875),c8(875),c1(876),c8(876),c1(877),c8(877),c1(878),c8(878),c1(879),c8(879),c1(880),c8(880)
, c1(881),c8(881),c1(882),c8(882),c1(883),c8(883),c1(884),c8(884),c1(885),c8(885),c1(886),c8(886),c1(887),c8(887),c1(888),c8(888),c1(889),c8(889),c1(890),c8(890)
, c1(891),c8(891),c1(892),c8(892),c1(893),c8(893),c1(894),c8(894),c1(895),c8(895),c1(896),c8(896),c1(897),c8(897),c1(898),c8(898),c1(899),c8(899),c1(900),c8(900)
, c1(901),c8(901),c1(902),c8(902),c1(903),c8(903),c1(904),c8(904),c1(905),c8(905),c1(906),c8(906),c1(907),c8(907),c1(908),c8(908),c1(909),c8(909),c1(910),c8(910)
, c1(911),c8(911),c1(912),c8(912),c1(913),c8(913),c1(914),c8(914),c1(915),c8(915),c1(916),c8(916),c1(917),c8(917),c1(918),c8(918),c1(919),c8(919),c1(920),c8(920)
, c1(921),c8(921),c1(922),c8(922),c1(923),c8(923),c1(924),c8(924),c1(925),c8(925),c1(926),c8(926),c1(927),c8(927),c1(928),c8(928),c1(929),c8(929),c1(930),c8(930)
, c1(931),c8(931),c1(932),c8(932),c1(933),c8(933),c1(934),c8(934),c1(935),c8(935),c1(936),c8(936),c1(937),c8(937),c1(938),c8(938),c1(939),c8(939),c1(940),c8(940)
, c1(941),c8(941),c1(942),c8(942),c1(943),c8(943),c1(944),c8(944),c1(945),c8(945),c1(946),c8(946),c1(947),c8(947),c1(948),c8(948),c1(949),c8(949),c1(950),c8(950)
, c1(951),c8(951),c1(952),c8(952),c1(953),c8(953),c1(954),c8(954),c1(955),c8(955),c1(956),c8(956),c1(957),c8(957),c1(958),c8(958),c1(959),c8(959),c1(960),c8(960)
, c1(961),c8(961),c1(962),c8(962),c1(963),c8(963),c1(964),c8(964),c1(965),c8(965),c1(966),c8(966),c1(967),c8(967),c1(968),c8(968),c1(969),c8(969),c1(970),c8(970)
, c1(971),c8(971),c1(972),c8(972),c1(973),c8(973),c1(974),c8(974),c1(975),c8(975),c1(976),c8(976),c1(977),c8(977),c1(978),c8(978),c1(979),c8(979),c1(980),c8(980)
, c1(981),c8(981),c1(982),c8(982),c1(983),c8(983),c1(984),c8(984),c1(985),c8(985),c1(986),c8(986),c1(987),c8(987),c1(988),c8(988),c1(989),c8(989),c1(990),c8(990)
, c1(991),c8(991),c1(992),c8(992),c1(993),c8(993),c1(994),c8(994),c1(995),c8(995),c1(996),c8(996),c1(997),c8(997),c1(998),c8(998),c1(999),c8(999),c1(1000),c8(1000)
;
COMMIT;
END LOOP;
END;
/
set veri on
UNDEFINE 1
UNDEFINE 2
@show_mystats
SELECT COUNT(1) FROM mrows_ins_tab
/
@show_mrows_ins_tab_size
truncate table scott.mrows_ins_tab
/
関連エントリ
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #20 - Table Value Constructer （TVC）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #21 - Table Value Constructer（TVC）- ハードパース時間とメモリ消費量 / BONUS TRACK
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #22 - Multi Row INSERT
・Oracle Database - Multi Row INSERT、バインド変数を使うと、リテラル値を使う場合では見える景色が変わるんだよね #1 - バグなのか現時点の仕様なのか?
| 固定リンク | 0
コメント