Changes access method for non-correlated subquery

8 votes
3 answers
150 views
                          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.
                        
Asked by rfusca (1569 rep)
Mar 11, 2014, 07:28 PM
Last activity: Aug 1, 2025, 06:13 PM
Changes access method for non-correlated subquery

Related Questions
