Re: Outer join = full table scan

Chrysalis wrote:
>
> Outer joins do *not*, in general, result in a full-table scan unless a
> full join would also require one.
>

You're comment certainly helped. I think what is happening here is that the optimizer is getting confused because the select statement actually uses views (with subqueries), not tables. Perhaps I should have been a little more specific. For example:

create or replace view a_view as
select * from a where group_id in

            (select group_id from groups
             where username = user);

create or replace view b_view as
select * from b where group_id in

            (select group_id from groups
             where username = user);

A full table scan is used for:
             

select ...
from a_view, b_view
where a_view.foo (+) = b_view.foo;

And not for:

select ...
from a, b
where a.foo (+) = b.foo;

Unfortunately, the original authors of the software decided that Oracle views could be used to handle "group" security. It works pretty well, except for these cases. The application layer doesn't access the "tables" directly, but through the views and/or stored procedures.

I will probably write my own view to access the tables directly. Hopefully,
this will speed things up a bit.

-Allen

-- 
Allen Hadden
Systems Engineer
Taratec Development Corporation
ahadden_at_taratec.com