Назад | Перейти на главную страницу

Как сгенерировать тестовые данные из sql-запроса и набора результатов?

Я использую базу данных 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