Oracle, slow performance when using sub select

Question

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)

Answer 1

This is the expected behavior... 3 is slow because Oracle will perform a "full table scan", which means that your indexes are not helping there (your where clause does not contain any constant or range and is unbounded, which implies that whatever index you use, all the rows are potentially candidates for the join condition.

Possible improvment: First, check that the indexes are ok on your join/pk columns (id in my_slow_view and active_ids). This is necessary for the second step: Second, generate table statistics for your table and views, that will make the Oracle cache memory optimizer kicks in. (It should work because it is assumed that your active_ids table is small enough to be fully in memory.)

Second approach: Write a stored procedure in PL/SQL where your id is an in parameter and rewrite your SQL so that it is used a bound parameter.

That should give you the flexibility you need (no hard coded ids), and the speed of the fastest query.

Answer 2

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.

Most likely, gathering statistics on the small table (while it contains a single row) will help, since that should help Oracle realize that it is small and encourage it to use the index on ID.

However, it sounds like this is really not the right solution to your original problem. Generally, when one wants to perform a query repeatedly with a different lookup value, the best way is to use a bind variable. The basic method of doing this in SQLPlus would be:

SQL>  variable id number
SQL>  exec :id := 1;
SQL>  select * from ci.my_slow_view where id = :id ;
SQL>  exec :id := 2;
SQL>  select * from ci.my_slow_view where id = :id ;

The details of implementing this depend on the environment you are developing in.

Answer 3

How about

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

Replacing the "in" with an "=" will tell Oracle that you expect the "select id from active_ids" to return only a single row.

Answer 4

Or:

select * from ci.my_slow_view, active_ids
where my_slow_view.id = active_ids.id;