分类： Oracle
2006-11-08 10:11:32
SQL> L
1 select COUNT(*)
2 from leaguer l left outer join serv s on s.devcode = l.booktel and s.latnid = 434
3* where exists (select 1 from servunit su where su.servid = s.servid)
SQL> /
COUNT(*)
----------
779
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4685 Card=1 Bytes=46
)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=4685 Card=1259 Bytes=57914)
3 2 HASH JOIN (OUTER) (Cost=6 Card=1259 Bytes=35252)
4 3 TABLE ACCESS (FULL) OF 'LEAGUER' (Cost=3 Card=1259 B
ytes=2518)
5 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SERV' (Cost=
2 Card=1 Bytes=26)
6 5 INDEX (RANGE SCAN) OF 'IX_SERV_LATNID' (NON-UNIQUE
) (Cost=1 Card=1)
7 2 TABLE ACCESS (FULL) OF 'SERVUNIT' (Cost=4601 Card=6165
125 Bytes=110972250)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
590947 consistent gets
75486 physical reads
0 redo size
211 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
天哪，servunit全表扫描，这个可是900w的数据，发现索引也存在，后来重建一次吧，
SQL>
SQL> ALTER INDEX IX_SERVUNIT_SERVID REBUILD;
Index altered
SQL>
SQL> /
COUNT(*)
----------
779
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4685 Card=1 Bytes=46
)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=4685 Card=1259 Bytes=57914)
3 2 HASH JOIN (OUTER) (Cost=6 Card=1259 Bytes=35252)
4 3 TABLE ACCESS (FULL) OF 'LEAGUER' (Cost=3 Card=1259 B
ytes=2518)
5 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SERV' (Cost=
2 Card=1 Bytes=26)
6 5 INDEX (RANGE SCAN) OF 'IX_SERV_LATNID' (NON-UNIQUE
) (Cost=1 Card=1)
7 2 TABLE ACCESS (FULL) OF 'SERVUNIT' (Cost=4601 Card=6165
125 Bytes=110972250)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
590947 consistent gets
75486 physical reads
0 redo size
211 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
为题依然存在，可能存在表的结构的问题，后来重新查询了标的结构，原来最后的s.servid是number，另一个是varchar2的，这时候索引会失效的，于是改了过来
10:01:17 SQL> l
1 select count(*)
2 from leaguer l, serv s
3 where s.devcode = l.booktel
4 and s.latnid = 434
5* and exists (select 1 from servunit su where su.servid = s.servid)
10:01:18 SQL> c /s.servid/s.id
5* and exists (select 1 from servunit su where su.servid = s.id)
10:01:48 SQL> l
1 select count(*)
2 from leaguer l, serv s
3 where s.devcode = l.booktel
4 and s.latnid = 434
5* and exists (select 1 from servunit su where su.servid = s.id)
10:01:50 SQL> /
COUNT(*)
----------
779
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=49)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (SEMI) (Cost=7 Card=1 Bytes=49)
3 2 HASH JOIN (Cost=6 Card=1 Bytes=31)
4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SERV' (Cost=
2 Card=1 Bytes=29)
5 4 INDEX (RANGE SCAN) OF 'IX_SERV_LATNID' (NON-UNIQUE
) (Cost=1 Card=1)
6 3 TABLE ACCESS (FULL) OF 'LEAGUER' (Cost=3 Card=4 Byte
s=8)
7 2 INDEX (RANGE SCAN) OF 'IX_SERVUNIT_SERVID' (NON-UNIQUE
) (Cost=1 Card=6165125 Bytes=110972250)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
517010 consistent gets
0 physical reads
0 redo size
211 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
10:02:16 SQL>
ok，问题解决，效率马上提了上来，代价才为7,正好也修改了开发人员的sql，原来的sql查询也存在一些问题，呵呵
