I have a view that is very slow if you fetch all rows. But if I select a subset (providing an ID in the where clause) the performance is very good. I cannot hardcode the ID so I create a sub select to get the ID from another table. The sub select only returns one ID. Now the performance is very slow and it seems like Oracle is evaluating the whole view before using the where clause. Can I somehow help Oracle so SQL 2 and 3 have the same performance? I’m using Oracle 10g

1 slow

select * from ci.my_slow_view

2 fast

select * from ci.my_slow_view where id = 1;

3 slow

select * from ci.my_slow_view where id in (select id from active_ids)