Changes access method for non-correlated subquery

Oracle 11g R2

Unfortunately our application has per row security “features“.
We have a query that looks about like this:

Bad, slow:

SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
  SELECT 0 FROM bigTableA_securitymapping b 
  WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
       b.accesscode in (SELECT accesscode 
                              FROM accesscodeView 
                              WHERE user = :someUserID)
)

There a unique index on bigTableA_securitymapping(PrimaryKeyTableA,accesscode).

The accesscodeView could potentially return more than one accesscode for a given user, so it must be IN() and not =.

The issue is that this query ignores the unique index for bigTableA_securitymapping and chooses to do a full table scan.

If I change the IN() to an = then it does a UNIQUE SCAN on the unique index on bigTableA_securitymapping and is about 50 times faster.

Good, fast but not possible:

SELECT someRow, someOtherRow
    FROM bigTableA a
    WHERE EXISTS (
      SELECT 0 FROM bigTableA_securitymapping b 
      WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
           b.accesscode =(SELECT distinct accesscode 
                                  FROM accesscodeView 
                                  WHERE user = :someUserID)
    )

But, I cannot do that because the accesscodeView may return more than one row.

(There’s a distinct in there because the accesscodeView needs it given the =, putting the DISTINCT on the original query makes no difference.)

If I hardcode the accesscodes, it also does a UNIQUE SCAN on the unique index for bigTableA_securitymapping.

Good, fast but requires large application change:

SELECT someRow, someOtherRow
      FROM bigTableA a
      WHERE EXISTS (
        SELECT 0 FROM bigTableA_securitymapping b 
        WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
             b.accesscode in (1,2,3,4)
      )

Changing to a join inside doesn’t really help either. It still does a full table scan.

Bad, slow:

SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
  SELECT 0 FROM accesscode ac INNER JOIN 
                bigTableA_securitymapping b ON
                  ac.accesscode = b.accesscode         
  WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA 
        AND user = :someUserID
)

So why the difference between = and IN() in. And why does a non-correlated subquery (the accesscodeview subquery) cause such a plan difference? Is there any way to rewrite it to do what I want? The difference in ‘good plan’ costs vs ‘bad plan’ costs here are 87 vs 37,000 and a large amount of time in real runtime for the same results.

SELECT someRow, someOtherRow
    FROM bigTableA a
    WHERE EXISTS (
      SELECT 0 FROM bigTableA_securitymapping b 
      WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA 
           AND EXISTS (SELECT 1 FROM accesscodeView 
                          WHERE user = :someUserID
                          AND accesscode = b.accesscode)
    )