Mark to your calendars two last days at the end of may 2012. There will be a Ougf seminar in Hämeenlinna. Just another time to feel closer as a beginner. And better yet learn things to unlearn...
Well just a tought. Lending an example from Jonathans teaching. Should the bind name be a part to be considered a part of sql text by optimizer?
drop table t1; drop table t2; create table t1(n1 number, n2 number); create table t2(n1 number, n2 number); create index t1_i1 on t1(n1); create index t2_i1 on t2(n1);First without binds.
select t1.n2,t2.n2
from t1, t2
where t1.n1=1
and t2.n1=1
and t1.n1=t2.n1
;
select * from table(dbms_xplan.display_cursor(format=>'basic +predicate'));
EXPLAINED SQL STATEMENT:
------------------------
select t1.n2,t2.n2 from t1, t2 where t1.n1=1 and t2.n1=1 and
t1.n1=t2.n1
Plan hash value: 1051316565
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 |
|* 4 | INDEX RANGE SCAN | T1_I1 |
|* 5 | INDEX RANGE SCAN | T2_I1 |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 |
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."N1"=1)
5 - access("T1"."N1"="T2"."N1")
filter("T2"."N1"=1)
Nice looking plan.and now with binds.
select t1.n2,t2.n2
from t1, t2
where t1.n1=:b1
and t2.n1=:b1
and t1.n1=t2.n1
;
select * from table(dbms_xplan.display_cursor(format=>'basic +predicate'));
EXPLAINED SQL STATEMENT:
------------------------
select t1.n2,t2.n2 from t1, t2 where t1.n1=:b1 and t2.n1=:b1
and t1.n1=t2.n1
Plan hash value: 810726339
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | MERGE JOIN CARTESIAN | |
| 3 | TABLE ACCESS BY INDEX ROWID | T1 |
|* 4 | INDEX RANGE SCAN | T1_I1 |
| 5 | BUFFER SORT | |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 7 | INDEX RANGE SCAN | T2_I1 |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B1)=TO_NUMBER(:B1))
4 - access("T1"."N1"=TO_NUMBER(:B1))
filter("T1"."N1"=TO_NUMBER(:B1))
7 - access("T2"."N1"=TO_NUMBER(:B1))
filter("T2"."N1"=TO_NUMBER(:B1))
select t1.n2,t2.n2
from t1, t2
where t1.n1=1
and t2.n1=1
and t1.n1=t2.n1
;
Plan changed. Oracle knows :B1 should be :B1
1 - filter(TO_NUMBER(:B1)=TO_NUMBER(:B1))
But why the plan changed? The bind might be null. And NUL is not equalt to NULL. How about sql monitor report.
SQL Monitoring Report SQL Text ------------------------------ select /*+monitor*/t1.n2,t2.n2 from t1, t2 where t1.n1=:b1 and t2.n1=:b1 and t1.n1=t2.n1 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : RAFU (28:21) SQL ID : dm8z90v1mk8tb SQL Execution ID : 16777217 Execution Started : 11/03/2011 13:54:27 First Refresh Time : 11/03/2011 13:54:27 Last Refresh Time : 11/03/2011 13:54:27 Duration : .002418s Module/Action : SQL Developer/- Service : SYS$USERS Program : SQL Developer Fetch Calls : 1 Binds ======================================================================================================================== | Name | Position | Type | Value | ======================================================================================================================== | :B1 | 1 | VARCHAR2(32) | 1 | | :B1 | 2 | VARCHAR2(32) | 1 | ======================================================================================================================== Global Stats ============================== | Elapsed | Other | Fetch | | Time(s) | Waits(s) | Calls | ============================== | 0.00 | 0.00 | 1 | ============================== SQL Plan Monitoring Details (Plan Hash Value=810726339) ======================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ======================================================================================================================================= | 0 | SELECT STATEMENT | | | | | | 1 | | | | | 1 | FILTER | | | | | | 1 | | | | | 2 | MERGE JOIN CARTESIAN | | 1 | 1 | | | 1 | | | | | 3 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | | | 1 | | | | | 4 | INDEX RANGE SCAN | T1_I1 | 1 | 1 | | | | | | | | 5 | BUFFER SORT | | 1 | | | | | | | | | 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | | | | | | | | | 7 | INDEX RANGE SCAN | T2_I1 | 1 | | | | | | | | =======================================================================================================================================B1 is named nicely. But through jdbc seems like the bind variable naming is lost at some point. A named bind becomes named like :B1 and :B2 all thou there was only one name while binding.
No comments:
Post a Comment