我在对 ct_timemicro 表作过分析之后,为什么会出现如下情况,该怎样解决,我的表空间都还有很大空闲,而且为什么我选用RBO时不会出现表空间不足的错误.
到底是什么原因造成的.
我的ct_timemicro 上有一个主键索引 (no,tdate)
cv_nowworkno 为一个视图,视图如下:
select a.no,
a.wno,
b.name,
a.dept,
a.position,
a.jobs,
a.emptype,
a.bail
from ct_salchange a,
ct_basicinfo b,
(select no,max(cno) cno from ct_salchange group by no) c
where a.no=b.no and a.no=c.no and a.cno=c.cno
下面是三种sql的执行计划.
语句A:
select /*+ rule */
to_char(a.tdate,'yyyy-mm-dd') tdate,
b.dept
from tmp.ct_timemicro a,tmp.cv_nowworkno b
where a.no=b.no
group by a.tdate,b.dept
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=HINT: RULE
10 SORT (GROUP BY)
21 NESTED LOOPS
32 NESTED LOOPS
43 NESTED LOOPS
54 VIEW
65
SORT (GROUP BY)
76
TABLE ACCESS (FULL) OF 'CT_SALCHANGE'
84 TABLE ACCESS (BY INDEX ROWID) OF 'CT_SALCHANGE'
98
INDEX (UNIQUE SCAN) OF 'CT_SALCHANGE_PK010083210
73108' (UNIQUE)
103 INDEX (UNIQUE SCAN) OF 'PK_CT_BASICINFO' (UNIQUE)
112 INDEX (RANGE SCAN) OF 'PK_CT_TIMEMICRO' (UNIQUE)
Statistics
----------------------------------------------------------
0recursive calls
4db block gets
51925consistent gets
0physical reads
0redo size
446039bytes sent via SQL*Net to client
85558bytes received via SQL*Net from client
769SQL*Net roundtrips to/from client
2sorts (memory)
0sorts (disk)
11518rows processed
语句B
1select
2to_char(a.tdate,'yyyy-mm-dd') tdate,
3b.dept
4from tmp.ct_timemicro a,tmp.cv_nowworkno b
5where a.no=b.no
6* group by a.tdate,b.dept
SQL> /
select
*
ERROR 在行 1:
ORA-03232: unable to allocate an extent of 19 blocks from tablespace 3
语句C: (只选择一个人的记录)
1select
2to_char(a.tdate,'yyyy-mm-dd') tdate,
3b.dept
4from tmp.ct_timemicro a,tmp.cv_nowworkno b
5where a.no=b.no and a.no='0005845'
6* group by a.tdate,b.dept
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=414 Card=71 Bytes=37
63)
10 SORT (GROUP BY) (Cost=414 Card=71 Bytes=3763)
21 HASH JOIN (Cost=353 Card=12795 Bytes=678135)
32 VIEW (Cost=4 Card=112 Bytes=896)
43 SORT (GROUP BY) (Cost=4 Card=112 Bytes=1456)
54 INDEX (FAST FULL SCAN) OF 'CT_SALCHANGE_PK01008321
073108' (UNIQUE) (Cost=4 Card=112 Bytes=1456)
62 MERGE JOIN (CARTESIAN) (Cost=348 Card=11424 Bytes=5140
80)
76 NESTED LOOPS (Cost=12 Card=112 Bytes=3472)
87 INDEX (UNIQUE SCAN) OF 'PK_CT_BASICINFO' (UNIQUE)
(Cost=1 Card=1 Bytes=9)
97 TABLE ACCESS (FULL) OF 'CT_SALCHANGE' (Cost=11 Car
d=112 Bytes=2464)
106 SORT (JOIN) (Cost=337 Card=102 Bytes=1428)
11 10 INDEX (RANGE SCAN) OF 'PK_CT_TIMEMICRO' (UNIQUE) (
Cost=3 Card=102 Bytes=1428)
Statistics
----------------------------------------------------------
7recursive calls
8db block gets
94consistent gets
0physical reads
0redo size
5732bytes sent via SQL*Net to client
1309bytes received via SQL*Net from client
10SQL*Net roundtrips to/from client
8sorts (memory)
0sorts (disk)
132rows processed
|
