SCOUG-Programming Mailing List Archives

<< Previous Message << >> Next Message >>

Date:	Sun, 26 Mar 2006 18:05:30 PST8
From:	Sheridan George <s-geo@usa.net >
Reply-To:	scoug-programming@scoug.com
To:	scoug-programming@scoug.com
Subject:	SCOUG-Programming: Data Repository/Directory

Content Type: text/plain

Knowledgeman was definitely legacy (I started using it two years before OS/2 first came out) but it
used SQL in its queries. It operated under the rule that if at least one index had been described
for that table all queries came back in index order unless the "order by" clause was added. Using
your examples:

select indexed_column from table would produce the result in [that] index's order.

select non-indexed_column from table (that has 1 or more indexes described) would produce the result
in primary index order. (If more than one index was described, one of them was designated the
primary index to be used if an order by clause was not part of the query.)

select indexed_column from table order by [index] would produce the result in that index's order.
(Note: this result is the same as example 1).

select indexed_column from table order by [some function] would produce the result in that
function's order. (Here the index simply was a faster way to choose rows. It didn't affect ordering.)

Knowledgeman's idea of indices was quite broad. One didn't specify a particular column to be the
index. Indexes were separately named (any name) and would index based on a column, a portion of a
column's data, several columns, or even calculated columns. (A calculated column had to be an
actual database column populated by some function of other columns not simply a calculation in the
query.)

Sheridan

Steven Levine wrote:
> In <44259647.1090900@usa.net>, on 03/25/06
> at 10:41 PM, Sheridan George said:
>
>
>>In the databases I've used the retrieved rows are unordered unless the
>>retrieval is via an index.
>
>
> You and Greg are probably thinking of legacy databases which don't
> understand concepts like join. Modern SQL style databases always allow
> the result set rows to be sorted, as needed, regardless of whether or not
> the column is indexed. What is typically true is that if you don't
> specify the sort, the row order is undefined. This is often true even for
> selections based on an indexed column. For example in SQL
>
> select indexed_column from table
>
> and
>
> select indexed_column from table order by indexed_column
>
> may order the result set differently.
>
> It is best to think of indexes as performance tuning tools rather than
> access tools.
>
> Regards,
>
> Steven
>

=====================================================

To unsubscribe from this list, send an email message
to "steward@scoug.com". In the body of the message,
put the command "unsubscribe scoug-programming".

For problems, contact the list owner at
"postmaster@scoug.com".

=====================================================

Return to [ 26 | March | 2006 ]

SCOUG, Warp Expo West, and Warpfest are trademarks of the Southern California OS/2 User Group. OS/2, Workplace Shell, and IBM are registered trademarks of International Business Machines Corporation. All other trademarks remain the property of their respective owners.