RE: SQL exception join: Left side now needs data combined fromtwo tables

Key data is in the index as well, only not likely to be in a contiguous
memory location as it is in a file. Instead it's likely to be spread across
several nodes (leafs). But we shouldn't get bogged down with internal index
implementation.

Bottom line is that you're saying that with SQL it is possible to get
index-only-access if you're only projecting columns contained within an
index. This is yet another reason to be explicit with which columns you
project in the SQL statement rather than using an asterisk (I know it's
easy, I use it all the time in my examples, but I avoid it in embedded SQL,
honest :)).

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: SQL exception join: Left side now needs data combined fromtwo
tables

As a general rule, avoid SELECT *. Select just the fields you need
instead. For example if you do a SELECT ICLAS, COUNT(*) AS NbrIclas FROM
IIM GROUP BY ICLAS and you have an index on iclas then the SQL optimizer
only has to read the index and never process the table. I know, I know,
the data is not in an index - it's in the table. Maybe I am saying it
wrong, but it just makes more sense in iNav's Visual Explain. Go listen
to Tom McKinley from IBM sometime and get the details. (John Sears for
the next generation.)
Joe, I am sure that you just said that because that wasn't the meat of
your explanation and you didn't want to get sidetracked. Maybe replace
the asterisk by an ellipses?

Rob Berendt