| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: strange CBO decision
Rich, the column is defined a varchar so when you place it in single quotes
you are comparing varchar2 to varchar2. Without the quotes you are
comparing a number to a character value and forcing Oracle to do an implicit
conversion: to_char or to_number. Specify the data as the right datatype
and eliminate implicit conversions.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Rich Holland
Sent: Monday, March 01, 2004 2:27 PM
To: oracle-l_at_freelists.org
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\"\@\`\`");
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |