ITPub博客

首页 > 数据库 > Oracle > 测试环境中构造大表

测试环境中构造大表

原创 Oracle 作者:qishao7232 时间:2015-08-24 22:24:06 0 删除 编辑

SQL> conn / as sysdba
Connected.
SQL>create table t_object1 as select * from dba_objects;
Table created.
SQL> conn scott/tiger
Connected.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
T_OBJECT1
SALGRADE
BONUS
EMP
DEPT
SQL> select count(1) from t_object1;
  COUNT(1)
----------
     86959
SQL>  create table t_object2 as select * from t_object1;
Table created.

SQL> alter table t_object2 nologging;
Table altered.
SQL> select wm_concat(column_name) from user_tab_columns where table_name='T_OBJECT2';
WM_CONCAT(COLUMN_NAME)
--------------------------------------------------------------------------------
OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LA
ST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAM
E
SQL> create sequence seq_t_object2_id start with 86960 ;
Sequence created.
SQL> select seq_t_object2_id.nextval from dual;
   NEXTVAL
----------
     86960
SQL> select seq_t_object2_id.nextval from dual;
   NEXTVAL
----------
     86961

点击(此处)折叠或打开

  1. SQL> begin
  2.   2 for i in 1 .. 10 loop
  3. insert into t_object2 (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME) select OWNER,OBJECT_NAME,SUBOBJECT_NAME,seq_t_object2_id.nextval,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME from t_object1 ;
  4.   4 commit;
  5.   5 end loop;
  6.   6 end;
  7.   7 /
PL/SQL procedure successfully completed.
SQL> select count(1) from t_object2;
  COUNT(1)
----------
    956549



点击(此处)折叠或打开

  1. SQL> begin
  2.   2 for i in 1 .. 1000 loop
  3.   3 insert into t_object2 (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME) select OWNER,OBJECT_NAME,SUBOBJECT_NAME,seq_t_object2_id.nextval,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME from t_object1 ;
  4.   4 commit;
  5.   5 end loop;
  6.   6 end;
  7.   7 /

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28340999/viewspace-1781549/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-03-12

  • 博文量
    8
  • 访问量
    26114