1 2 Назад Вперед 21 Ответы Последний ответ: 26.07.2017 17:25, автор: rp0428 Перейти к первоначальной записи
      • 15. Re: Moving rows to destination table and deleting from source in one go?
        BluShadow

        Yes, that could be an option for the select cursor in PL/SQL.

        I think however the addition of a DELETE WHERE... clause in the insert section of the existing MERGE statement would be simplest, just as it already exists in the update section, then you could conditionally delete rows from the source (or make the condition just delete all inserted rows).

        • 16. Re: Moving rows to destination table and deleting from source in one go?
          AndrewSayer

          This has come up before and prompted a lot of similarly interesting ideas and problems that need considering. Jonathan discusses a couple of ideas in https://jonathanlewis.wordpress.com/2016/11/22/deleteinsert/ the comments have a lot of other interesting points.

           

          If the amount of data is small (relative term) or you can do the task in batches then my suggestion of using 'delete returning into' in plsql as a starting point could be a good idea. This suggestion is discussed in the blog.

          • 17. Re: Moving rows to destination table and deleting from source in one go?
            BluShadow

            Interesting article Andrew.  Most of the "solutions" given though are about how to achieve this with the current technology.  I think, as rp said, there room for an enhancement request.  To me, MERGE is the obvious choice as it's already a single statement that has a primary purpose of moving data from one table to another regardless of whether it's updating or inserting.  The "locking" issue would then be something handled internally by Oracle and not something that developers would need to concern themselves with.

            • 18. Re: Moving rows to destination table and deleting from source in one go?
              AndrewSayer

              BluShadow wrote:

               

              Interesting article Andrew. Most of the "solutions" given though are about how to achieve this with the current technology. I think, as rp said, there room for an enhancement request. To me, MERGE is the obvious choice as it's already a single statement that has a primary purpose of moving data from one table to another regardless of whether it's updating or inserting. The "locking" issue would then be something handled internally by Oracle and not something that developers would need to concern themselves with.

              Certainly room for an enhancement.

              I think a generic "lock for current and inserted rows that match predicate" statement would be a useful addition.

              I would welcome an archival type statement too but I believe the locking statement would be a more generic tool.

               

              Sort of relevent: Oracle 12.2 offers filtered partition management statements https://docs.oracle.com/database/122/VLDBG/maintenance-partition-can-be-performed.htm#VLDBG-GUID-E5ACAFF8-AB4D-4165-9833… (also applies to move table) , currently it doesn't handle the newly inserted rows problem particuarly well either, although I'm struggling to find where I read that. Maybe it is a problem that Oracle would rather avoid.

              • 19. Re: Moving rows to destination table and deleting from source in one go?
                Nimish Garg

                In one go, it is not possible.

                 

                Earlier I use to bulk collect ROWID to be moved in PLSQL collection and use that collection for insert and delete operation.

                 

                Now a days, I am using following approach suggested by rp0428 in

                Re: Delete from table and insert same rows to other table

                 

                1. create a select for update cursor to lock the rows

                2. use a temp table to hold the ROWIDs

                3. use a  multitable insert to insert the data into the data table and the ROWIDs into the temp table

                4. use a delete based on the ROWIDs in the temp table

                5. commit to release the locks

                • 20. Re: Moving rows to destination table and deleting from source in one go?
                  Stew Ashton

                  Nimish Garg wrote:

                  ...

                  Now a days, I am using following approach suggested by rp0428 in

                  Re: Delete from table and insert same rows to other table

                   

                  1. create a select for update cursor to lock the rows

                  2. use a temp table to hold the ROWIDs

                  3. use a multitable insert to insert the data into the data table and the ROWIDs into the temp table

                  4. use a delete based on the ROWIDs in the temp table

                  5. commit to release the locks

                  That agrees with my proposal in reply 8. I didn't know rp0428 had already proposed the same thing in another thread. Thanks for the link.

                   

                  Best regards, Stew

                  • 21. Re: Moving rows to destination table and deleting from source in one go?

                    I think a generic "lock for current and inserted rows that match predicate" statement would be a useful addition.

                    I would welcome an archival type statement too but I believe the locking statement would be a more generic tool.

                    Almost (and I emphasize that 'almost') makes one yearn for the old 'desktop' days where tools like Delphi ALWAYS created subsidiary tables for statements.

                     

                    1. DELETE - would delete the rows and create a table named 'DELETED' (replaced the table if it existed) containing the rows deleted. If you made a mistake (there was no commit/rollback) you just inserted the rows in the 'deleted' table into the original table

                     

                    2. INSERT - would insert the rows and also create a table named 'INSERTED' containing the rows inserted

                     

                    The was ever only one table named DELETED or INSERTED and it was the one from the last such operation. Pretty much like an automatic temp table. Made it really easy to break a complex series of steps into simple single pieces and save the intermediate results (by renaming the deleted/inserted table).

                    1 2 Назад Вперед