on the one hand you're correct. on the other hand, i see know reason
why the optimizer should be full scanning the index. ...and i also
see know reason why it's not possible to convince the optimizer to use
the index as a lookup. ...the last thing i tried was setting
optimizer_index_cost_adj = 1 ...still no luck.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 136.30 333.73 367616 17004541 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 136.31 333.74 367616 17004541 0 0
---------------------------------------------------------------------------------------
| Id | Operation |
Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 281K| 22M| 826 |
|* 1 | FILTER |
| | | |
| 2 | TABLE ACCESS BY INDEX ROWID | CASE_ID
| 281K| 22M| 826 |
|* 3 | INDEX FULL SCAN | CASE_ID_IDX1
| 2889K| | 26 |
|* 4 | TABLE ACCESS BY INDEX ROWID | ADDRESS
| 1 | 25 | 2 |
|* 5 | INDEX UNIQUE SCAN | ADDRESS_PK1
| 1 | | 1 |
| 6 | NESTED LOOPS |
| 1 | 41 | 3 |
|* 7 | INDEX RANGE SCAN |
ASSOCIATED_ADDRESS_PK | 1 | 16 | 2 |
|* 8 | TABLE ACCESS BY INDEX ROWID | ADDRESS
| 1 | 25 | 1 |
|* 9 | INDEX UNIQUE SCAN | ADDRESS_PK1
| 1 | | |
----------------------------------------------------------------------------------------
