RE: strange CBO decision
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 1 Mar 2004 14:41:46 -0500
Rich,
Not so strange really. The optimizer is converting the column values to
numbers and
then comparing them to your literal. This, of course, prevents the use of
indexes.
Remember, Oracle will not convert a literal to match a column - it converts
the column to match the literal. Makes perfect sense.
Hope this helps
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Rich Holland [mailto:holland@xxxxxxxxxxxxxxxx]
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:
