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.
Best Answer
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.