Re: Order by Problem on 8i

You are correct. (at least on an initial data load(I believe) - Don't have Jonathan's book in front of me - I left it at work, I'll look at it tomorrow; yes, it is good reading)

What I should have said is that don't insert them in a particular order with the expectation that by default you will get them back in that order without specifying a particular order. Often get people expecting a particular order and they don't specify one. Seen whole threads where people "complain" because database X retrieves rows in the "correct" order without an order by clause and database Y does not and management won't let the poor sod use an order by clause in database Y so how to fix. (enlighten management)
Thanks,
Jim

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3bdf5321$0$25892$afc38c87_at_news.optusnet.com.au...
> There are perfectly justifiable grounds for loading data with an order by
> clause. It's possible that queries have to read fewer blocks to retrieve
> the requisite data as a result of re-packing the data into a
> query-appropriate order.
>
> You might, for example, care to re-read pages 67 and 68 of Jonathan Lewis'
> "Practical Oracle 8i", which illustrates the point.
>
> Not perhaps that that is relevant to the original poster, but saying it
just
> causes extra work for no benefit is oversimplifying things a bit.
>
> Regards
> HJR
> --
>
> Oracle Resources : http://www.geocities.com/howardjr2000
> ========================================
>
>
> "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
> news:H5ID7.2035$Fm5.1250544_at_news1.sttln1.wa.home.com...
> > no. If it works it is just causing the machine to do more work for no
> > benefit.
> > Jim
> > "Bliss" <bliss_is_ignorance_at_hotmail.com> wrote in message
> > news:3bdee76c$1_1_at_news.tm.net.my...
> > > A friend just brought to my notice that
> > >
> > > CREATE TABLE .... AS SELECT .... ORDER BY does work... will that help
in
> > any
> > > way????
> > >
> > > Regards,
> > > Bliss
> > >
> > > "Bliss" <bliss_is_ignorance_at_hotmail.com> wrote in message
> > > news:3bd83ba3_1_at_news.tm.net.my...
> > > > Hi,
> > > >
> > > > Though your script works in 9i, it seems a little illogical to
me....
> > How
> > > > can you INSERT using ORDER BY? What will you achieve by doing
this???
> > > > Remember that Oracle does not display data in order it is inserted
> when
> > > you
> > > > give a SELECT statement. If you can specify what you are trying to
> > > achieve,
> > > > may be there'll be a solution...
> > > >
> > > > Regards,
> > > > Bliss
> > > >
> > > > "tIM" <tjm_at_geac.dk> wrote in message
> > > > news:b1f8412c.0110250426.69c05a26_at_posting.google.com...
> > > > > Hi I am faced with a problem ...
> > > > >
> > > > > If I run the following code on Oracle 8i the create procedure
fails.
> I
> > > > > can't figure out why because the command alone works!!!
> > > > >
> > > > > Note that this works on 9i (is this a confirmed bug with a patch
or
> > > > > anything!!!?!?)
> > > > >
> > > > > TIA
> > > > >
> > > > > CREATE TABLE TEST1(
> > > > > test1 number(1));
> > > > >
> > > > > CREATE TABLE TEST2(
> > > > > test1 number(1));
> > > > >
> > > > >
> > > > >
> > > > > insert into test1 (test1) select test1
> > > > > FROM
> > > > > test2
> > > > > WHERE
> > > > > test1='ss'
> > > > > ORDER BY test1;
> > > > >
> > > > > CREATE PROCEDURE TEST
> > > > > IS
> > > > > BEGIN
> > > > > insert into test1 (test1) select test1
> > > > > FROM
> > > > > test2
> > > > > WHERE
> > > > > test1='ss'
> > > > > ORDER BY test1;
> > > > > END;
> > > > > /
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Tue Oct 30 2001 - 19:59:03 CST