4 Replies Latest reply: May 29, 2009 1:21 AM by Toon Koppelaars RSS

    Moving Data from one table to another table

    414066
      Hi,
      I've a requirement to move large volumes (few billions) of data from one table to another empty target table. The target has more number of attributes and may not have same attribute name as the source. Please let me know what's the best method using oracle to move data in this case?

      Thanks in Advance,
      Tom
        • 1. Re: Moving Data from one table to another table
          21205
          how about
          insert /*+ append */
                into target (c1, c2, c3)
          select col1, col2, col3
            from source
          • 2. Re: Moving Data from one table to another table
            Kamran Agayev A.
            You should use nologging and parallelism options
            Please refer to Tom's answers:

            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:164612348068
            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330


            - - - - - - - - - - - - - - - - - - - - -
            Kamran Agayev A. (10g OCP)
            http://kamranagayev.wordpress.com
            [Step by Step install Oracle on Linux and Automate the installation using Shell Script |http://kamranagayev.wordpress.com/2009/05/01/step-by-step-installing-oracle-database-10g-release-2-on-linux-centos-and-automate-the-installation-using-linux-shell-script/]
            • 3. Re: Moving Data from one table to another table
              OrionNet
              Hello,

              If moving into the same datbase and same schema, your best option if using direct path insert /*+ APPEND */. Beside that I assume you will be using partitioning for target table and you will also have partitioning for source table. You can use compress and nologging option on target table.
              insert /*+APPEND */  into  target_part_table (col1, col2, col3) values (val1, val2, val3) where partition_key_column = 'value';
              
              commit;
              You can use datapump and export /import as well to move data but fastest will be using direct path insert

              Regards
              • 4. Re: Moving Data from one table to another table
                Toon Koppelaars
                Another good idea might be to make sure the target table has no indexes (or row insert triggers) when you insert the rows from the source into it.
                And after the data has been moved, build the indexes, re-enable the triggers.


                Toon