对于自适应游标共享的一点补充(三)
ind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y Y
已选择6行。
SQL> @show_sel
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 2679189014 082txyqgv2bhq 5 =VR 0 0.000003 0.068751 --由于15不存在，所以选择性向下扩充
00000000DD40C0E0 2679189014 082txyqgv2bhq 4 =VR 0 0.000007 0.068751
00000000DD40C0E0 2679189014 082txyqgv2bhq 3 =VR 0 0.000007 0.001074
00000000DD40C0E0 2679189014 082txyqgv2bhq 2 =VR 0 0.000007 0.000034
00000000DD40C0E0 2679189014 082txyqgv2bhq 1 =VR 0 0.000007 0.000017
00000000DD40C0E0 2679189014 082txyqgv2bhq 0 =VR 0 0.000007 0.000008
已选择6行。
SQL> exec :vr := 17
PL/SQL 过程已成功完成。
SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;
SUM(ID)
----------
8455659520
SQL> @show_sql
SQL_TEXT EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y Y
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y Y
已选择7行。
SQL>
@show_sel
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 2679189014 082txyqgv2bhq 6 =VR 0 0.787503 0.962503 --新的子游标的执行计划于旧子游标不同，所以均保留
00000000DD40C0E0 2679189014 082txyqgv2bhq 5 =VR 0 0.000003 0.068751
00000000DD40C0E0 2679189014 082txyqgv2bhq 4 =VR 0 0.000007 0.068751
00000000DD40C0E0 2679189014 082txyqgv2bhq 3 =VR 0 0.000007 0.001074
00000000DD40C0E0 2679189014 082txyqgv2bhq 2 =VR 0 0.000007 0.000034
00000000DD40C0E0 2679189014 082txyqgv2bhq 1 =VR 0 0.000007 0.000017
00000000DD40C0E0 2679189014 082txyqgv2bhq 0 =VR 0 0.000007 0.000008
已选择7行。
由此可见，计算绑定变量的谓词选择性在bind_aware中扮演者重要角色