Re: 10053 curiosity (changes with and without bind)
- From: Jared Still <jkstill@xxxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 24 May 2004 22:28:23 -0700
I would suspect that there are histograms on
the columns. The bind variable precludes the
use histograms by the CBO, whereas the query
with the hardcoded value is allowing the CBO
to use them when choosing an execution path.
Jared
On Mon, 2004-05-24 at 20:16, Henry Poras wrote:
> 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.
...
----------------------------------------------------------------
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:
