| 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
No mystery at all. If you use
mandt = 100
Oracle must do a type conversion and it effectively translates your predicate to
to_number(mandt) = 100
Having a function on the field precludes the use of the index. When you correctly specify your predicate using the predicate datatype matching the column datatype there is no type conversion and Oracle can use the index.
At 12:27 PM 3/1/2004, you wrote:
>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_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Mar 01 2004 - 13:40:21 CST
 |
 |