Oracle Scratchpad

November 3, 2013

Not Pushing

Filed under: Bugs,Execution plans,Oracle,subqueries — Jonathan Lewis @ 5:24 pm BST Nov 3,2013

Here’s an odd little optimizer glitch – probably irrelevant to most people, but an indication of the apparent randomness that appears as you combine features. I’ve created an example which is so tiny that the only explanation I can come up with the for optimizer not “behaving properly” is that I’ve found an undocumented restriction relating to a particular feature.

Here’s the basic schema structure with query and execution plan – there’s nothing particularly significant about the object definitions – they’re just a couple of (reduced) structures from a client site I visited a few years ago:

drop view v_item;
drop table part;
drop table item;

create table item (
	item_id number not null,
	company number not null,
	part_id number not null,
	quantity number
);

alter table item add constraint item_pk primary key (company,part_id);

create table part(
	part_id number not null,
	company number not null,
	part_name varchar2(30) not null
);

create unique index part_idx on part(company,part_name);

alter table part
	add constraint part_fk foreign key (company, part_id)
	references item (company, part_id)
;

create or replace view v_item
as
select * from item
;

begin
	dbms_stats.gather_table_stats(user, 'part', method_opt=>'for all columns size 1');
	dbms_stats.gather_table_stats(user, 'item', method_opt=>'for all columns size 1');

end;
/

explain plan for
update v_item i
   set quantity = 0
 where i.company = to_number(:b1)
   and i.part_id = (select /*+ push_subq */
                           part_id
                      from part p
                     where part_name=:b2
                       and p.company=i.company
                   )
;

select * from table(dbms_xplan.display);

-- Initial Plan
------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |          |     1 |    39 |     0   (0)| 00:00:01 |
|   1 |  UPDATE                       | ITEM     |       |       |            |          |
|*  2 |   INDEX RANGE SCAN            | ITEM_PK  |     1 |    39 |     0   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PART     |     1 |    43 |     0   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | PART_IDX |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("COMPANY"=TO_NUMBER(:B1))
       filter("PART_ID"= (SELECT "PART_ID" FROM "PART" "P" WHERE "PART_NAME"=:B2
              AND "P"."COMPANY"=:B1))
   4 - access("P"."COMPANY"=:B1 AND "PART_NAME"=:B2)

You can see from this plan that Oracle has use simple view merging to eliminate the view v_item and has then “pushed” the subquery to a point where it can be applied at the earliest possible moment – which is as Oracle reads an index entry from item_pk and before it visits the table. (This plan came from 11.1, and if you printed the outline information you would see that it includes a push_subq() hint.)

Now let’s make a little change to the object definition – and see what happens to the execution plan (two versions – the second one includes a hint):


create or replace trigger v_trig
instead of insert or update or delete on v_item
begin
	null;
end;
/

-- New plan without hinting:
-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT               |          |     1 |    39 |     0   (0)| 00:00:01 |
|   1 |  UPDATE                        | V_ITEM   |       |       |            |          |
|*  2 |   FILTER                       |          |       |       |            |          |
|   3 |    VIEW                        | V_ITEM   |     1 |    39 |     0   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ITEM     |     1 |    39 |     0   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | ITEM_PK  |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID | PART     |     1 |    43 |     0   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN          | PART_IDX |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("I"."PART_ID"= (SELECT "PART_ID" FROM "PART" "P" WHERE
              "PART_NAME"=:B2 AND "P"."COMPANY"=:B1))
   5 - access("COMPANY"=TO_NUMBER(:B1))
   7 - access("P"."COMPANY"=:B1 AND "PART_NAME"=:B2)

-- New plan with a push_subq hint in the subquery:
------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |          |     1 |    39 |     0   (0)| 00:00:01 |
|   1 |  UPDATE                       | V_ITEM   |       |       |            |          |
|*  2 |   VIEW                        | V_ITEM   |     1 |    39 |     0   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM     |     1 |    39 |     0   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | ITEM_PK  |     1 |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| PART     |     1 |    43 |     0   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | PART_IDX |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("I"."PART_ID"= (SELECT /*+ PUSH_SUBQ */ "PART_ID" FROM "PART" "P"
              WHERE "PART_NAME"=:B2 AND "P"."COMPANY"=:B1))
   4 - access("COMPANY"=TO_NUMBER(:B1))
   6 - access("P"."COMPANY"=:B1 AND "PART_NAME"=:B2)

With the existence of the trivial “instead of” trigger on the view, view merging no longer takes place; moreover, with view merging blocked, subquery pushing doesn’t happen unless hinted – the subquery acts as a filter¬†after Oracle starts generating the view rowsource.

Even when we use the push_subq hint in the subquery – which clearly has some effect as the filter is now applied “inside” the view – the subquery isn’t applied on the index range scan it is only applied after we visit the table.

Conclusion

I haven’t done any sort of exhaustive analysis of this example (a large data set, and checks on rowsource execution statistics would be the next stage), but it does look as if the presence of an “instead of” trigger blocks some query transformations that look (at first sight, at least) as if they should be legal.

If you’re using updateable views that depends on triggers and don’t see the optimizer taking the path you expect, you might want to try testing the query on a copy schema with the trigger removed before you spend too much time trying to work out how to change the execution path. The case that particularly springs to mind (though I’ve rarely seen the feature in use) is “Object Views” where a view may be used with triggers to translate between the relational database and an¬†object-oriented programming environment.

 

 

4 Comments »

  1. Jonathan, I wonder if this might be related to bug 7362007?

    Many thanks for your blog.

    Comment by Brandon Roberts — January 22, 2014 @ 9:49 pm BST Jan 22,2014 | Reply

    • Brandon,

      Thanks for the pointer – it’s hard to say, it may be a match but if so, Oracle Corp has had quite a long time to fix it and the problem is still there in 12.1.0.1 (which I’ve just tested).

      Comment by Jonathan Lewis — January 22, 2014 @ 10:09 pm BST Jan 22,2014 | Reply

  2. Jonathan,
    Yes, as you mentioned this problem is persisting from a long back, i have tested and found the ditto results in 10.2.0.1.0 – 64 and 11.2.0.1.0 too.
    and many thanks and appreciate you for sharing such a useful information and your contribution towards showing the workings/functionality of Oracle. I am learning a lot by reading your blog
    Thanks

    Comment by jagdeepsangwan — January 23, 2014 @ 6:49 am BST Jan 23,2014 | Reply

    • Thanks for the comment, and thanks for the feedback on different versions. It helps many people when someone reproduces the same results on other versions of Oracle and then reports back with the results. (And, of course, it’s even nicer when they can report back that “it’s fixed in version xx.x.x.x).

      Comment by Jonathan Lewis — January 23, 2014 @ 6:54 am BST Jan 23,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,529 other followers