create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0 //这句什么意思?/
alter table big_table nologging;
declare
l_cnt number;
l_rows number := &1;//这句什么意思?/
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a
where rownum <= &1; //这句什么意思?/
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/
alter table big_table add constraint
big_table_pk primary key(id);
exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);PL/SQL
as
select rownum id, a.*
from all_objects a
where 1=0 //这句什么意思?/
alter table big_table nologging;
declare
l_cnt number;
l_rows number := &1;//这句什么意思?/
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a
where rownum <= &1; //这句什么意思?/
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/
alter table big_table add constraint
big_table_pk primary key(id);
exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);PL/SQL
from all_objects a
where 1=0 --只创建表结构,不插入数据l_rows number := &1;&1 手动输入参数
a.* 代表表的所有列。
a 是表的别名
/** **/,如果你说的是/*+ */,这个是hint写法。不然/** **/就是注释这些基本的你都问,那你永远也问不完的。。
2、&1 程序运行到此处,接收外来参数
create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0;
--只创建表结构,不插入数据,:= 表示赋值
l_rows number := &1;
--接收输入数据
rownum <= &1;
--同上,只不过此处是比较