10053 curiosity (changes with and without bind)
- From: "Henry Poras" <hporas@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 24 May 2004 23:16:55 -0400
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
