我正在使用 Oracle 数据库，想要从实时数据库生成测试数据，但不想复制整个数据库内容。
给定一个数据库 DB(S, D)，它由数据库模式 S（用 DDL 描述的所有内容（表、列、约束等））和数据 D（数据库中的实际数据，用 DML 描述的所有内容）、查询 Q 和结果集 R（查询在 DB 上运行时的结果）组成。伪代码如下：
R = runQuery(Q, DB(S, D))
是否有一个工具可以生成数据 D2，以便我获得与查询 Q 相同的结果集 R？
D2 = generateTestData(S, Q, R)
R2 = runQuery(Q, DB(S, D2))
assert(R == R2)
一般来说这可能吗？ Toad 可以生成这些插入语句吗？
答案1
我使用自 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