6 Replies Latest reply: Dec 10, 2012 9:51 AM by BluShadow RSS

    insert into local table as select from remote tables

    a.stoyanov
      Hi all,
      In Oracle DB version 11g i have the following issue:
      I want to insert into a table in the current schema as selecting data from two remote tables. I execute the insert in portions of data. Firstly, when the target table where i want to insert is empty the Select as Insert is being executed very fast. But after every insert i made , the performance became worse than the previous one. I have no FKs or indexes in the local table/target table where i'm inserting/... I tried using /*+ append*/ hint but no success...what should be the reason of that?

      Thanks in advance,
      Alexander.
        • 1. Re: insert into local table as select from remote tables
          sb92075
          a.stoyanov wrote:
          Hi all,
          In Oracle DB version 11g i have the following issue:
          I want to insert into a table in the current schema as selecting data from two remote tables. I execute the insert in portions of data. Firstly, when the target table where i want to insert is empty the Select as Insert is being executed very fast. But after every insert i made , the performance became worse than the previous one. I have no FKs or indexes in the local table/target table where i'm inserting/... I tried using /*+ append*/ hint but no success...what should be the reason of that?

          Thanks in advance,
          Alexander.
          It takes time to brings rows across the network.
          • 2. Re: insert into local table as select from remote tables
            a.stoyanov
            Hi,
            Thanks for the quick response. We noticed that but is there any workarounds?

            Thanks,
            Alexander.
            • 3. Re: insert into local table as select from remote tables
              6363
              a.stoyanov wrote:

              We noticed that but is there any workarounds?
              Don't do this
              I execute the insert in portions of data.
              Just one insert for all data.
              • 4. Re: insert into local table as select from remote tables
                Paul  Horth
                a.stoyanov wrote:
                Hi all,
                In Oracle DB version 11g i have the following issue:
                I want to insert into a table in the current schema as selecting data from two remote tables. I execute the insert in portions of data. Firstly, when the target table where i want to insert is empty the Select as Insert is being executed very fast. But after every insert i made , the performance became worse than the previous one. I have no FKs or indexes in the local table/target table where i'm inserting/... I tried using /*+ append*/ hint but no success...what should be the reason of that?

                Thanks in advance,
                Alexander.
                How should we know? You don't give enough information to be able to tell. Not even the SQL involved.

                Please read {message:id=9360002} and {message:id=9360003}
                and follow the advice given.
                • 5. Re: insert into local table as select from remote tables
                  sb92075
                  a.stoyanov wrote:
                  Hi,
                  Thanks for the quick response. We noticed that but is there any workarounds?

                  Thanks,
                  Alexander.
                  the only "workaround" is to have the data local so it is not brought across the network;
                  but realize that to get the data local it will have to be shipped across the network.
                  • 6. Re: insert into local table as select from remote tables
                    BluShadow
                    sb92075 wrote:
                    a.stoyanov wrote:
                    Hi,
                    Thanks for the quick response. We noticed that but is there any workarounds?

                    Thanks,
                    Alexander.
                    the only "workaround" is to have the data local so it is not brought across the network;
                    but realize that to get the data local it will have to be shipped across the network.
                    Unless you can pull the hard disk out and install it locally. :D