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).
