Hi,
I've got query
select * from dziennik i
WHERE i.ID IN ('20074735', '0074736', '20074737', '74738')
id - is PK
with plan
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DZIENNIK'
3 2 INDEX (UNIQUE SCAN) OF 'DZIENNIK_PK' (UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DZIENNIK'
5 4 INDEX (UNIQUE SCAN) OF 'DZIENNIK_PK' (UNIQUE)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'DZIENNIK'
7 6 INDEX (UNIQUE SCAN) OF 'DZIENNIK_PK' (UNIQUE)
8 1 TABLE ACCESS (BY INDEX ROWID) OF 'DZIENNIK'
9 8 INDEX (UNIQUE SCAN) OF 'DZIENNIK_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
1408 bytes sent via SQL*Net to client
237 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
and when using hint NO_EXPAND got that plan:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=6 Card=1 Bytes=405)
1 0 INLIST ITERATOR
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DZIENNIK' (Cost=6 Card
=1 Bytes=405)
3 2 INDEX (RANGE SCAN) OF 'DZIENNIK_PK' (UNIQUE) (Cost=2 C
ard=8)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
1407 bytes sent via SQL*Net to client
237 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
So, looks like index concatenation is better than range scan .
Where NO_EXPAND hint is helpful ?
Is that dependent on 'range of values' in IN clause or maybe something different ?
Regards.
Greg