Mohamed Houri’s Oracle Notes

October 7, 2013

Intelligent CBO

Filed under: explain plan — hourim @ 6:06 pm

Just a small note to show you a situation I’ve recently encountered which shows how the CBO is intelligent. I have the following two pieces of SQL

UPDATE t1 a
SET
a.padding = 'yyyyyyyy'
WHERE
a.id1 in
(SELECT
     b.id2
FROM t2  b
WHERE a.id1 = a.n1   ---> spot this
);

And the second one

UPDATE t1 a
SET
a.padding = 'yyyyyyyy'
WHERE
a.id1 in
(SELECT
 b.id2
 FROM t2  b
)
AND a.id1 = a.n1;       ---> spot this

I would not have written the first SQL in order to restrict the updates only to records in t1 having identical id1 and n1. I would have logically issued the second one instead.

But to my surprise the CBO recognized that the where clause in the subquery (WHERE a.id1 = a.n1 ) should be applied to the main update by replacing it with the AND clause outside the brackets. Here below are the corresponding execution plans

First query

Plan hash value: 1788758844
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |     1 |    91 |   899   (2)| 00:00:03 |
|   1 |  UPDATE             | T1   |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |      |     1 |    91 |   899   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| T1   |     1 |    78 |   447   (2)| 00:00:02 |
|   4 |    TABLE ACCESS FULL| T2   |   104K|  1320K|   449   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID1"="B"."ID2")
3 - filter("A"."ID1"="A"."N1")

Note
-----
- dynamic sampling used for this statement (level=2)

Second query

Plan hash value: 1788758844
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |     1 |    91 |   899   (2)| 00:00:03 |
|   1 |  UPDATE             | T1   |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |      |     1 |    91 |   899   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| T1   |     1 |    78 |   447   (2)| 00:00:02 |
|   4 |    TABLE ACCESS FULL| T2   |   104K|  1320K|   449   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID1"="B"."ID2")
3 - filter("A"."ID1"="A"."N1")

Note
-----
- dynamic sampling used for this statement (level=2)

The same plan hash value and the same predicate part. It’s funny enough.

If you want to play with the test here is the model (borrowed from Jonathan Lewis)

create table t1
as
with generator as (
select  --+ materialize
rownum id
from dual
connect by
level <= 10000)
select
   rownum id1,
   trunc(dbms_random.value(1,1000))    n1,
   lpad(rownum,10,'0') small_vc,
   rpad('x',100)       padding
from
 generator   v1,
 generator   v2
where
rownum <= 100000;
create table t2
 as
with generator as (
select  --+ materialize
rownum id
from dual
connect by
level <= 10000)

select
 rownum                  id2,
 trunc(dbms_random.value(10001,20001))   x1,
 lpad(rownum,10,'0') small_vc,
rpad('x',100)       padding
from
 generator   v1,
 generator   v2
where
rownum <= 100000;

Leave a Comment »

No comments yet.

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

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: