DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Generate Dummy Data

  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Generate Dummy Data

    Dear all,
    how can i generate 20gb of dummy data in my database? please advise
    tq

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  4. #4
    Join Date
    Jan 2004
    Posts
    162
    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>

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  7. #7
    Join Date
    Jan 2004
    Posts
    30
    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
  •  


Click Here to Expand Forum to Full Width