Re: SQL question

[PaulMmn <PaulMmn@xxxxxxxxxxxxx>]

Did your query have to create an access path?  Any sorting that has to
create a new index will take time--

--Paul E Musselman
PaulMmn@ix.netcom.com

-=-=-=-=-

>Howdy folks,
>
>I have a file with about 1/2 million records on our development
>machine (V4R2).
>
>I ran a SQL query from ISQL:
>-  Select distinct FIELD from FILE.
>The query ran in about 2 minutes and gave me the result set with
>about 200,000 records. FIELD is not a key field.
>
>I ran another query from ISQL over the same file/field:
>- Select count(distinct FIELD) from FILE.
>This query ran for over 10 minutes and returned the count.
>Why would this query take so much longer than the first query? I ran the
>test a few times when there was hardly any activity on the system and
>got the same results. The 2nd query takes atleast 5 times as long
>to complete.
>We have a similar table(few hundred thousand rcds)on an RS6000/Oracle
>database. I ran similar queries over Oracle and there was no noticeable
>difference in run times. I chose a non-indexed field.
>What is so different about DB2/400 that would cause this? Just
>curious...
>
>Thanks
>
>Ravi


+---
| 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
+---