> -----Original Message-----
> From: Jesse, Rich [mailto:[EMAIL PROTECTED]]
>
> 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?
Here are my results. No rows returned from the third query!!!
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
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.
SQL>
SQL> insert into rej_test
2 values (23, 'ESR', 'upd1', -1, 60, 1);
1 row created.
SQL> insert into rej_test
2 values (60, 'ESR', 'upd1', -1, 13, 2);
1 row created.
SQL>
SQL> analyze table rej_test compute statistics;
Table analyzed.
SQL>
SQL> REM SELECT 1
SQL>
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
SQL>
SQL> ALTER SESSION SET CURSOR_SHARING=EXACT;
Session altered.
SQL>
SQL> REM SELECT 2
SQL>
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
SQL>
SQL> ALTER SESSION SET CURSOR_SHARING=FORCE;
Session altered.
SQL>
SQL> REM SELECT 3
SQL>
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);
no rows selected
