Como gerar dados de teste de uma consulta sql e um conjunto de resultados?

Question

Estou usando um banco de dados oracle e quero gerar dados de teste de um banco de dados ativo, mas não quero copiar todo o conteúdo do banco de dados.

Dado um banco de dados DB (S, D) que consiste no esquema de banco de dados S (tudo descrito com DDL (tabelas, colunas, restrições, ...)) e dados D (os dados reais no banco de dados, tudo descrito com DML), uma consulta Q e um conjunto de resultados R que é o resultado da consulta quando executado no banco de dados. No pseudocódigo:

R = runQuery(Q, DB(S, D))

Existe uma ferramenta que pode gerar dados D2 de tal forma que Eu obtenho o mesmo conjunto de resultados R com a consulta Q?

D2 = generateTestData(S, Q, R)
R2 = runQuery(Q, DB(S, D2))

assert(R == R2)

Isso é possível em geral? O sapo pode gerar essas instruções de inserção?

    

Answer 1

Eu insiro um milhão de linhas com um script que tenho usado desde 2000.

Eu tenho um script de chamada que cria um relatório legal para 10 execuções e a média.

 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

Aqui está o script:

-- 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

E aqui está o script de chamada:

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