SQL> select tg.id,count(distinct tu.id) studynum
2 from tgroup tg,tuser tu, vUserProvinceCorpID vp,
3 (
4 select us_Id from logbook
5 where event=3
6 and EVENT_TIME1>=to_date('2005-06-01','yyyy-mm-dd') and
7 EVENT_TIME1<=(to_date('2005-06-30','yyyy-mm-dd')+1)
8 ) lg
9 where tg.ParentID = 9002 and tg.ReservedInt=1 and tg.id=vp.provinceID and v
p.userid =tu.id
10 and tu.id=lg.us_id
11 group by tg.id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1 Bytes=49)
1 0 SORT (GROUP BY) (Cost=16 Card=1 Bytes=49)
2 1 NESTED LOOPS (Cost=12 Card=1 Bytes=49)
3 2 HASH JOIN (Cost=9 Card=3 Bytes=84)
4 3 NESTED LOOPS (Cost=2 Card=258 Bytes=3096)
5 4 VIEW OF 'VUSERPROVINCECORPID' (Cost=2 Card=258 Byt
es=2064)
6 5 UNION-ALL
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'TUSER' (Cost
=72 Card=654 Bytes=5232)
8 7 NESTED LOOPS (Cost=89 Card=131 Bytes=6550)
9 8 NESTED LOOPS (Cost=17 Card=1 Bytes=42)
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'TGROUP
' (Cost=12 Card=1 Bytes=21)
11 10 INDEX (RANGE SCAN) OF 'TGROUP_PARENTID
_IDX' (NON-UNIQUE) (Cost=1 Card=19)
12 9 TABLE ACCESS (BY INDEX ROWID) OF 'TGROUP
' (Cost=5 Card=1 Bytes=21)
13 12 INDEX (RANGE SCAN) OF 'TGROUP_PARENTID
_IDX' (NON-UNIQUE) (Cost=1 Card=8)
14 8 INDEX (RANGE SCAN) OF 'TUSER_CORPID_IDX' (
NON-UNIQUE) (Cost=2 Card=654)
15 6 TABLE ACCESS (BY INDEX ROWID) OF 'TUSER' (Cost
=72 Card=654 Bytes=5232)
16 15 NESTED LOOPS (Cost=84 Card=127 Bytes=3683)
17 16 TABLE ACCESS (BY INDEX ROWID) OF 'TGROUP'
(Cost=12 Card=1 Bytes=21)
18 17 INDEX (RANGE SCAN) OF 'TGROUP_PARENTID_I
DX' (NON-UNIQUE) (Cost=1 Card=19)
19 16 INDEX (RANGE SCAN) OF 'TUSER_CORPID_IDX' (
NON-UNIQUE) (Cost=2 Card=654)
20 4 INDEX (UNIQUE SCAN) OF 'PK_TUSER' (UNIQUE)
21 3 TABLE ACCESS (BY INDEX ROWID) OF 'LOGBOOK' (Cost=6 C
ard=3078 Bytes=49248)
22 21 INDEX (RANGE SCAN) OF 'LOGBOOK_EVENT_TIME1_2_IDX'
(NON-UNIQUE) (Cost=2 Card=17)
23 2 TABLE ACCESS (BY INDEX ROWID) OF 'TGROUP' (Cost=1 Card
=1 Bytes=21)
24 23 INDEX (UNIQUE SCAN) OF 'PK_TGROUP' (UNIQUE)