Re: Creating a sorted table

From: <Diana_Duncan_at_ttpartners.com>
Date: Fri, 25 May 2001 15:35:11 -0700
Message-ID: <F001.00310546.20010525154101@fatcity.com>

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

Received on Fri May 25 2001 - 17:35:11 CDT