| 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
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;
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-LReceived on Wed May 30 2001 - 10:10:58 CDT
(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).
 |
 |