Not Pushing

Here’s an odd little optimizer glitch – probably irrelevant to most people but an indication of the apparent random changes in behaviour that can appear 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. The example is one I created a long time ago, but the problem (probably) surfaced very recently on OTN.

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:

rem
rem     Script:         index_subq_update.sql
rem     Author:         J P Lewis
rem     Dated:          April 2007
rem

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 -- add push_subq hint here for testing
                           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 the point between reading an index entry from item_pk and visiting the table. (This plan came from 11.1, and if you printed the “Outline Information” section of the plan 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. I’ve reported two versions of the plan here, the second one appeared when I included an explicit push_subq hint in the subquery:

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, and that can make a huge difference to the performance.

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 updatable 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.