| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Creating a sorted table
Sure, and thanks. I appreciate the corrections, as one problem with doing this for a while is the aggregation of useless and no longer true "facts", which I'm finding I have more and more of. They feel like barnacles. :)
I've finally decided to go get my OCP, which I hope will help scrape some of the buggers off. This list always helps, too.
Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: Diana_Duncan_at_ttpartners.com
"Mohan, Ross"
<MohanR_at_STARS To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
-SMI.com> cc:
Sent by: Fax to:
root_at_fatcity. Subject: RE: Creating a sorted table
com
05/30/2001
01:01 PM
Please
respond to
ORACLE-L
Diana,
You might wanna check out www.kx.com Those guys have a lightning rocket of a dbms, and it is based, in part, on storing data in search/sort-dependent order. (There is a bunch of background data on the site itself.)
In any case, reading up on other dbms technologies has a place in any DBAs self-education. Else, we're just sitting around quoting anecdotes to one another!
Hope this helps,
Cheers,
Ross
|| -----Original Message-----
|| From: Christopher Spence [mailto:cspence_at_FuelSpot.com]
|| Sent: Wednesday, May 30, 2001 12:10 PM
|| To: Multiple recipients of list ORACLE-L
|| Subject: RE: Creating a sorted table
||
||
|| This is totally NOT accurate.
||
|| Yes there are performance gains storing index data ordered.
|| Perhaps great
|| on range scans. Yes you can reorder tables and indexes.
||
|| "Walking on water and developing software from a
|| specification are easy if
|| both are frozen."
||
|| Christopher R. Spence
|| Oracle DBA
|| Fuelspot
||
||
||
|| -----Original Message-----
|| Sent: Friday, May 25, 2001 7:41 PM
|| To: Multiple recipients of list ORACLE-L
||
||
||
|| 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
|| Sys Admin
|| City of Coquitlam
|| --
|| Please see the official ORACLE-L FAQ: 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: 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).
|| --
|| Please see the official ORACLE-L FAQ: http://www.orafaq.com
|| --
|| Author: Christopher Spence
|| INET: cspence_at_FuelSpot.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).
||
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: MohanR_at_STARS-SMI.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). -- Please see the official ORACLE-L FAQ: 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).Received on Wed May 30 2001 - 12:54:32 CDT
 |
 |