-
Generate Dummy Data
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
You can adapt Tamil's method to create a vast number of rows:
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)) ;
If there are n 1's in the cube, you get 2**n rows.
-
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.
Last edited by DaPi; 08-27-2004 at 08:06 AM.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|