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 ?

Answer 1

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
;