RE: STRSQL SELECT * on a logical file: keys not recognized -- MIDRANGE-L

Walden

I'd forgot about the hash methods for grouping. So I went to the database optimization manual and learned a few more things. Thanks for the push to dig a little more. It was interesting to try to determine why use indexes or hash tables. There are, as usual with SQL, several things that affect the decision. The most interesting was that hash table processing is usually sequential (arrival sequence), so it is easier to predict what pages need to brought in, allowing for asynchronous I/O, just like table scan. OTOH, indexed approaches tend to be random and therefore synchronous.

Vern

At 04:51 PM 3/21/2005, you wrote:

>It seems in my experience with non-parallel that grouping does sort -
>it has to sort things in order to get things in groups...

My experience agrees with yours, grouping does result in a sorted list,
but that's just a side effect of how they group.

As for the "it HAS to sort" part, I can't speak for DB2/400, but you
don't have to sort to group, just create enough buckets for your
distinct values and drop the values into the buckets as you go. Think of
"select state, count(*) from customer" you could sort on state and then
count as you go, or you could create 50 buckets and simply pound the
file in arrival order adding 1 to the approprite bucket for each row.
Heck, with parallelism you could even have several threads pound the
file (thread 1 from row 1 to 1M, thread 2 from row 1M+1 to 2M, thread
3...) and then add together the results at the end.

-Walden

------------
Walden H Leverich III
President & CEO
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.