Re: Bind Variable Peeking

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 21 Nov 2006 22:56:26 -0700
Message-Id: <20061122055627.24RX6PW3HN@priv-edtnaa05.telusplanet.net>

I did a quick test (using Oracle 10.2.0.2 on Linux) to answer my own question and, yes, if the bind variables are the same rather than just the bind values then the transformation happens as well:

var a1 number
var b1 number
var b2 number
var b3 number
var b4 number
var b5 number
var b6 number
exec :a1 := 3; :b1 := 10; :b2 := 20; :b3 := 20; :b4 := 20; :b5 := 20; :b6 := 20;

select 01, substr(A.filler,1,8)
FROM N1 A
WHERE A.random = 3

AND ( A.uniform = 10 or A.uniform = 20 or A.uniform = 20 or A.uniform = 20 or A.uniform = 20 or A.uniform = 20) /

select 02, substr(A.filler,1,8)
FROM N1 A
WHERE A.random = :a1

AND ( A.uniform = :b1 or A.uniform = :b2 or A.uniform = :b3 or A.uniform = :b4 or A.uniform = :b5 or A.uniform = :b6) /

select 03, substr(A.filler,1,8)
FROM N1 A
WHERE A.random = :a1

AND ( A.uniform = :b1 or A.uniform = :b2 or A.uniform = :b2 or A.uniform = :b2 or A.uniform = :b2 or A.uniform = :b2) /

SQL_ID 0pmwhgjj4yxwy, child number 0

select 01, substr(A.filler,1,8) FROM N1 A WHERE A.random = 3 AND ( A.uniform = 10 or A.uniform = 20 or A.uniform = 20 or A.uniform = 20 or A.uniform = 20 or A.uniform = 20)

Plan hash value: 1510245936

| 0 | SELECT STATEMENT | | | | 6
(100)| |
| 1 | INLIST

ITERATOR             |        |       |       |            |          |


|   2 |   TABLE ACCESS BY INDEX ROWID| N1     |     2 |    78 

|     6   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | N1_IX2 |     2 


|       |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

3 - access("A"."RANDOM"=3 AND (("A"."UNIFORM"=10 OR "A"."UNIFORM"=20)))

SQL_ID fnabm1ntpn7mm, child number 0

select 02, substr(A.filler,1,8) FROM N1 A WHERE A.random = :a1 AND ( A.uniform = :b1 or A.uniform = :b2 or A.uniform = :b3 or A.uniform = :b4 or A.uniform = :b5 or A.uniform = :b6)

Plan hash value: 1510245936

| 0 | SELECT STATEMENT | | | | 13
(100)| |
| 1 | INLIST

ITERATOR             |        |       |       |            |          |


|   2 |   TABLE ACCESS BY INDEX ROWID| N1     |     7 |   273 

|    13   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | N1_IX2 |     7 


|       |     6   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

    3 - access("A"."RANDOM"=:A1 AND (("A"."UNIFORM"=:B1 OR "A"."UNIFORM"=:B2 OR
               "A"."UNIFORM"=:B3 OR "A"."UNIFORM"=:B4 OR "A"."UNIFORM"=:B5 OR
               "A"."UNIFORM"=:B6)))

SQL_ID da9ggksnwxnxx, child number 0

select 03, substr(A.filler,1,8) FROM N1 A WHERE A.random = :a1 AND ( A.uniform = :b1 or A.uniform = :b2 or A.uniform = :b2 or A.uniform = :b2 or A.uniform = :b2 or A.uniform = :b2)

Plan hash value: 1510245936

| 0 | SELECT STATEMENT | | | | 6
(100)| |
| 1 | INLIST

ITERATOR             |        |       |       |            |          |


|   2 |   TABLE ACCESS BY INDEX ROWID| N1     |     2 |    78 

|     6   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | N1_IX2 |     2 


|       |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

3 - access("A"."RANDOM"=:A1 AND (("A"."UNIFORM"=:B1 OR "A"."UNIFORM"=:B2)))

At 10:33 PM 11/21/2006, Wolfgang Breitling wrote:

>It appears that with literals the optimizer, quite possibly the 
>query transformation engine i.e. before the CBO gets its "hands" on 
>the query and does its peeking, recognizes that all those -1s are 
>identical and rewrites the predicate to "and parent_id in ( 
>21971987,-1)" which then gets transformed to "and ( parent_id = 
>21971987 or parent_id = -1 )" as you can see from the access 
>predicate. Or vice versa, i.e. the in list gets transformed to a 
>string of ORs and all the redundant " or parent_id = -1 " predicates 
>get dropped.
>With bind variables this transformation does not happen, and probably can not.
>It would be interesting if the transformation would happen if the 
>optimizer could recognize that all those bind values are identical 
>by using the same bind variable:
>" and x.parent_id in ( :b1,:b2,:b2,:b2,:b2,:b2,:b2,:b2,:b2,... and so on ) ".
>Of course that is purely academic since it defeats the purpose of 
>bind variables as placeholders for host variables which get 
>initialized to -1 and a variable number of them filled with values 
>from user input.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 21 2006 - 23:56:26 CST