【oracle实验】一张一千六百万记录的表按部门id分组累计一次需要几秒？答案（6-7秒）

对一个唯一主键为id，包括名称和部门id及其他共20个字段的表，答案是7秒左右，以下是执行情况：

SQL> select count(*),deptid from tb_16million2 group by deptid;

  COUNT(*)     DEPTID
---------- ----------
    768000          5
    860000          8
    848000          7
    836000          6
    828000         11
    420000         20
    828000         18
    912000          3
    436000          1
    832000         14
    912000         17

  COUNT(*)     DEPTID
---------- ----------
    884000         10
    860000         16
    796000          9
    856000          2
    808000         15
    840000         19
    728000         13
    836000          4
    912000         12

已选择20行。

已用时间:  00: 00: 07.26
SQL>

如上，最后显示是7.26秒。

 

建表及查询语句如下，大家可以自行去试：

CREATE TABLE tb_4thousand1
(
    id NUMBER not null primary key,
    name NVARCHAR2(60) not null,
    deptid int not null,
    col03 int not null,
    col04 int not null,
    col05 int not null,
    col06 int not null,
    col07 int not null,
    col08 int not null,
    col09 int not null,
    col10 int not null,
    col11 int not null,
    col12 int not null,
    col13 int not null,
    col14 int not null,
    col15 int not null,
    col16 int not null,
    col17 int not null,
    col18 int not null,
    col19 int not null,
    col20 int not null
)

insert into tb_4thousand1
select rownum,
        dbms_random.string('*',dbms_random.value(6,20)),
    dbms_random.value(1,20),
        0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
from dual
connect by level<4001
order by dbms_random.random

CREATE TABLE tb_4thousand2
(
    id NUMBER not null primary key,
    name NVARCHAR2(60) not null,
    deptid int not null,
    col03 int not null,
    col04 int not null,
    col05 int not null,
    col06 int not null,
    col07 int not null,
    col08 int not null,
    col09 int not null,
    col10 int not null,
    col11 int not null,
    col12 int not null,
    col13 int not null,
    col14 int not null,
    col15 int not null,
    col16 int not null,
    col17 int not null,
    col18 int not null,
    col19 int not null,
    col20 int not null
)

insert into tb_4thousand2
select rownum,
        dbms_random.string('*',dbms_random.value(6,20)),
    dbms_random.value(1,20),
        0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
from dual
connect by level<4001
order by dbms_random.random

select count(*) from (select t1.* from tb_4thousand1 t1 cross join tb_4thousand2 t2)

create table tb_16million2 as select t1.* from tb_4thousand1 t1 cross join tb_4thousand2 t2

update tb_16million2 set id=rownum where 1=1;

ALTER TABLE tb_16million2 ADD CONSTRAINT tb_16million2_pk PRIMARY KEY (id);

select count(*),deptid from tb_16million2 group by deptid;

本人机器情况如下：

我的环境：

#	类别	版本
1	操作系统	Win10
2	数据库	Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
3	硬件环境	T440p
4	内存	8G

感悟：须教自我心中出，切忌随人脚后行。

END