Execution plan not as expected

Question

I've encountered something weird which I can't explain .

I'm using the following query:

MERGE INTO Main_Table t
USING  Stg_Table s 
 ON(s.site_id = t.site_id)
 WHEN MATCHED THEN
   UPDATE SET t.arpu_prev_period = s.arpu_prev_period 
              .... --50 more columns  
  where  t.period_code = 201612

Stg_Table : Indexed (Site_Id)

Main_Table:
- Indexed (Period_code,Site_id)
- Partitioned by period_code
- Note - I tried adding an index on Site_Id alone , same execution plan .

I would expect an execution plan that uses single partition scan, but instead I'm getting Partition list all .

This is the execution plan:

6   |   0 | MERGE STATEMENT       |                               |
7   |   1 |  MERGE                | Main_Table                    |
8   |   2 |   VIEW                |                               |
9   |   3 |    HASH JOIN          |                               |
10  |   4 |     TABLE ACCESS FULL | Stg_Table                     |
11  |   5 |     PARTITION LIST ALL|                               |
12  |   6 |      TABLE ACCESS FULL| Main_Table                    |

EDIT: For clarification , if it wasn't clear, I'm not looking for an answer on how to make Oracle to scan only a single partition, I already know that placing the t.period_code = 201612 in the ON clause will be fine. My question is - Why doesn't oracle evaluates the WHERE clause which should filter only the specific partition ?

Do you get the same plan if you move `t.period_code = 201612` to `ON` condition instead of `WHERE`? — Kacper, Dec 07 '16 at 13:14

score 2 · Accepted Answer · answered Dec 07 '16 at 13:53

2

It seems like there is no optimization on the UPDATE's WHERE clause, at all.

create table t (n,x) as select level n,-1 x from dual connect by level <= 1000000;
create table s (n,x) as select level n,-1 x from dual connect by level <= 1000000;

merge into t
using s
on (s.n = t.n)
when matched then update set t.x = s.x where 1=2
;

answered Dec 07 '16 at 13:53

David דודו Markovitz

42,900
6
64
88

Thanks for the answer. I'll be happy to approve this if you'll find a document about this behavior . – sagi Dec 07 '16 at 14:13
Nothing I'm familiar with. It is unlikely to find Oracle's documentation on optimization they **don't** do, but I think the demo shows It well. In comparison, look at the execution plan of `select * from t where 1=2` – David דודו Markovitz Dec 07 '16 at 14:16

Kacper · Answer 2 · 2016-12-07T13:35:12.337

Please consider moving t.period_code = 201612 to on condition: on (t.period_code = 201612 and s.site_id = t.site_id). I think your query is trying tyo access all partitions PARTITION LIST ALL and that's the problem.

If you add condition that access single partition to on it should go better.

Another option is:

MERGE INTO (select * from Main_Table where period_code = 201612) t
USING  Stg_Table s 
 ON(t.period_code = 201612 and s.site_id = t.site_id)
 WHEN MATCHED THEN
   UPDATE SET t.arpu_prev_period = s.arpu_prev_period 
              .... --50 more columns  
  where  t.period_code = 201612

To directly point that update is only for one partition.

EDIT

Ok, so trying to answer question why. Oracle can't combine two conditions into one. where contains information about partition but to apply where on joined data it needs to apply ON condition first. But at that point of time it has no information about partition just site_id so it decides to do scan over all partitions. You need to inform Oracle at first step (which is joining on) what partition you want to use.

In other words to apply where which contains information about partition first it needs to resolve:

MERGE INTO Main_Table t
USING  Stg_Table s 
 ON(s.site_id = t.site_id)

and here you have to access all partitions. where is part of when matchedso first we need to determine if there is any match or not without knowledge about partitions.

Thanks for the answer, yes indeed it accessed only a single partition. See edit of my question , I think I misled you .. — sagi, Dec 07 '16 at 13:28
@sagi OK I've added some explanations why I think it is going like that. — Kacper, Dec 07 '16 at 13:35
The explanation is simply wrong. This is not the way an optimizer works. — David דודו Markovitz, Dec 07 '16 at 14:53

Marmite Bomber · Answer 3 · 2016-12-07T14:56:30.370

A distinction should be made between a WHERE clause of merge insert / update clause and a WHERE clauseof SELECT statement.

The MERGE clauses are in general saing - do not UPDATE or do not INSERT. A generation of ACCESS predicate in general case is not trivial.

As other pointed out in the simplest case with only one UPDATE WHERE clause, there is no ACCESS predicate generation performed as of 11g.

It is also documentes as such

merge_update_clause

Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.

i.e. the predicate skipps the update not a record in row source.

I'm not appointed with Oracle company. Citation from documentation should not be punished on SO! — Marmite Bomber, Dec 07 '16 at 22:16

Execution plan not as expected

3 Answers3