RE: strange CBO decision
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 1 Mar 2004 14:36:44 -0500
Because implicit conversion of MANDT data type (from number to varchar2)
does not allow to use index?
Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rich Holland
Sent: Monday, March 01, 2004 2: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:
