Efficient Alternative to Outer Join

Question

The RIGHT JOIN on this query causes a TABLE ACCESS FULL on lims.operator. A regular join runs quickly, but of course, the samples 'WHERE authorised_by IS NULL' do not show up.

Is there a more efficient alternative to a RIGHT JOIN in this case?

  SELECT   full_name
  FROM       (SELECT   operator_id AS authorised_by, full_name
                FROM lims.operator)
  RIGHT JOIN (SELECT   sample_id, authorised_by
                FROM   lims.sample
               WHERE   sample_template_id = 200)
  USING (authorised_by)

NOTE: All columns shown (except full_name) are indexed and the primary key of some table.

Answer 1

No need to nest queries. Try this:

select s.full_name
from lims.operator o, lims.sample s
where o.operator_id = s.authorised_by(+)
and s.sample_template_id = 200

Answer 2

I didn't write sql for oracle since a while, but i would write the query like this:

SELECT lims.operator.full_name
FROM       lims.operator
RIGHT JOIN lims.sample
           on lims.operator.operator_id = lims.sample.authorized_by
           and sample_template_id = 200

Does this still perform that bad?

Answer 3

Since you're doing an outer join, it could easily be that it actually is more efficient to do a full table scan rather than use the index.

If you are convinced the index should be used, force it with a hint:

SELECT /*+ INDEX (lims.operator operator_index_name)*/ ...

then see what happens...