Skip to Content
18
Dec 19, 2008 at 02:33 PM

For All Entries is NOT better than INNER JOIN in most cases

5432 Views

I quote from Siegfried Boes' excellent post here: Will writing an inner join be better or creating a view?

For all the FOR ALL ENTRIES lovers ... there is no proof for these reappearing recommendation.
There is nearly nobody who receives forum points, who recommends FOR ALL ENTRIES instead of Joins. What is the reason ???

It is easier to prove the opposite. A Join is a nested loop inside the database, a FOR ALL ENTRIES is partly outside of the database. FOR ALL ENTRIES works in blocks, joins on totals.

FOR ALL ENTRIES are not recommded on really large tables, because the chances are too high that
too many records are transferred.

People prefer FOR ALL ENTRIES, because JOINs are not so easy to understand. Joins can go wrong, but with a bit of understanding they can be fixed.

Some Joins are slow and can not be fixed, but then the FOR ALL ENTRIES would be extremely slow.

There are several kinds of views:

- projection views, i.e. only one table involved just fields reduced
- join views, several tables, joins conditions stored in dictionary 
- materialized views, here the joined data are actually stored in the database. Storing and synchronisation has to be done manually.

Only the last one creates real overhead. It should be the exception. 

Join Views and Joins are nearly identical. The view is better for reuse. The join is better in complicated, becuase if the access goes wrong, it can often be fixed by adding a hint. Hints can not be added to views.

Abraham Bukit points out:

If it is cluster table, (you can't use join). If it is buffered table, I would also say avoid join.
If they all are transaction table which are not buffered and are not cluster tables.

He further supports Siegfried's statement that FAE is easier to undestand than INNER JOINs.

Thomas Zloch says, regarding buffered tables:

At least think twice, maybe compare runtimes if in doubt.

So, unless someone has some EVIDENCE that FOR ALL ENTRIES is better, I don't think we want to see this discussed further.

Kind regards

Matt