构建百万千万级表的多种方法及其性能对比分析(三)
show error
Errors for PROCEDURE PROC_TEST1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1 PL/SQL: SQL Statement ignored
6/13 PL/SQL: ORA-00942: table or view does not exist
BYS@ bys001>create table test1(a number);
BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>exec proc_test1;
PL/SQL procedure successfully completed.
Elapsed: 00:01:21.39
19:12:38 SQL> select t.sql_text, t.sql_id,t.PARSE_CALLS, t.EXECUTIONS
2 from v$sql t
3 where sql_text like '%INSERT INTO TEST1%' and rownum <10;
SQL_TEXT SQL_ID PARSE_CALLS EXECUTIONS
------------------------------ ------------- ----------- ----------
INSERT INTO TEST1 VALUES (:B1 b9vjmmkg8ffhg 0 1000000
BYS@ bys001>select 1000000/81 from dual;
1000000/81
----------
12345.679
##########################################
方法七：这种方法是把原来的过程变成了单条SQL，把一条一条插入的语句变成一个集合，
成批次的写入data buffer区，再次提高了速度。
BYS@ bys001>create table test1(a number);
BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>insert into test1 select rownum from dual connect by rownum<1000001;
1000000 rows created.
Elapsed: 00:00:04.46
BYS@ bys001>commit;
BYS@ bys001>select count(*) from test1;
COUNT(*)
----------
1000000
#############################################
方法八：使用CREATE TABLE as select的方法来创建表更快速。
原因是insert into test1 select的方式需要先将数据写入data buffer区，再写入磁盘
create table test1 as select的方式跳过data buffer区直接写入磁盘。
BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>create table test1 as select rownum a from dual connect by rownum<1000001;
BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>create table test1 as select rownum a from dual connect by rownum<1000001;
Table created.
Elapsed: 00:00:03.10
############################
方法九 ：设置并行度为2，再次提升速度。
修改虚拟机配置为使用两个核心。查看：
[oracle@oel-01 ~]$ grep 'physical id' /proc/cpuinfo | so
physical id : 0
[oracle@oel-01 ~]$ grep 'core id' /proc/cpuinfo | sort -u | wc -l
2
比如我这里就是单CPU双核心，设置并行度为2
BYS@ bys001>alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.35
BYS@ bys001>create table test1 parallel 2 as select rownum a from dual connect by rownum<1000001;
Table created.
Elapsed: 00:00:02.20