Re: SQL order by question

[pytel@xxxxxxxxxx]

If there's an access path which SQL can use for ORDER BY and/or selection,
SQL will use it.
It does not matter whether you put specific logical file in FROM clause -
this will be done automatically by query optimizer.

Actually, this is one of the first steps of optimizing SQL applications -
run SQL statements, look what indexes SQL will want to process them and
build these indexes statically.
You will rarely need to modify your actual SQL statements - this "external"
optimization is a beauty of SQL.

As for FOR FETCH ONLY clause:
I do not really understand what you mean by "you want the time it takes to
build the table to be less??"
Anyway, my rule is - if I want smth about my application, it's always good
to make it known to optimizer.
If you know that you will not update the cursor, then tell so - use FOR
FETCH ONLY.
This gives more degrees of freedom to query optimizer in choosing
implementation for SELECT.


Best regards
    Alexey Pytel



Gary Lehman <Gary_Lehman@mail.mchcp.org> on 01/19/99 09:08:05 AM

Please respond to MIDRANGE-L@midrange.com

To:   "Midrange Mailing List (E-mail)" <MIDRANGE-L@midrange.com>
cc:    (bcc: Alexei Pytel/Rochester/IBM)
Subject:  SQL order by question





Hello all you SQL gurus,

I use SQL quite a bit, but never really got down to the intricate question
of what really works more efficiently.  My question is(may sound simple),
if
you do a DECLARE CURSOR on a logical that is in say SSN, DATE, PRODUCT
order
and then do a order by SSN, DATE, PRODUCT order does that cause it to do
more work to create an extra index??  If the logical is already in that
order is it detrimental to do an order by with the same fields?  I probably
already know the answer to this, but I wanted to double check.

Also do you specify a FETCH ONLY on a DECLARE CURSOR if you're not doing
any
updating and you want the time it takes to build the table to be less??

Gary Lehman
Programmer Analyst II
Missouri Consolidated Health Care Plan

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---



+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---