Oracle千万条记录的测试

客户-订单-订单项

插入数据的存储过程如下：

 

create or replace procedure createUsers is
v_sequ_user number(19);
v_sequ_order number(19);
begin
  for v_count in 1..1000000 loop
      insert into user_ values(seq_all.nextval, 'user_'||v_count, 'user_'||v_count, sysdate, '这是第'||v_count||'用户');
      select seq_all.currval into v_sequ_user from dual;
      for v_order_cout in 1..3 loop
         insert into order_ values(seq_all.nextval, sysdate,v_sequ_user, '这是第'||v_count||'用户第'||v_order_cout||'张订单');
         select seq_all.currval into v_sequ_order from dual;
         for v_item_count in 1..5 loop
             insert into item_ values(seq_all.nextval,'订单项'||v_item_count,dbms_random.value(100, 100000), dbms_random.value(1, 1000), '这是第'||v_count||'用户第'||v_order_cout||'张订单第'||v_item_count||'个订单项', v_sequ_order);
         end loop;
      end loop;
      if mod(v_count, 100) = 0 then
         commit;
      end if;
  end loop;
end createUsers;

 

===========================================

总共插入季1900W条记录，用了4898.188秒。

 

--查询订单总额大于10000的所有用户的姓名和订单号
select u.name_, o.id_
  from user_ u, order_ o,  (select i.fk_order_item, sum(i.price_) from item_ i group by i.fk_order_item) temp
 where u.id_ = o.fk_user_order_
   and temp.fk_order_item = o.id_;