RSS Feed

Method R Discussion Group

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

>VARIABLE a VARCHAR2(20)
>VARIABLE b VARCHAR2(20)
>begin :a := '86653175';  :b := '99999999'; end;
  2  /
PL/SQL procedure successfully completed.

>EXPLAIN plan FOR
  2      SELECT workorderid, wonum, rowstamp FROM  WO_TEST WHERE rowstamp BETWEEN :a AND :b;

Explained.

>SELECT * FROM table(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                      |  8088 |  229K|  8787 |
|*  1 |  FILTER                      |                      |      |      |      |
|  2 |  TABLE ACCESS BY INDEX ROWID| WO_TEST              |  8088 |  229K|  8787 |
|*  3 |    INDEX RANGE SCAN          | WO_TEST_ROWSTAMP_UNQ  | 14559 |      |    43 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter(:Z<=:Z)
  3 - access("WO_TEST"."ROWSTAMP">=:Z AND "WO_TEST"."ROWSTAMP"<=:Z)

Note: cpu costing is off

I don't understand the predicate information.
  filter(:Z<=:Z) will always be true - what is happening?

  access("WO_TEST"."ROWSTAMP">=:Z AND "WO_TEST"."ROWSTAMP"<=:Z)
  Why is it using the same variable, I fed in two different variables?

When using explain plan, does the optimizer use bind peeking to look at the values?  If not, then the explain plan would often be inaccurate for bind variables, right?

This looks like bug 3208393 or something related.  It occurs in version 9.2.0.4 but less than v10 and can cause wrong results from a query which uses bind variables in a BETWEEN statement. In some cases the optimizer considers that the binds are equivalent and converts the BETWEEN :bind1 AND :bind2 incorrectly.  The published worksaround is to set event 38007 to level 1 and this should prevent transformation of the BETWEEN.

I tested a similar query I created against a modified emp table in v10 and the predicates are fine:

select * from emp where rowstamp between :a and :b;

yields this plan
---------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost
---------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    15 |  585 |    3
|*  1 |  FILTER            |      |      |      |
|*  2 |  TABLE ACCESS FULL| EMP  |    15 |  585 |    3
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter(:A<=:B)
  2 - filter("ROWSTAMP">=:A AND "ROWSTAMP"<=:B)


I'd suggest checking on this bug to make sure it's what is hitting you and if so, then you'll either need a patch or try the workaround.  Metalink should have more info on both the bug and patchsets, etc.

Thanks Karen, I was confused.
I know that we learned about bind peeking for Histograms, how about SQL like this? Can Oracle Peek at the values to decide the plan in this case?

Yes, bind peeking will occur every time a query is hard parsed.