It also causes ORA 600's, it was supposed to be fixed in Patch 8..1.7.1.. I
believe.
KK
-----Original Message-----
Sent: Tuesday, August 21, 2001 1:57 PM
To: Multiple recipients of list ORACLE-L
Although it works for me under Oracle 8.1.7.1.4 on Win/2K as shown below, I
do have doubts about CURSOR_SHARING=FORCE. When I had experimented couple of
months ago, Oracle didn't substitute bind variables for all literals values,
and that wasn't going to help us. So we couldn't use this feature.
Jay
ORADB SQL>CREATE TABLE REJ_TEST (
2 ACCESSCONTROLID NUMBER (10) NOT NULL,
3 RESOURCE_NAME VARCHAR2 (10) NOT NULL,
4 ACTION VARCHAR2 (10) NOT NULL,
5 ISALLOWED NUMBER (5) NOT NULL,
6 PARTYID NUMBER (10) NOT NULL,
7 PTYPE NUMBER (3) NOT NULL);
Table created.
ORADB SQL>
ORADB SQL>insert into rej_test
2 values (23, 'ESR', 'upd1', -1, 60, 1);
1 row created.
ORADB SQL>insert into rej_test
2 values (60, 'ESR', 'upd1', -1, 13, 2);
1 row created.
ORADB SQL>
ORADB SQL>analyze table rej_test compute statistics;
Table analyzed.
ORADB SQL>
ORADB SQL>REM SELECT 1
ORADB SQL>
ORADB SQL>SELECT Resource_Name, Action
2 FROM rej_test
3 WHERE
4 (PartyID=60 AND IsAllowed=-1 AND PType=1) OR (PartyID=13 AND
IsAllowed=-1
5 AND PType=2);
RESOURCE_N ACTION
---------- ----------
ESR upd1
ESR upd1
2 rows selected.
ORADB SQL>
ORADB SQL>ALTER SESSION SET CURSOR_SHARING=EXACT;
Session altered.
ORADB SQL>
ORADB SQL>REM SELECT 2
ORADB SQL>
ORADB SQL>SELECT Resource_Name, Action
2 FROM rej_test
3 WHERE
4 (PartyID=60 AND IsAllowed=-1 AND PType=1) OR
5 (PartyID=13 AND IsAllowed=-1 AND PType=2);
RESOURCE_N ACTION
---------- ----------
ESR upd1
ESR upd1
2 rows selected.
ORADB SQL>
ORADB SQL>ALTER SESSION SET CURSOR_SHARING=FORCE;
Session altered.
ORADB SQL>
ORADB SQL>REM SELECT 3
ORADB SQL>
ORADB SQL>SELECT Resource_Name, Action
2 FROM rej_test
3 WHERE
4 (PartyID=60 AND IsAllowed=-1 AND PType=1)
5 OR
6 (PartyID=13 AND IsAllowed=-1 AND PType=2);
RESOURCE_N ACTION
---------- ----------
ESR upd1
ESR upd1
2 rows selected.
ORADB SQL>
-----Original Message-----
Sent: Tuesday, August 21, 2001 1:12 PM
To: Multiple recipients of list ORACLE-L
Eeeek! It just keeps getting worse! Can someone on 8.1.7.x on a NON-HP
platform using CURSOR_SHARING=FORCE and CBO at the system level try the
following test for me?
----------------------
DROP TABLE REJ_TEST CASCADE CONSTRAINTS ;
CREATE TABLE REJ_TEST (
ACCESSCONTROLID NUMBER (10) NOT NULL,
RESOURCE_NAME VARCHAR2 (10) NOT NULL,
ACTION VARCHAR2 (10) NOT NULL,
ISALLOWED NUMBER (5) NOT NULL,
PARTYID NUMBER (10) NOT NULL,
PTYPE NUMBER (3) NOT NULL);
insert into rej_test
values (23, 'ESR', 'upd1', -1, 60, 1);
insert into rej_test
values (60, 'ESR', 'upd1', -1, 13, 2);
analyze table rej_test compute statistics;
REM SELECT 1
SELECT Resource_Name, Action
FROM rej_test
WHERE
(PartyID=60 AND IsAllowed=-1 AND PType=1) OR (PartyID=13 AND IsAllowed=-1
AND PType=2);
ALTER SESSION SET CURSOR_SHARING=EXACT;
REM SELECT 2
SELECT Resource_Name, Action
FROM rej_test
WHERE
(PartyID=60 AND IsAllowed=-1 AND PType=1) OR
(PartyID=13 AND IsAllowed=-1 AND PType=2);
ALTER SESSION SET CURSOR_SHARING=FORCE;
REM SELECT 3
SELECT Resource_Name, Action
FROM rej_test
WHERE
(PartyID=60 AND IsAllowed=-1 AND PType=1)
OR
(PartyID=13 AND IsAllowed=-1 AND PType=2);
------------------------------
I've purposely formatted each SELECT a little differently in order to force
a hard parse. The problem I'm seeing is that SELECT #1 and #3 *RETURN NO
ROWS*! If #3's formatted to be just like #2, it works fine, which leads me
to believe the problem's in the parse.
TIA!
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
****************************************************************************
This electronic message contains information from CTIS, Inc., which
may be company sensitive, proprietary, privileged or otherwise protected
from disclosure. The information is intended to be used solely by the
recipients named above. If you are not an intended recipient, be aware
that any review, disclosure, copying, distribution or use of this
transmission or its contents is prohibited. If you have received this
transmission in error, please notify us immediately at [EMAIL PROTECTED]
****************************************************************************
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Mehta
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Kostyszyn
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
