Hi,
I'm on 9.2.0.8 and got query like this:
SELECT trw.id, trw.data, trw.godzina_od, trw.godzina_do,
trw.liczba_wnioskow
FROM trw
WHERE
trw.psl_kta_id = 12791592
AND
(SELECT COUNT (*)
FROM zgl, zdn
WHERE zgl.audyt_st = '1'
AND zgl.id = zdn.zgl_id
AND zdn.trw_id = trw.id
) < trw.liczba_wnioskow
ORDER BY trw.data, trw.godzina_od
/
Plan
SELECT STATEMENT CHOOSECost: 38 Bytes: 240 Cardinality: 10
10 SORT ORDER BY Cost: 8 Bytes: 240 Cardinality: 10
9 FILTER
2 TABLE ACCESS BY INDEX ROWID TRW Cost: 2 Bytes: 240 Cardinality: 10
1 INDEX RANGE SCAN NON-UNIQUE TRW_PSL_KTA_FK Cost: 1 Cardinality: 201
8 SORT AGGREGATE Bytes: 12 Cardinality: 1
7 NESTED LOOPS Cost: 3 Bytes: 12 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID ZDN Cost: 2 Bytes: 7 Cardinality: 1
3 INDEX FULL SCAN NON-UNIQUE ZDN_ZGL_FK Cost: 1 Cardinality: 30
6 TABLE ACCESS BY INDEX ROWID ZGL Cost: 1 Bytes: 5 Cardinality: 1
5 INDEX UNIQUE SCAN UNIQUE ZGL_PK Cardinality: 1
All three tables are small (TRW = 7MB , ZDN 4MB , ZGL 0,5 MB) but Oracle needs 356844 buffer gets to return 216 rows .
Any ideas how to rewrite that subquery are much appreciated .
Regards
GregG