6

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.

  • Does it make any difference if you write the (SELECT accesscode FROM accesscodeView WHERE user = :someUserID) as a CTE? – ypercubeᵀᴹ Mar 12 '14 at 0:53
  • Nope, I tried. Because you still have to SELECT to get the value out and the moment its a SELECT there, it seems to lose the index. – rfusca Mar 12 '14 at 1:03
  • I misread which table scan was taking so long..... I will revise my answer – Threaten Mar 12 '14 at 3:14
0

The database may be ignoring your index if your statistics are stale (including both the system and table statistics) or if your initialization parameters are not optimized. The default optimizer cost adjustment is likely wrong for most applications, for example.

-1

Here is what I would do, although I do not exactly see the whole picture here and maybe I am missing something. An example would help refine my answer if its not useful.

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)
    )

It will always return a single row rather than scanning for a second (or more, if there are more)

  • Hmm i feel like I tried that, but let me test – rfusca Mar 12 '14 at 0:08
  • Nope, same issue. – rfusca Mar 12 '14 at 0:13
  • Hmm I'll see if put together more of an example. – rfusca Mar 12 '14 at 0:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.