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

Thread: loading tables with test data

  1. #1
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865

    loading tables with test data

    Hi all,

    Is there any way to load a table with n number of rows to test performance scenarios? Long time back I read some where about a package but not able to recall.

    My requirement is to load several millions of data into few table and test performance scenarios. I can do this by inserting into same table several times but want to do this using package.

    Appreciate your suggestion(s).

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    I am able to recall, it's dams_random package.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    You can use a pipelined function to generate data, and use the dbms_random to randomize the output.

  4. #4
    Join Date
    Jul 2006
    Posts
    195
    Vijay, as I believe in sharing try this out.

    CREATE TABLE XXX.TAB1
    (
    ID NUMBER NOT NULL,
    S1 VARCHAR2(30 BYTE),
    N1 NUMBER(10,0),
    INSERT_DATE DATE DEFAULT sysdate,
    UPDATE_DATE DATE
    )....tablespace bla bla bla


    CREATE INDEX XXX.TAB1_ID ON XXX.TAB1 (ID) tablespace bla bla bla


    CREATE OR REPLACE PACKAGE XXX.tab1dml_pkg as
    procedure addtran_tab1(num_loops NUMBER, commit_size NUMBER) ;
    procedure updtran_tab1(commit_size NUMBER) ;
    end tab1dml_pkg;
    /


    procedure addtran_tab1 (num_loops IN NUMBER, commit_size IN NUMBER) is
    id BINARY_INTEGER := 0;
    i number := 0;
    c_cnt number :=0;

    l_start number default dbms_utility.get_time;

    begin
    WHILE id < num_loops
    LOOP
    id := id + 1;

    INSERT INTO c0harpa.tab1
    (id, N1, S1)
    VALUES
    (id,
    DBMS_RANDOM.RANDOM,
    DBMS_RANDOM.VALUE(10000000,99999999),
    dbms_random.string('X', 20));

    i := i+1;
    if mod(i, commit_size) = 0
    THEN
    c_cnt := c_cnt+1;
    COMMIT;
    end if;

    END LOOP;
    commit;
    dbms_output.put_line (round((dbms_utility.get_time-l_start)/100, 2) ||' Seconds...' || c_cnt || ' commits' );

    END;


    procedure updtran_tab1 (commit_size IN NUMBER) is
    i number := 0;
    c_cnt number :=0;

    l_start number default dbms_utility.get_time;

    begin
    for r in (select * from xxx.tab1)
    LOOP


    UPDATE XXX.tab1 set
    N1= DBMS_RANDOM.RANDOM,
    S1=dbms_random.string('U', 25),
    UPDATE_DATE=sysdate where id = r.id;

    i := i+1;
    if mod(i, commit_size) = 0
    THEN
    c_cnt := c_cnt+1;
    COMMIT;
    end if;
    END LOOP;
    commit;
    dbms_output.put_line (round((dbms_utility.get_time-l_start)/100, 2) ||' Seconds.
    ..' || c_cnt || ' commits' );

    END;

    END;

    In this example, you will insert 1 million rows into the table and commit on
    every 1000th row. The update package will read though every row in the
    the table, in this case (1 million) and will commit on every 1000th row.

    Code to invoke from shell script like this:

    LOG=/tmp/test.out

    sqlplus / <> $LOG 2>&1
    set serveroutput on
    set timing on
    exec XXX.tab1dml_pkg.addtran_tab1(1000000, 1000);
    exec XXX.tab1dml_pkg.updtran_tab1(1000);
    exit
    EOT


    If the numbers are too large you may get a 1555 so you may want to
    add code to handle that.

    There are different values that can be used for the RANDOM function, change
    to suit your needs.... In my test case I have cols N1-N20 (all NUMBERS) and
    S1-S20 (ALL VARCHAR2) and I use different parameters to RANDOM in order
    to get a good mix (distribution) of data.

    Good luck

  5. #5
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thank you Stecal & BeefStu ...

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

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