データに依存するが、
Oracleではdeterministic指定の効果を確認できた。
MySQLとPostgreSQLでは効果を確認できなかった。
(8.0.27)
-- 1. テストデータ作成
drop table tab1;
create table tab1(
col1 int primary key
,col2 varchar(100)
,col3 varchar(100)
);
drop procedure proc1;
delimiter //
create procedure proc1(in x int)
begin
declare i int;
set i = 0;
start transaction;
while i < x do
set i = i + 1;
insert into tab1 values(
i
,substring(md5(rand()), 1, 30)
,'123456789012345678901234567890'
);
end while;
commit;
end
//
delimiter ;
call proc1(10000000);
select count(*) from tab1;
select * from tab1 order by rand() limit 20;
-- 2. ファンクション作成
drop function fun1;
delimiter //
create function fun1(p1 varchar(100) )
returns varchar(100)
no sql
begin
return concat('XXXXX' , substr(p1,6) );
end//
delimiter ;
drop function fun2;
delimiter //
create function fun2(p1 varchar(100) )
returns varchar(100)
deterministic
no sql
begin
return concat('XXXXX' , substr(p1,6) );
end//
delimiter ;
select fun1('123456789012345678901234567890');
select fun2('123456789012345678901234567890');
-- 3. 時間計測
select ps_current_thread_id();
select * from sys.memory_by_thread_by_current_bytes
where thread_id = 46\G
-- 3.1 ランダムデータでdeterministicなし
drop table tab2;
create table tab2 as select col1,fun1(col2) col2 from tab1;
(2 min 34.02 sec)
current_allocated: 1011.25 KiB
--- 3.2 一様データでdeterministicなし
drop table tab2;
create table tab2 as select col1,fun1(col3) col2 from tab1;
(3 min 10.72 sec)
current_allocated: 999.77 KiB
-- 3.3 ランダムデータでdeterministicあり
drop table tab2;
create table tab2 as select col1,fun2(col2) col2 from tab1;
(2 min 26.91 sec)
current_allocated: 1.00 MiB
--- 3.4 一様データでdeterministicあり
drop table tab2;
create table tab2 as select col1,fun2(col3) col2 from tab1;
(2 min 35.31 sec)
current_allocated: 1013.18 KiB
-- 4. 結果
ランダムデータでdeterministicの効果なし。
一様データでもdeterministicの効果は確認できなかった。
(19c)
https://www.ashisuto.co.jp/db_blog/article/20170322_session_memory.html
http://stevenfeuersteinonplsql.blogspot.com/2020/08/the-differences-between-deterministic.html
-- 1. テストデータ作成
drop table tab1 purge;
create table tab1(
col1 int primary key
,col2 varchar2(100)
,col3 varchar2(100)
);
declare
begin
for i in 1..10000000 loop
insert into tab1 values
(i
,substr(standard_hash(dbms_random.value(), 'MD5'),1,30)
,'123456789012345678901234567890'
);
end loop;
end;
/
commit;
select count(*) from tab1;
select * from tab1 order by dbms_random.value() fetch first 20 rows only;
-- 2. ファンクション作成
create or replace function fun1 (p1 in varchar2) return varchar2
is
begin
return 'XXXXX' || substr(p1,6);
end;
/
create or replace function fun2 (p1 in varchar2) return varchar2
deterministic
is
begin
return 'XXXXX' || substr(p1,6);
end;
/
select fun1('123456789012345678901234567890') from dual;
select fun2('123456789012345678901234567890') from dual;
-- 3. 時間計測
set timing on
select
s.sid
,s.serial#
,s.state
,s.status
,p.pga_alloc_mem/1024/1024
from v$session s,v$process p
where s.paddr = p.addr
and s.sid = 59;
-- 3.1 ランダムデータでdeterministicなし
drop table tab2 purge;
create table tab2 as select col1,fun1(col2) col2 from tab1;
経過: 00:00:32.56
実行時PGAサイズ = 27MB
--- 3.2 一様データでdeterministicなし
drop table tab2 purge;
create table tab2 as select col1,fun1(col3) col2 from tab1;
経過: 00:00:32.01
実行時PGAサイズ = 30MB
-- 3.3 ランダムデータでdeterministicあり
drop table tab2 purge;
create table tab2 as select col1,fun2(col2) col2 from tab1;
経過: 00:00:32.01
実行時PGAサイズ = 30MB
--- 3.4 一様データでdeterministicあり
drop table tab2 purge;
create table tab2 as select col1,fun2(col3) col2 from tab1;
経過: 00:00:09.06 ★
実行時PGAサイズ = 30MB
-- 4. 結果
ランダムデータでdeterministicの効果なし。
一様データでdeterministicの効果あり。 ★
deterministicを指定しても特にPGAサイズが大きくなる傾向は見られなかった
(14)
-- 1. テストデータ作成
drop table tab1;
create table tab1(
col1 int primary key
,col2 varchar(100)
,col3 varchar(100)
);
start transaction;
insert into tab1 select
g
,substring(md5(random()::text), 1, 30)
,'123456789012345678901234567890'
from generate_series(1,10000000) g;
commit;
select count(*) from tab1;
select * from tab1 order by random() limit 20;
-- 2. ファンクション作成
create or replace function fun1(in p1 varchar)
returns varchar as $$
begin
return 'XXXXX' || substr(p1,6);
end;
$$ language plpgsql;
create or replace function fun2(in p1 varchar)
returns varchar as $$
begin
return 'XXXXX' || substr(p1,6);
end;
$$ language plpgsql immutable;
select fun1('123456789012345678901234567890');
select fun2('123456789012345678901234567890');
-- 3. 時間計測
\timing 1
set log_statement_stats = 'on';
show log_statement_stats;
-- 3.1 ランダムデータでimmutableなし
drop table tab2;
create table tab2 as select col1,fun1(col2) col2 from tab1;
時間: 26654.378 ミリ秒(00:26.654)
171932 kB max resident size
--- 3.2 一様データでimmutableなし
drop table tab2;
create table tab2 as select col1,fun1(col3) col2 from tab1;
時間: 23447.982 ミリ秒(00:23.448)
171932 kB max resident size
-- 3.3 ランダムデータでimmutableあり
drop table tab2;
create table tab2 as select col1,fun2(col2) col2 from tab1;
時間: 22697.288 ミリ秒(00:22.697)
171932 kB max resident size
--- 3.4 一様データでimmutableあり
drop table tab2;
create table tab2 as select col1,fun2(col3) col2 from tab1;
時間: 25635.885 ミリ秒(00:25.636)
171932 kB max resident size
-- 4. 結果
ランダムデータでimmutableの効果なし。
一様データでもimmutableの効果は確認できなかった。
(2019)
IsDeterministicプロパティはSQL Server によって自動的に決定されるため、検証不可。
