Re: SQL question

Answers embedded
"Ed Stevens" <spamdump_at_nospam.noway.nohow> wrote in message news:3cadb33b.83445077_at_ausnews.austin.ibm.com...
> I just received this question from one of our developers: (my questions
will
> follow)
>
> <begin quote>
>
> SELECT nmm.DOWNTIME_HEADER.DTHDR_RUN_DTE
> FROM nmm.DOWNTIME_HEADER
> GROUP BY nmm.DOWNTIME_HEADER.DTHDR_RUN_DTE
> ORDER BY nmm.DOWNTIME_HEADER.DTHDR_RUN_DTE DESC;
>
> vs. this version
>
>
> select distinct dthdr_run_dte
> from nmm.downtime_header
> order by 1
>
> <end quote>
>
> Now, my first question is, what WOULD be the theoretical performance
difference
> between the two, given growth to "tens of thousands" of rows?

performance difference would be none as both selects have 2 sorts. In theory the GROUP BY is formally *NOT* guaranteed to provide an ordered result, nor is I believe the DISTINCT
>
> Second, my first impulse is to do an EXPLAIN and a TKPROF to see which is
> better, but the current data volumes are so low, I'm not sure these tools
would
> yeild anything useful.

They would yield identical plans right now, and a few months from now. By design GROUP BY and DISTINCT always end up in sort operations.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address



>