strange CBO decision

From: Rich Holland <holland_at_guidancetech.com>
Date: Mon, 1 Mar 2004 14:27:20 -0500
Message-Id: <20040301192738.57AF18A@jester.pobox.com>

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

---

Received on Mon Mar 01 2004 - 13:26:08 CST