RE: Creating a sorted table

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Wed, 30 May 2001 08:19:13 -0700
Message-ID: <F001.003139A6.20010530081648@fatcity.com>

That
is version specific, but you can use an index hint to do this in older versions.

"Walking on water and developing software from a specification are easy if both are frozen." Christopher R. Spence Oracle DBA Fuelspot

-----Original Message-----From: Vijay_Krishna [mailto:Vijay_Krishna_at_satyam.com]Sent: Tuesday, May 29, 2001 7:00 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Creating a sorted table
 Hi infact creating the table as sorted data from another table works with oracle 8.1.6.3.0 as: create table agrs as
 select * from agreements order by agr_agreement_number desc; Is this OK??
 Vijay

From: Connor McDonald[SMTP:hamcdc_at_yahoo.co.uk] Reply To: ORACLE-L_at_fatcity.com Sent: Monday, May 28, 2001 4:05 PM To: Multiple recipients of list ORACLE-L Subject: Re: Creating a sorted table There is a very good reason for having data "approximately" in physical order - it can dramatically improve your buffer hit rates. IOT's are great for this, but if you're on an earlier version then the occasional job to "pseudo-cluster" the data can be a very good thing... Cheers Connor
- Diana_Duncan_at_ttpartners.com wrote: >
 > Whyever would you want data inserted
 in order? > There is no guarantee that > Oracle will actually store the records "in order", > there is no performance > gain, and you can always retrieve the records in > order by using an order by > statement -- if you really need ordered data, you > could use a > index-organized table with all of your columns, with > the date as the first > column. But methinks this would be dangerous for a > heavy transaction > table. (Gurus, please correct me if I'm wrong here) > > However, if you are still keen, you could do this > through a PL/SQL block, > something like the following: >
 > declare > cursor get_data is > select col1, col2, col3, ... > from unordered_table > order by whatever; > begin
 > for dataRec
 in get_data loop > insert into ordered_table (col1, col2, > col3, ...) > values (dataRec.col1, dataRec.col2, > dataRec.col3, ...) > end loop; > end; > /
 > >
 Cheers! > > Diana > > > > > > > "Browett, Darren" > > > <dbrowett_at_city.coquit To: > Multiple recipients of list ORACLE-L > <ORACLE-L_at_fatcity.com> > lam.bc.ca> cc: > > > Sent by: Fax > to: > > root_at_fatcity.com
 > Subject:
 Creating a sorted table > > > > > > > > 05/25/2001 06:45 PM > > > Please respond to > > > ORACLE-L > > > > > > > >
 > > > > > We have un-ordered data in a table that needs to be > inserted into a > transaction table in > order of the date that the transaction took place. > > Oracle does not allow "INSERT ..... AS SELECT ..... > ORDER BY....." > or "CREATE TMP_TABLE ..... AS SELECT ..... ORDER > BY......" > > Is there a method by which I can accomplish this. > > Thank you in advance >
 > Darren Browett <FONT
 face=Arial size=2>> Sys Admin > City of Coquitlam > --
 > Please see the official ORACLE-L
 FAQ: > <A target=_blank href="http://www.orafaq.com">http://www.orafaq.com > -- > Author: Browett, Darren > INET: dbrowett_at_city.coquitlam.bc.ca >
 > Fat City Network
 Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California
 - Public Internet > access / Mailing Lists > --------------------------------------------------------------------
 > To REMOVE yourself from this mailing list,
 send an > E-Mail message
 > to: ListGuru_at_fatcity.com (note EXACT
 spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L
 > (or the name of mailing list you want to be
 removed > from). You may
 > also send the HELP command for other
 information > (like subscribing). > > >
 > > -- > Please see the official ORACLE-L FAQ: > <A target=_blank href="http://www.orafaq.com">http://www.orafaq.com > -- > Author:
 > INET:
 Diana_Duncan_at_ttpartners.com >
 > Fat City Network
 Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California
 - Public Internet > access / Mailing Lists > --------------------------------------------------------------------
 > To REMOVE yourself from this mailing list,
 send an > E-Mail message
 > to: ListGuru_at_fatcity.com (note EXACT
 spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L
 > (or the name of mailing list you want to be
 removed > from). You may
 > also send the HELP command for other
 information > (like subscribing).
 - Connor McDonald <A target=_blank href="http://www.oracledba.co.uk">http://www.oracledba.co.uk (mirrored at <A target=_blank href="http://www.oradba.freeserve.co.uk">http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" ____________________________________________________________ Do You Yahoo!? Get your free @yahoo.co.uk address at <A target=_blank href="http://mail.yahoo.co.uk">http://mail.yahoo.co.uk or your free @yahoo.ie address at <A target=_blank href="http://mail.yahoo.ie">http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: <A target=_blank href="http://www.orafaq.com">http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: hamcdc_at_yahoo.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

Received on Wed May 30 2001 - 10:19:13 CDT