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:[EMAIL PROTECTED]]
|| 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"
||
|| <[EMAIL PROTECTED] To:
|| Multiple recipients
|| of list ORACLE-L <[EMAIL PROTECTED]>
|| lam.bc.ca> cc:
||
|| Sent by: Fax to:
||
|| [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
||
|| 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
||
|| 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
||
|| 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).