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).
RE: Creating a sorted table
Title: RE: Creating a sorted table
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:[EMAIL PROTECTED]]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:[EMAIL PROTECTED]]
Reply To:
[EMAIL PROTECTED] 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
--- [EMAIL PROTECTED] 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"
[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).
= Connor
McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.u
RE: Creating a sorted table
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
OT (everybody has a bad day once in a while...)/ RE: Creating a sorted table
On 30 May 2001, at 8:10, Christopher Spence wrote: This is totally NOT accurate. http://www.amazon.com/exec/obidos/ASIN/B5BYQH ---excerpt--- Tales of the Arabian Nights Barbie and Ken Our Price: $99.99 This item will be released on June 29, 2001. You may order it now and we will ship it to you when it arrives. Get a free Air Hogs Hydro water rocket with purchase of $50 or more of toys and/or video games. Box Size (in inches): 13.0 x 14.0 x 14.0 Shipping: Currently, item can be shipped only within the U.S. Shipping weight: pounds. ASIN: B5BYQH SKN: 267719 From Toysrus.com Amazon.com From the Manufacturer Barbie doll and Ken doll re-create the romantic legend of how Scheherazade brought peace to a trouble Sultan with 1001 nights of captivating stories. Barbie as Sheherazade is absolutely ravishing in a spectacularly patterned skirt, with matching top, embellished with golden highlights. She wears pink and blue veils in her hair that spiral gracefully around her, adding an air of mystery. Ken as the Sultan is Barbie doll's *dashing companion*. He wears a *pink tunic* with golden trim over billowy golden pants. A blue and purple sash ties at his waist and serves as a place to rest his ***trusty sword***. His colorful turban shines with a *faux ruby* and is topped with a *golden plume*. ---end--- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce 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).
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: [EMAIL PROTECTED] Mohan, Ross MohanR@STARSTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] -SMI.comcc: Sent by: Fax to: root@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:[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
RE: Creating a sorted table
Title: 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:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] 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 --- [EMAIL PROTECTED] 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 [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). = Connor McDonald http://www.oracledba.co.uk (mirrored at 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 http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
Re: Creating a sorted table
Alternately on earlier versions where the order by can't be used, is to select from the table in indexed order using a hint... hth connor --- Regina Harter [EMAIL PROTECTED] wrote: Well, it won't work in all cases, but I have on occasion used as a shortcut: INSERT INTO ... SELECT DISTINCT transaction_date, ... since the distinct will order it for you, beginning with the first item in the select. A more reliable way would be to use pl/sql, select the ordered data into a cursor, then insert one by one. At 02:45 PM 5/25/01 -0800, you wrote: 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: Regina Harter 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at 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 http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
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 --- [EMAIL PROTECTED] 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 [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). = Connor McDonald http://www.oracledba.co.uk (mirrored at 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 http://mail.yahoo.co.uk or your free @yahoo.ie
Re: Creating a sorted table
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).
RE: Creating a sorted table
Try insert ... select* from (select * from table_name order by column_name) Alex Hillman -Original Message- Sent: Friday, May 25, 2001 6:45 PM To: Multiple recipients of list 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: Hillman, Alex 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).
Re: Creating a sorted table
Well, it won't work in all cases, but I have on occasion used as a shortcut: INSERT INTO ... SELECT DISTINCT transaction_date, ... since the distinct will order it for you, beginning with the first item in the select. A more reliable way would be to use pl/sql, select the ordered data into a cursor, then insert one by one. At 02:45 PM 5/25/01 -0800, you wrote: 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: Regina Harter 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).
