Dear all,
how can i generate 20gb of dummy data in my database? please advise
tq
Printable View
Dear all,
how can i generate 20gb of dummy data in my database? please advise
tq
Use loops and insert constants or value returned from function (random generated)
Abhay.
You can adapt Tamil's method to create a vast number of rows:
If there are n 1's in the cube, you get 2**n rows.Quote:
Originally posted by tamilselvan
PHP Code:select rownum as run_num,
to_date('15/01/2004','dd/mm/yyyy')+ rownum from
(select 1 from dual group by cube (1,1,1,1,1)) ;
Have you actually tried that? Be advised that it might suck depending on your version. A cube with 12 dimensions returns a mere 4096 rows (discussion is regarding millions). Compare times for 8i, 9.2 and 10g below...
Code:Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
SQL> SET TIMING ON;
SQL> SELECT COUNT (*)
2 FROM (SELECT 1
3 FROM dual
4 GROUP BY CUBE (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1));
COUNT(*)
----------
4096
Elapsed: 00:00:00.00
SQL>
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> SET TIMING ON;
SQL> SELECT COUNT (*)
2 FROM (SELECT 1
3 FROM dual
4 GROUP BY CUBE (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1));
COUNT(*)
----------
4096
Elapsed: 00:00:52.09
SQL>
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> SET TIMING ON;
SQL> SELECT COUNT (*)
2 FROM (SELECT 1
3 FROM dual
4 GROUP BY CUBE (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1));
COUNT(*)
----------
4096
Elapsed: 00:01:34.07
SQL>
I knew there was at least one good reason to stick with 8i ! ! ! !
Just tried with a 24-cube to give 16Meg rows - blows my temp storage! So it looks like a more modest cube in a PL/SQL loop is more practical.
To use CUBE, you need very high speed CPU and a lot of TEMP tablespace.
You can try this method:
execute dbms_random.seed(630522626) ;
create table random_table
tablespace large_data
as
select
substr(dbms_random.string('a',30),1,30) id1,
substr(dbms_random.string('a',30),1,30) id2,
substr(dbms_random.string('a',30),1,30) id3,
substr(dbms_random.string('a',30),1,30) id4,
substr(dbms_random.string('a',30),1,30) id5,
substr(dbms_random.string('a',30),1,30) id6,
substr(dbms_random.string('a',30),1,30) id7,
substr(dbms_random.string('a',30),1,30) id8,
substr(dbms_random.string('a',30),1,30) id9,
substr(dbms_random.string('a',30),1,30) id10
from dba_objects where rownum < 32000
;
Once the table is created, you can use insert into the random_table by selecting rows from the same table. Or you can use dbms_random.string within the select in the insert statement.
Tamil
Thanks will try out with urs recommendation.