oracle全表扫描166G的表只花了6分钟(三)
nt(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p34) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p35) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p36) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p37) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p38) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p39) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p40) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p41) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p42) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p43) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p44) a
0
total:12529834
PL/SQL procedure successfully completed.
Elapsed: 00:02:31.85
--最终的数据量：
SQL> select 40193322+29667066+28726287+12529834 from dual;
40193322+29667066+28726287+12529834
-----------------------------------
111116509
1 row selected.
Elapsed: 00:00:00.04
--其中p是存储过程：
create or replace procedure p(v_sql in varchar2)
is
begin
dbms_output.put_line(v_sql);
end;
--getsegsize_partition.sql
set lines 200
set pages 200
set long 100000000
col owner for a20
col SEGMENT_NAME for a30
col SEGMENT_TYPE for a20
col TABLESPACE_NAME for a20
set ver off
col SEGMENT_NAME for a30
col big_m for 999,999.9
col big_g for 999,999.9
select owner,segment_name,PARTITION_NAME,
segment_type,
round(bytes / 1024 / 1024,1) big_m,
round(bytes / 1024 / 1024 / 1024,1) big_g,
tablespace_name
from dba_segments&dblink
where segment_name = upper('&segment_name')
AND OWNER=upper('&owner')
order by big_m;
因为4个窗口是同时执行的，最长的时间只用了6分钟，166G的表只要6分钟，速度已经非常快了。总共16个cpu，16个并行，已经充分利用了cpu的资源，如果
系统资源空闲，可以这样查，如果系统资源紧张，就不要这样查了，凡事有利有弊。