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

Thanks Rob & Elvis,

I do, in fact, specify only the columns needed in this query, but wanted to
keep the example I presented here short and to the point, hence the *
usage. But I'm glad you brought this up, because I can never be reminded
too much about the importance of this. For quick & dirties, and one-timers,
it's such a PITA to type in all of the necessary columns. Very good info on
the index-only queries. I'll put that on my cheat sheet.
Thanks again,
Dan
On Thu, Apr 3, 2008 at 11:40 AM, Elvis Budimlic <
ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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