| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 10053 curiosity (changes with and without bind)
I'm going slightly batty(er) trying to understand why Oracle (8.1.7.4) is using two different execution plans for two nearly identical queries. The only difference between the two is that one uses a bind variable where the other uses a hard coded value. The field in question, however, is NOT NULL, with only one distinct value (# of distinct values in dba_tab_column shows 1). Trying to find the source of this difference, I looked at a 10053 trace and found the following:
WITH BIND
SINGLE TABLE ACCESS PATH
Column: SETID Col#: 1
NDV: 1 NULLS: 0 DENS: 6.6667e-03
Column: EFFDT Col#: 5
NDV: 22 NULLS: 0 DENS: 2.4626e-05
Table: PS_ITM_CAT_TBL Orig CDB: 20304 Cmptd CDN: 10 <--
Access Path : index (iff)
INDEX#: 23177 TABLE: PS_ITM_CAT_TBL
CST: 20 IXSEL: 0.0000e+00 TBSEL: 1.0000e+00
Access Path : index (index-only)
INDEX#: 23177 TABLE: PS_ITM_CAT_TBL
CST: 4.00 IXSEL: 1.0000e-02 TBSEL: 1.0000e-02 <--
WITH EXPLICIT VALUE
SINGLE TABLE ACCESS PATH
Column: SETID Col#: 1
NDV: 1 NULLS: 0 DENS: 6.6667e-03
Column: EFFDT Col#: 5
NDV: 22 NULLS: 0 DENS: 2.4626e-05
Table: PS_ITM_CAT_TBL Orig CDB: 20304 Cmptd CDN: 923 <--
Access Path : index (iff)
INDEX#: 23177 TABLE: PS_ITM_CAT_TBL
CST: 20 IXSEL: 0.0000e+00 TBSEL: 1.0000e+00
Access Path : index (index-only)
INDEX#: 23177 TABLE: PS_ITM_CAT_TBL
CST: 206 IXSEL: 1.0000e+00 TBSEL: 1.0000e+00 <--
All of the other table statistics and access path data were identical.
I'm writing this from home using my notes, so if anyone would like some more detail, I can cut and paste from the 10053 and 10046 tomorrow. The explain plan does reflect a change from 'index only' to 'iff'. I actually have two questions with this query:
a.. Why the change in cost, and index/table selectivity (and computed cardinality) with a shift from bound to explicit variable even though NDV=1
b.. analyzing the table gives me a density, num_buckets, num_distinct =1 for setid. If I analyze for column setid, num_buckets and num_distinct remain 1, but density becomes .0066667 (as seen in the 10053 trace).
An example of the query is
select setid, setcntrlvalue, tree_name, effdt, to_char(effdt, 'yyyy-mm-dd')
from ps_cat_root_vw
where setid:=v
select setid, setcntrlvalue, tree_name, effdt, to_char(effdt, 'yyyy-mm-dd')
from ps_cat_root_vw
where setid = 'DFCII'
I'm not sure what is going on. Any ideas would be appreciated.
Henry
 |
 |