oracle全表扫描166G的表只花了6分钟(四)
n (p15) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p16) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p17) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p18) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p19) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p20) a
15269912
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p21) a
14474581
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p22) a
10448829
total:40193322
PL/SQL procedure successfully completed.
Elapsed: 00:06:02.03
--session 2：
SQL> set timing on
SQL> declare
2 v_sql varchar2(2000);
3 v_count number;
4 s number:=0;
5 begin
6 for x in 23..24 loop
7 v_sql:='select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p'||x||') a';
8 p(v_sql);
9 execute immediate v_sql into v_count;
10 p(v_count);
11 s:=s+v_count;
12 end loop;
13 p('total:'||s);
14 end;
15 /
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p23) a
14920046
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p24) a
14747020
total:29667066
PL/SQL procedure successfully completed.
Elapsed: 00:05:17.42
--session 3：
SQL> set timing on
SQL> declare
2 v_sql varchar2(2000);
3 v_count number;
4 s number:=0;
5 begin
6 for x in 25..26 loop
7 v_sql:='select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p'||x||') a';
8 p(v_sql);
9 execute immediate v_sql into v_count;
10 p(v_count);
11 s:=s+v_count;
12 end loop;
13 p('total:'||s);
14 end;
15 /
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p25) a
15727771
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p26) a
12998516
total:28726287
PL/SQL procedure successfully completed.
Elapsed: 00:05:16.31
--session 4：
SQL>
set timing on
SQL> declare
2 v_sql varchar2(2000);
3 v_count number;
4 s number:=0;
5 begin
6 for x in 27..44 loop
7 v_sql:='select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p'||x||') a';
8 p(v_sql);
9 execute immediate v_sql into v_count;
10 p(v_count);
11 s:=s+v_count;
12 end loop;
13 p('total:'||s);
14 end;
15 /
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p27) a
12529834
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p28) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p29) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p30) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p31) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p32) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p33) a
0
select /*+full(a) parallel(a,4)*/cou