Auto-completion of records in a database

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 28 Nov 2007 9:21 pm

For a College project, I need to implement a database with 5 million records using a couple of tables that the professor gave there. I already have relationships and everything, I just need to implement the database in Oracle and make an evaluation of his performance from a SQL query (type, the amount of resource it will consume, how long it will take to do the query, and so on and so forth). How can I fill these 5 million records at random? Is there any script that can do this?
tiagonascimento
Location: Belém - PA

Poston Thu, 29 Nov 2007 7:19 am

Hey James, beleza?

Which fields from your table?
If she has some records would you replicate these records that already exist? Or the data that you would include could be random letters and numbers??

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Thu, 29 Nov 2007 1:24 pm

Talks, Cristiano! Thanks for your time, man, I need too much of that aid.

The table is very simple, has four columns: student, PHONE, title, SALARY.

The filled tables and the database need not be shown to the teacher, so the fields can be filled in at random with a string, one real, one string and one real, respectively. The goal is to assess the performance of the database. I just need a way to automatically fill in the fields. with any values.

Thanks again! If you have someone else who can help, I appreciate it!
tiagonascimento
Location: Belém - PA

Poston Thu, 29 Nov 2007 1:52 pm

Opa and ai beleza?

There follows a code q I assembled here, I not tested, but take a look here!! :D

Code: Select all
DECLARE
    v_FUNCIONARIO varchar2(256);
    v_TELEFONE    varchar2(256);
    v_CARGO       varchar2(256);
    v_SALARIO     varchar2(256);
    v_din         varchar2(15);
    vCOUNT        NUMBER(8);
BEGIN
    FOR X IN 1..5000000 LOOP
      select substr(replace(replace(replace(sysdate-x/24/60*3600,'/',''),' ',''),':',''),1,9) into v_din from dual;
      dbms_random.initialize(v_din);       
      v_FUNCIONARIO := to_char( abs( dbms_random.random-(x / 1) ) );
      dbms_random.terminate();
     
      select substr(replace(replace(replace(sysdate-x/24/60/3600,'/',''),' ',''),':',''),1,9) into v_din from dual;
      dbms_random.initialize(v_din);       
      v_TELEFONE := to_char( abs( dbms_random.random/x ) );
      dbms_random.terminate();
     
      dbms_random.initialize(v_din);       
      v_CARGO := to_char( abs( dbms_random.random/x/13/30/1800 ) );
      dbms_random.terminate();
     
      dbms_random.initialize(v_din);       
      v_SALARIO := to_char( abs( dbms_random.random/x/13/30-1800  )/1000000 );
      dbms_random.terminate();

      v_FUNCIONARIO := TRANSLATE(v_FUNCIONARIO,'0123456789','ABCDEFGHIJ');
      v_TELEFONE    := TRANSLATE(v_TELEFONE   ,'0123456789','FSIUHWEHUS');
      v_CARGO       := TRANSLATE(v_CARGO      ,'0123456789','OIXJVCOIJW');
     
      INSERT INTO TABELA VALUES (v_FUNCIONARIO, v_TELEFONE, v_CARGO, v_SALARIO)
      vCOUNT := vCOUNT+1;
      IF vCOUNT = 10000 THEN
         vCOUNT := 0;
         COMMIT;
      END IF;
    END LOOP;
end;


[]'s
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Thu, 29 Nov 2007 6:44 pm

Follows another solution (tested in version 10 g)
Code: Select all
insert into teste_carga
select dbms_random.string(chr(mod(rownum, 25) + 65), 20)
     , substr(lpad(abs(dbms_random.random), 8, '0'), 1, 8)
     , dbms_random.string(chr(mod(rownum, 25) + 65), 12)
     , to_number(substr(abs(dbms_random.random), 1, 8)) / 100
from   dual
connect by level <= 5000000;
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Fri, 30 Nov 2007 11:15 pm

Thank you, my friends, for your attention and for your time!
If anyone has more suggestions I accept, ok?

A big hug!
tiagonascimento
Location: Belém - PA

Poston Tue, 04 Dec 2007 12:41 am

Cristiano, I took your code and made some changes to get what I wanted. Take a look at how it looks:
Code: Select all
CREATE TABLE TAB_TIAGO (
   FUNCIONARIO VARCHAR2(255),
   TELEFONE VARCHAR2(255),
   CARGO VARCHAR2(255),
   SALARIO NUMBER(38)
)
TABLESPACE TBS_TIAGO;

DECLARE
   TYPE T_FUNCIONARIO IS VARRAY(500) OF TAB_TIAGO.FUNCIONARIO%TYPE;
   TYPE T_TELEFONE IS VARRAY(500) OF TAB_TIAGO.TELEFONE%TYPE;
   TYPE T_CARGO IS VARRAY(500) OF TAB_TIAGO.CARGO%TYPE;
   TYPE T_SALARIO IS VARRAY(500) OF TAB_TIAGO.SALARIO%TYPE;
      
    v_FUNCIONARIO varchar2(256);
    v_TELEFONE    varchar2(256);
    v_CARGO       varchar2(256);
    v_SALARIO     varchar2(256);
   
    VFUNCIONARIO T_FUNCIONARIO;
    VTELEFONE T_TELEFONE;
    VCARGO T_CARGO;
    VSALARIO T_SALARIO;
   
    v_din         varchar2(15);
    vCOUNT        NUMBER(8);
BEGIN
    FOR X IN 1..20 LOOP
   
       vfuncionario:= t_funcionario();
         vtelefone := t_telefone();
         vcargo := t_cargo();
         vsalario := t_salario();
   
       FOR Y IN 1..500 LOOP
         select substr(replace(replace(replace(sysdate-x/24/60*3600,'/',''),' ',''),':',''),1,9) into v_din from dual;
         dbms_random.initialize(v_din);       
         v_FUNCIONARIO := to_char( abs( dbms_random.random-(x / 1) ) );
         dbms_random.terminate();
        
         select substr(replace(replace(replace(sysdate-x/24/60/3600,'/',''),' ',''),':',''),1,9) into v_din from dual;
         dbms_random.initialize(v_din);       
         v_TELEFONE := to_char( abs( dbms_random.random/x ) );
         dbms_random.terminate();
        
         dbms_random.initialize(v_din);       
         v_CARGO := to_char( abs( dbms_random.random/x/13/30/1800 ) );
         dbms_random.terminate();
        
         dbms_random.initialize(v_din);       
         v_SALARIO := to_char( abs( dbms_random.random/x/13/30-1800  )/1000000 );
         dbms_random.terminate();
   
         begin
         VFUNCIONARIO.EXTEND();
         VTELEFONE.EXTEND();
         VCARGO.EXTEND();
         VSALARIO.EXTEND();
        
         VFUNCIONARIO(Y) := TRANSLATE(v_FUNCIONARIO,'0123456789','ABCDEFGHIJ');
         VTELEFONE(Y) := TRANSLATE(v_TELEFONE   ,'0123456789','FSIUHWEHUS');
         VCARGO(Y) := TRANSLATE(v_CARGO      ,'0123456789','OIXJVCOIJW');
         VSALARIO(Y) := V_SALARIO;
         exception
            when others then
               null;
         end;
        
      END LOOP;
     
      FORALL I IN 1..500 INSERT INTO TAB_TIAGO VALUES (VFUNCIONARIO(I), VTELEFONE(I), VCARGO(I), VSALARIO(I));
     
      COMMIT;
    END LOOP;
end;
tiagonascimento
Location: Belém - PA

Poston Tue, 04 Dec 2007 7:38 am

Cool James, that's it!! :D took the script to roll muito???

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Sun, 09 Dec 2007 2:02 pm

Cristiano, it took a long time. With five million records, it took 12:0 and a half to make the query. That a Pentium 4 HT with 512 MB of memory. The level of processor activity stayed at 100% all the time, I thought I was going to merge the poor computer. But it was great, I learned a lot of things.
Thanks man, I was happy to use the forum and know that there's always someone willing to help.

A big hug!
tiagonascimento
Location: Belém - PA

Poston Mon, 10 Dec 2007 9:41 am

you think in create an index?
to test if changes a lot in making an appointment with or without index = D if you have more free 12:0, creates the index and faiz the select again = x
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas


  • See also
    Replies
    Views
    Last Post


      Return to SQL

      Who is online

      Users browsing this forum: No registered users and 2 guests