Re: Two users, one query, two optimizer plans

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: Wed, 18 Jan 2006 11:45:41 -0500 (EST)
Message-ID: <20060118164541.53554.qmail@web32811.mail.mud.yahoo.com>

How many rows do you get back for users A and B? The CBO expects one row back for both, but I'd guess that A gets by far more rows in the result set than B.

It looks like DM_GRANTPARTICIPANT is policy protected on company_fk column? Since there's an index range scan access path for A user on this table, I'd guess that the RLS predicate is not the only one in the query? I would also guess that this additional RLS predicate (probably treated by the CBO at the selectivity of 5%) simply makes it look to the CBO as a cheaper one to access than DM_ISSUEGRANT and hence the order is switched (BUFFER SORT operation seem to go on a more expensive row source)

But why MERGE JOIN CARTESIAN? Any chance that a join predicate is missing?

Thanks,
Boris Dali.

Paul Baumgartel <paul.baumgartel_at_gmail.com> wrote:

> User A is schema owner. User B has select on user
> A's objects, and is
> subject to row-level security policy on user A's
> objects. (Row-level
> security predicate function returns empty string if
> user issuing SQL is
> owner of object).

Find your next car at http://autos.yahoo.ca

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 18 2006 - 10:46:15 CST