Prev: How to make DBA_AUDIT_TRAIL�s status to VALID
Next: Rman backup of BCV using ASM
From: Vladimir M. Zakharychev on 24 May 2010 13:24
On May 23, 9:17 pm, Ken Quirici <kquir...(a)yahoo.com> wrote:
> On May 23, 11:54 am, "Vladimir M. Zakharychev"
>
>
>
> <vladimir.zakharyc...(a)gmail.com> wrote:
> > On May 23, 6:45 pm, Ken Quirici <kquir...(a)yahoo.com> wrote:
>
> > > Hi,
>
> > > The following query:
>
> > > select
> > > player,
> > > sum(hp) thp
> > > from b_data
> > > where hp = 1
> > > group by player
> > > order by thp
> > > ;
>
> > > does not sort the results either by player or thp.
>
> > > Is there some logical reason for this, or is order by only
> > > designed to sort group by's by the group by column?
>
> > > Basically, how can I get it to sort by thp?
>
> > > Any help MUCH appreciated.
>
> > > Regards,
>
> > > Ken Quirici
>
> > select player, thp
> >   from (select player, sum(hp) thp
> >           from b_data
> >          where hp = 1
> >          group by player)
> >  order by thp;
>
> > To order by thp Oracle needs to "materialize" the inner view, so you
> > need to give it a chance to do so.
>
> > Hth,
> >    Vladimir M. Zakharychev
> >    N-Networks, makers of Dynamic PSP(tm)
> >    http://www.dynamicpsp.com
>
> Hi Vladimir,
>
> I got the database restructured, and the revised code debugged.
> I tried out a query w/ a nested query like you suggested and it
> worked. Many thanks!
>
> However I'm not sure what you mean by 'materialize' the inner view.
> If there were no group by, there wouldn't be an issue - the order by
> would 'materialize' whatever query it was a clause of, whatever
> 'materialize' means. Why does the group by mean the order by
> can't to the same thing, if instead of a nested query, I simply
> append the order by clause to the group by query?
>
> I'm hope you understand how I don't understand, if you know
> what I mean.
>
> Again many thanks. It's enabled my project to lurch forward!
>
> Regards,
>
> Ken Quirici

Ken,

My query was just a quick "patch" solution that would definitely work
regardless. Not knowing exact details of your database, your data and
your test case it was not possible to guess why it doesn't work as
expected without asking quite a few additional questions (see
below. :) ) I couldn't actually reproduce the issue as presented in
your post in a 10.1.0.4 (yes, that outdated,) database. Results are
correctly sorted as in Serge's example in both directions. What's your
Oracle version? What's the DDL and sample data? What was the plan for
the query and what was your output? In my test case (where it worked
correctly) the plan was:

SELECT STATEMENT
SORT ORDER BY
SORT GROUP BY
TABLE ACCESS FULL

Oracle automatically materialized the group by step before sorting it.
By "materializing" I mean that to perform the ORDER BY sort, Oracle
needs to fetch all rows to be sorted first and store them somewhere.
That's what we forced it to do by utilizing the inner view.

Anyway, if you can easily reproduce the issue, it looks like a bug
worth opening a SR with "wrong results from a query" tag.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
From: Ken Quirici on 25 May 2010 11:25
On May 24, 8:27 am, Serge Rielau <srie...(a)ca.ibm.com> wrote:
> Ken,
>
> I'm curious....
> Does is work if you do ORDER BY 2 (I.e. the second column)? Does it fail
> or give wrong results. The first would be a limitation, the second would
> be a bug...
>
> ORDER BY name resolution is supposed operate to operate on the SELECT
> list since, semantically it happens afterwards:
>
> db2 => create table b_data(player varchar2(10), hp NUMBER(5));
> DB20000I  The SQL command completed successfully.
> db2 => insert into b_data values ('Jo', 1), ('Jo', 1), ('Jill', 1),
> ('Jill', 1), ('Jill', 1);
> DB20000I  The SQL command completed successfully.
> db2 => select player, sum(hp) thp from b_data where hp = 1 group by
> player order by thp;
>
> PLAYER     THP
> ---------- ------------------------------------------
> Jo                                                  2
> Jill                                                3
>
>    2 record(s) selected.
>
> db2 => select player, sum(hp) thp from b_data where hp = 1 group by
> player order by thp desc;
>
> PLAYER     THP
> ---------- ------------------------------------------
> Jill                                                3
> Jo                                                  2
>
>    2 record(s) selected.
>
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab

Thanks guys! Much food for thought.

I'm restructuring everything, not for this issue, but because I'm
looking for a
better-structured solution to my project in terms of divide-and-
conquer.

When I have a chance I'll look into your questions and suggestions
further - given
that Vladimir's suggestion works, that's fine for the moment.

I don't want to give up on a theoretical understanding of the issue
( I
kind of figured 'materialized' was as Vladimir described it given the
difference between 'view' and 'materialized view', and the suggestions
about using explain plan to see exactly what Oracle thinks it's doing
I'm going to explore in those llittle bits of time I have available.

Thanks again!
From: Ken Quirici on 25 May 2010 11:27
On May 24, 8:27 am, Serge Rielau <srie...(a)ca.ibm.com> wrote:
> Ken,
>
> I'm curious....
> Does is work if you do ORDER BY 2 (I.e. the second column)? Does it fail
> or give wrong results. The first would be a limitation, the second would
> be a bug...
>
> ORDER BY name resolution is supposed operate to operate on the SELECT
> list since, semantically it happens afterwards:
>
> db2 => create table b_data(player varchar2(10), hp NUMBER(5));
> DB20000I  The SQL command completed successfully.
> db2 => insert into b_data values ('Jo', 1), ('Jo', 1), ('Jill', 1),
> ('Jill', 1), ('Jill', 1);
> DB20000I  The SQL command completed successfully.
> db2 => select player, sum(hp) thp from b_data where hp = 1 group by
> player order by thp;
>
> PLAYER     THP
> ---------- ------------------------------------------
> Jo                                                  2
> Jill                                                3
>
>    2 record(s) selected.
>
> db2 => select player, sum(hp) thp from b_data where hp = 1 group by
> player order by thp desc;
>
> PLAYER     THP
> ---------- ------------------------------------------
> Jill                                                3
> Jo                                                  2
>
>    2 record(s) selected.
>
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab

One last thing - I'm work on personal, proprietary software/database
with commercial
potential so I have to figure out how to present info on it. Thanks!

Regards,

Ken Quirici
First  |  Prev  | 
Pages: 1 2
Prev: How to make DBA_AUDIT_TRAIL�s status to VALID
Next: Rman backup of BCV using ASM