Francois aka broesel2003 wrote:

>select
> p.personid, p.lastname,p.middlename, p.prename
>from
> person p left outer join
> employee e on (e.personid=p.personid)
>
>This makes a full table scan as i see in performance
>analysis on table person.
>If i simply add a nonsense where clause like this, it works
>well:
>
>where p.personid > 0

OK. Is there a measurable performance difference or do you just see the
difference when you look a the plan? If there's a measurable difference in
the time it takes to execute the query, then there's a problem. If,
however, you're concerned that the plans are different, then you haven't
thought the problem through.

What you have asked Firebird is "Tell me the name and serial number of
everybody. Check to see if each person is an employee, but don't do
anything different for employees." Firebird says to itself, "Well, old
bird, he wants information about everybody, so lets get everybody in the
most efficient way possible." The most efficient way to get all the
records in a table is a full table scan. A slightly cagier bird would
realize that you're not doing anything with the information from the
employee table and not bother with that check, but this bird was brought up
to be smart, not cagey.

If you asked for e.personid rather than p.personid, you'd be asking for the
name of everybody and the serial number of those people who are
employees. Non-employees would have a null value for their serial
number. Since you would still be asking about all people, a full table
scan would still be appropriate.

If your query were an inner join rather than a left outer join, you would
be asking Firebird "Tell me the name and serial number of
employees." Firebird says "OK, I can look at people and take only those
who are employees, or I can look at employees and take only those who are
people. Are there more employees or more people?" It will then do a full
table scan of the smaller table, using the serial number to look up values
in the larger table. If your query included the additional condition
"where e.dept = 'Funny Walks', Firebird would say, "Ah, I have another
clue. Is there an index on dept in employees?" If so, it uses the
department index to select a sub-set of employees, then looks up the
personal information for that subset of records.

Regards,


Ann