RE: strange CBO decision

  • From: DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 1 Mar 2004 13:35:24 -0600

Rich
   You don't mention the Oracle version. My recollection is that versions
before 9i had no visibility to the bind variables. I think 9i has some,
which some have termed "bind peek".

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Rich Holland
Sent: Monday, March 01, 2004 1:27 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: strange CBO decision


I'm having a hard time figuring out why the CBO would choose this access
path.
I have an SAP table which contains HR data.  The table contains the
following
fields in the first 2 columsn:

        MANDT           NOT NULL        VARCHAR2(3)
        PERNR           NOT NULl        VARCHAR2(8)

The table's primary key contains both fields (plus a few more).

When I run an update using:

        update pa0002 set field = 'value' where mandt = 100 and pernr =
00000001;

I would expect the system to do an index range scan over the primary key to
identify the rows which need updating, but it does a FTS instead.  If I
specify
the value for MANDT in a bind variable, or even specify it as '100' instead
of
100 (i.e. quote it), the optimizer uses the index.

Anyone have any idea why specifying a specific numeric value vs. specifying
the
value via a bind variable or quoting it would trigger the FTS?  I'm doing
the
update via Perl & DBI::Oracle 1.6, but that shouldn't matter...

Thanks!
Rich
--
Rich Holland        (913) 645-1950        SAP Technical Consultant
print unpack("u","92G5S\=\"!A;F]T:&5R(\'!E<FP\@:&%C:V5R\"\@\`\`");


----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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: