CODEWITHSUNDEEP
sqloracle-databaseperformancesql-execution-plan
sagi
sagi

Reputation: 40491

Execution plan not as expected

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 ?

Upvotes: 8

Views: 121

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21115

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.

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

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
;

enter image description here

Upvotes: 2

Kacper
Kacper

Reputation: 4818

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.

Upvotes: 0

Related Questions