10 Replies Latest reply: Feb 2, 2013 9:15 AM by Etbin RSS

    Is there any reason to ...

    Etbin
      After meeting yesterday's deadline I'm browsing today's two hours AWR report to relax from previous continuous looking into the same package and it's Friday after all.
      I stumbled into something like:
      update (select some_column
                from some_table
               where the_source = :our_source
                 and the_date = :our_date
                 and the_transaction_id = :our_transaction_id
               order by the_account,the_currency,the_group_1,the_group_2
             )
         set some_column = :new_value
      Not having the faintest idea what that stands for and knowing that in the house my question will be judged provocative almost for sure I'm asking the forum:
      why not just
      update some_table
         set some_column = :new_value
       where the_source = :our_source
         and the_date = :our_date
         and the_transaction_id = :our_transaction_id
      Can someone forsee any particular reason for such an update ? (as the first one above)

      Regards

      Etbin
        • 1. Re: Is there any reason to ...
          AlbertoFaenza
          Hi Etbin,

          unless the inline view has a join with more tables, I can draw the same conclusions as you.
          Hence I don't see any particular reason to use the first statement.

          Regards.
          Al
          • 2. Re: Is there any reason to ...
            bencol
            I've used that form of update when updating a table based on the contents of 1 or more other tables, and never with an order by. I can see no benefit with doing it for a single table
            • 3. Re: Is there any reason to ...
              Bawer
              Alberto Faenza wrote:
              Hence I don't see any particular reason to use the first statement.
              try second statement with a nested table. (also update only a column of a nested table)
              • 4. Re: Is there any reason to ...
                Etbin
                Thanks all, there seem to be no benefit, but can there be any drawbacks using that kind of update ?
                It's been a week I was given the permission to use a read only version of Enterprise Manager Cloud Control 12 c (I started today)
                The application containing the update sticks out on buffer waits if this can mean something to someone.

                Regards

                Etbin

                Edited by: Etbin on 1.2.2013 22:49
                Additional facts - to keep the post alive ;) (some might have missed it)
                On Exadata: updating about 1000 rows of over 3 million rows table (HCC compressed for Archive High with no index) there's no noticeable difference between the two updates (first run 7 seconds, subsequent runs between .6 and .8 seconds for both with no particular pattern)
                • 5. Re: Is there any reason to ...
                  rp0428
                  >
                  Thanks all, there seem to be no benefit, but can there be any drawbacks using that kind of update ?
                  >
                  Not sure why there would be benefit or any drawbacks since those two queries are exactly the same aren't they?

                  This sample shows the same plan hash value being used for the two queries I created that are similar to the ones you posted.
                  SQL> set serveroutput on
                  SQL> set autotrace on
                  SQL> update emp_copy set ename = 'test' where empno = 7369
                    2  /
                  
                  1 row updated.
                  
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 1880658981
                  
                  -------------------------------------------------------------------------------
                  | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                  -------------------------------------------------------------------------------
                  |   0 | UPDATE STATEMENT   |          |     1 |    10 |     3   (0)| 00:00:01 |
                  |   1 |  UPDATE            | EMP_COPY |       |       |            |          |
                  |*  2 |   TABLE ACCESS FULL| EMP_COPY |     1 |    10 |     3   (0)| 00:00:01 |
                  -------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     2 - filter("EMPNO"=7369)
                  
                  
                  Statistics
                  ----------------------------------------------------------
                           45  recursive calls
                            3  db block gets
                           52  consistent gets
                            0  physical reads
                            0  redo size
                          745  bytes sent via SQL*Net to client
                          864  bytes received via SQL*Net from client
                            4  SQL*Net roundtrips to/from client
                            5  sorts (memory)
                            0  sorts (disk)
                            1  rows processed
                  
                  SQL> rollback;
                  
                  Rollback complete.
                  
                  SQL> update (select ename
                    2            from emp_copy
                    3           where empno = 7369
                    4         )
                    5     set ename = 'test'
                    6  /
                  
                  1 row updated.
                  
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 1880658981
                  
                  -------------------------------------------------------------------------------
                  | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                  -------------------------------------------------------------------------------
                  |   0 | UPDATE STATEMENT   |          |     1 |    10 |     3   (0)| 00:00:01 |
                  |   1 |  UPDATE            | EMP_COPY |       |       |            |          |
                  |*  2 |   TABLE ACCESS FULL| EMP_COPY |     1 |    10 |     3   (0)| 00:00:01 |
                  -------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     2 - filter("EMPNO"=7369)
                  
                  
                  Statistics
                  ----------------------------------------------------------
                            1  recursive calls
                            3  db block gets
                            7  consistent gets
                            0  physical reads
                            0  redo size
                          745  bytes sent via SQL*Net to client
                          914  bytes received via SQL*Net from client
                            4  SQL*Net roundtrips to/from client
                            1  sorts (memory)
                            0  sorts (disk)
                            1  rows processed
                  
                  SQL> rollback;
                  
                  Rollback complete.
                  
                  SQL>
                  Note 'Plan hash value: 1880658981' is the same for both of those queries. Oracle considers them to be the same.

                  See the Using Subqueries section of the SQL Language doc
                  http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries007.htm
                  >
                  Use subqueries for the following purposes:

                  •To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement

                  •To define the set of rows to be included in a view or materialized view in a CREATE VIEW or CREATE MATERIALIZED VIEW statement

                  •To define one or more values to be assigned to existing rows in an UPDATE statement <--- this is the one

                  •To provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECT, UPDATE, and DELETE statements

                  •To define a table to be operated on by a containing query

                  You do this by placing the subquery in the FROM clause of the containing query as you would a table name. You may use subqueries in place of tables in this way as well in INSERT, UPDATE, and DELETE statements.
                  >
                  You specified 'some_table' in your example but I have used that syntax when updating an updateable view that joins two tables together.

                  The subquery makes it easier for me to make sure that only the columns of the table I want to update are included in the query.

                  But that is just my preference.
                  • 6. Re: Is there any reason to ...
                    sql_coder
                    Etbin wrote:
                    Can someone forsee any particular reason for such an update ? (as the first one above)
                    WITH CHECK option it would prolly make a sense....

                    Ikrischer
                    • 7. Re: Is there any reason to ...
                      rp0428
                      If you want to be helpful you need to post information that supports the statements you make so people can understand why you are saying that.
                      • 8. Re: Is there any reason to ...
                        Etbin
                        Many thanks for the demonstration.
                        Now you got me unprepared to submit further facts. :(
                        As said I just noticed it. I don't know what is all about and it's something I don't have access to. I have only done a wanna be translation and posted it thinking it might represent a particular approach, say an advanced nuance that I'm not aware of, but most probably chances are the forum might be able to say something about it even without the usually necessary information provided.
                        Now all I can say (being pretty sure of) is the select is meant to return multiple rows. The <tt>the_transaction_id</tt> in this particular case should be read as <tt>the_batch_id</tt>. Sorry, my bad, but within a company the word transaction is not used strictly to denote the work done between two combinations of commit and/or rollback, nobody talks about atomicity ... etc. (I should have thought about it in the first place)
                        Running the two updates in the Test Database on some (a bit bigger) table the plans differed only for the sort operation which provided for 1 as the cost difference. The timings (few repeated/mixed executions of both) did not reveal any fact that could be used to tell one being superior to the other, just random fluctuations.
                        As for the rest, now I'm not very sure whether the select is done from the table or from the view (according to some naming conventions should be a table, but you never know ...)
                        If I find a time I'll try to investigate it deeper. I might be able to obtain more info through the dictionary to set up a test case, but I must see to avoid the possibility of others taking as an act of conspiracy.

                        Regards

                        Etbin
                        • 9. Re: Is there any reason to ...
                          Peter Gjelstrup
                          Hi Etbin,

                          For what it's worth, I sometimes write my updates like that, update (inline view) set ..

                          The reason is that I first work with select, and once it does what I want it to, I simply wrap it in update, anticipating there will be no difference (Haven't actually checked as far as I remember)

                          The case you demonstrate I find a bit strange due to the simplicity of the select, but stilll ....

                          The order by would then be something I forgot to remove ;)


                          Regards
                          Peter
                          • 10. Re: Is there any reason to ...
                            Etbin
                            Thanks, I subscribe to that.
                            I sometimes do the same (but never when dealing with a single table).
                            I'm in somehow awkward position now. Years (6-7 leaving 8i) ago I suggested to abandon leaving old rows in the same table and using a history table instead, to possibly abandon row-by-row (loops within loops within loops ...) processing, not to use "standard" <tt>lpad(to_char(some_sequence.nextval),16,'0')</tt>, to restrict the use of triggers in favor of stored procedures or at least not writing them being as single-task-single-user type, not keeping queries within the client, to impose constraints as mandatory, to review the use of indexes, to use partitioned tables, to use IOTs (I still don't have create cluster privilege granted), (some less years ago using 10g) to get xmldb installed, to use Apex, not to proceed in EAV direction, to use Flashback, RLS, FGA, CDC, ... I even mentioned Total Recall. But the only result was that, in spite of good intentions, the "devil's advocate" role made me a sort of black sheep and sadly enough enabled them to build a don't-know-how-to-call-it Database that certainly does not scale. Moving to Exadata as you may imagine didn't bring substantial improvement and now, whatever it is, here it is.
                            I can live with it quite easily as I can, with my consciousness clear (because of all those years old e-mails), sleep at night, but it seems others don't, might be because they are not willing to admit it for now (the next step could be accepting responsibilities).
                            Things I hoped would happen after I retire are happening already and I'm not very comfortable with that, revenge (stressing the fact that I was right) is not worth a free man as there's no easy way out (every irresponsible decision might seriously jeopardize the company). I told the management to look/ask around - outside the company (possibly not the competitors - two Oracle Aces for our country should be more than enough) - and not trying to make me some kind of prophet being able to set everything just because I might know some good practice everybody (themselves included) has a possibility to know.
                            The present conditions dictate a kind of underground actions (that's why this post) in order not to endanger the good spirit supposed to be reigning all around here embarrassing the presumably good intentioned superiors.

                            Regards

                            Etbin