Here’s an execution plan from a recent thread on the Oracle developer forum:
------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | UPDATE STATEMENT | | 1 | 231 | 160 (6)| 00:00:02 | | 1 | UPDATE | GS_TABLE | | | | | |* 2 | HASH JOIN SEMI | | 1 | 231 | 130 (0)| 00:00:02 | |* 3 | TABLE ACCESS FULL | GS_TABLE | 5 | 895 | 123 (0)| 00:00:02 | |* 4 | TABLE ACCESS FULL | UPDATEDPROGRAMCODE | 850 | 44200 | 7 (0)| 00:00:01 | |* 5 | VIEW | | 11 | 2024 | 8 (13)| 00:00:01 | |* 6 | WINDOW SORT PUSHED RANK| | 11 | 440 | 8 (13)| 00:00:01 | |* 7 | FILTER | | | | | | |* 8 | TABLE ACCESS FULL | UPDATEDPROGRAMCODE | 11 | 440 | 7 (0)| 00:00:01 | | 9 | VIEW | | 850 | 1138K| 9 (23)| 00:00:01 | | 10 | SORT ORDER BY | | 850 | 685K| 9 (23)| 00:00:01 | |* 11 | VIEW | | 850 | 685K| 8 (13)| 00:00:01 | | 12 | WINDOW SORT | | 850 | 47600 | 8 (13)| 00:00:01 | |* 13 | FILTER | | | | | | |* 14 | TABLE ACCESS FULL | UPDATEDPROGRAMCODE | 850 | 47600 | 7 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------
Unfortunately the originator of this plan declined to show us the query or answer any questions about where the work was going, but did confirm a speculative comment I had made that the instance was 12c. So the quiz question tonight is this: can you spot what it was that made me think that the plan came from 12c?
I have to say, by the way, that there may be ways to get this plan from 11g, it was just that my first impression was that it was probably 12c and I didn’t attempt to come up with a way of getting a similar plan from 11g. (And, as far as the general shape of the plan is concerned, I can think of two different types of query that could produce it.)
Footnote
You are allowed to prove me wrong.
Answer
(Which might be me showing ignorance rather than inspiration)
The basic shape of the plan suggests to me that the query is of the form:
update gs_table set col1 = (select from updatedprogramcode), col2 = (select from updatedprogramcode) where exists (select from updatedprogramcode) -- possibly "where IN (subquery)" ;
There are a couple of variations in how the “set” subquery content might vary, and I’ll write up a short blog about that later.
Having noted this basic shape, I then noted that the subqueries involved analytic functions – as indicated by the WINDOW SORT operations; moreover one of them used a PUSHED RANK option and the other was embedded in a non-mergeable VIEW (operation 11). Updates with subqueries generally involve correlated columns – and prior to 12c there are some restrictions on how far up the tree the correlation can go. Here’s a sample query (using two tables that I’ve cloned from all_objects) to demonstrate:
update t1 set data_object_id = ( select objno from ( select object_id objno, row_number() over (order by object_id desc) rn from t2 where t2.object_type = t1.object_type ) where rn = 1 ) /
We need to embed the inner select statement in an inline view because we want to use the result of the row_number() analytic function in a filter predicate, but in Oracle 11g the reference to t1.object_id can’t correlate back to the outer t1 table, while in 12c this isn’t a problem. Here’s the 12c plan, followed by the 11g error:
12c Plan (autotrace) ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 5000 | 45000 | 70012 (15)| 00:04:34 | | 1 | UPDATE | T1 | | | | | | 2 | TABLE ACCESS FULL | T1 | 5000 | 45000 | 12 (0)| 00:00:01 | |* 3 | VIEW | | 1 | 26 | 13 (8)| 00:00:01 | |* 4 | WINDOW SORT PUSHED RANK| | 556 | 6116 | 13 (8)| 00:00:01 | |* 5 | TABLE ACCESS FULL | T2 | 556 | 6116 | 12 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("RN"=1) 4 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=1) 5 - filter("T2"."OBJECT_TYPE"=:B1) 11g Error --------- t2.object_type = t1.object_type * ERROR at line 11: ORA-00904: "T1"."OBJECT_TYPE": invalid identifier
Notice, by the way that my predicate “rn = 1” has resulted in the WINDOW SORT PUSHED RANK that appeared in the original plan.
In case I haven’t said it enough times: this is a just a rapid inference I drew from looking briefly at the plan and I haven’t tried hard to work out whether there is a way to get a plan like this in 11g. It was nice being proved right by the follow-up post from the OP, but my guess may have been right by accident – I’d rather be proved wrong than carry on thinking I’d got it right when I hadn’t … so feel free to supply an alternative in the comments.
I don’t recall ever having seen an UPDATE EXPLAIN PLAN have an “update” step listed twice…
Comment by Jason B. — June 8, 2016 @ 4:38 am BST Jun 8,2016 |
Jason,
It’s a measure of how much we don’t notice when we’re not specifically looking, but when I read your response I couldn’t decide whether I would expect to see two UPDATE lines or not on earlier versions of Oracle, but here’s a quick test on 11.2.0.4:
Comment by Jonathan Lewis — June 8, 2016 @ 7:39 am BST Jun 8,2016 |
my guess is , the full scan on step 8 is on 11 rows ….instead on 850 rows on the other full scnas
Comment by Gadi — June 8, 2016 @ 8:25 am BST Jun 8,2016 |
Gadi,
Bear in mind that that’s the number of rows that the optimizer expects to produce each time it executes that operation, not the number of rows it expects to scan. The filter predicate (which we weren’t given) for operation 8 is probably different from the filter predicate used at operations 4 and 14.
Comment by Jonathan Lewis — June 8, 2016 @ 8:43 am BST Jun 8,2016 |
Jonathan,
reading through the OTN thread I think it was the point that the OP indirectly suggested that your guess about the “existence” subquery was wrong, but there is a semi join in the main query, and the “Partial Join” transformation that could turn an inner join into a semi join only appeared in 12c as far as I remember.
Randolf
Comment by Randolf Geist — June 8, 2016 @ 12:45 pm BST Jun 8,2016 |
Randolf,
That’s a subtle one – I hadn’t thought of that.
I’d actually decided that it was a 12c plan before that comment came up, and I think I interpreted the comment as meaning the original had been written as an IN subquery that the OP was going to change to an EXISTS subquery.
Example of 11g getting to the starting semi-join:
With the data in the tables I had to put in a hint to stop the side swap and RIGHT semi-join.
Comment by Jonathan Lewis — June 8, 2016 @ 1:13 pm BST Jun 8,2016 |