Я использую базу данных oracle и хочу сгенерировать тестовые данные из реальной базы данных, но я не хочу копировать все содержимое базы данных.
Учитывая базу данных DB (S, D), состоящую из схемы базы данных S (все, что описано с помощью DDL (таблицы, столбцы, ограничения, ...)) и данных D (фактические данные в базе данных, все, что описано с помощью DML), запрос Q и набор результатов R, который является результатом запроса при запуске в БД. В псевдокоде:
R = runQuery(Q, DB(S, D))
Есть ли инструмент, который может генерировать данные D2, чтобы я получал тот же набор результатов R с запросом Q?
D2 = generateTestData(S, Q, R)
R2 = runQuery(Q, DB(S, D2))
assert(R == R2)
Это вообще возможно? Может ли жаба генерировать эти инструкции вставки?
Я вставляю миллион строк со скриптом, который использую с 2000 года.
У меня есть вызывающий скрипт, который создает хороший отчет для 10 прогонов и среднего.
6 of the top 10 fastest databases are running faster due to performance tuning.
INSTANCE SEC_TO_INSERT_250_MEG AVG_SEC_TO_INSERT_25_MEG SYSDATE
-------- --------------------- ------------------------ ---------
PWCMT 3.71 .37 21-AUG-14 Test Launch Pad DR with all the tuning parameters
DWADE 4.23 .42 14-OCT-14
PWCM1 4.44 .44 21-AUG-14 After System Changes Production Lauch Pad 4 times faster
TPLM 4.81 .48 21-AUG-14
PADMRT 5.94 .59 21-AUG-14 After System Changes refresh runs in 7 hours instead of 11
PWCZ1 6.17 .62 21-AUG-14 After System Changes Stage Launch Pad 3 times faster.
CPLM 6.60 .66 21-AUG-14
PRACPLM1 8.66 .87 21-AUG-14 Indexes built and purge of tables.
QADMRT 8.93 .89 21-AUG-14 After System Changes refresh runs in 6 hours instead of 10
DETL 11.12 1.11 21-AUG-14
DDCA 11.81 1.18 21-AUG-14
QDCA1 12.28 1.23 21-AUG-14
...
PIEDI 110.99 11.10 21-AUG-14
PSRT 154.50 15.45 21-AUG-14
PAUDIT 187.64 18.76 21-AUG-14
DHDR 522.30 52.23 21-AUG-14
QHDR 772.22 77.22 21-AUG-14
PHDR 989.03 98.90 21-AUG-14
Вот сценарий:
-- The Following script shows the time it takes to insert 1 million rows in a database.
-- I used it to determine that:
--
-- 1) Optimized Databases perform in relation to the hardware they run on,
-- and all the databases on 1 platform run within 10% of each other.
-- 2) When Rollback, Temp or Redo logs share the same underlying disks as
-- the "alan" table, the insert takes up to twice as long.
-- 3) When the rollback segments, Temp segments or redo log buffers are > 20 meg as
-- opposed to less than 1 meg, the inserts run 2 to 5 times faster.
-- By making the log_buffer and Rollback extents sizes close to 32k,
-- the inserts run up to 9 times slower.
set feedback off
set termout off
drop table alan;
set termout on
create table alan (name varchar2(30)) storage (initial 32k next 1m) logging noparallel storage(buffer_pool default);
insert into alan values ('alan');
insert into alan values ('alan1');
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
set timing on
insert /*+ PPEND */ into alan logging select * from alan /* This line shows the elapsed time. */
;
set timing off
set feedback on
set termout off
--drop table alan;
set termout on
А вот и вызывающий скрипт:
set feedback off
col sec_to_insert_250_meg for 9999.99
col avg_sec_to_insert_25_meg for 9999.99
col tot for 99999.9999
set termout off
drop table alan10;
create table alan10 (tot number(19,5));
insert into alan10 values (0.000);
commit;
-- select * from alan10;
set termout off
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
col instance for a8
set termout on
select instance_name instance,sum(tot) sec_to_insert_250_meg, sum(tot)/10 avg_sec_to_insert_25_meg,sysdate from alan10,v$instance group by instance_name;
set termout off
drop table alan10;
set termout on
set feedback on