Re: Two users, one query, two optimizer plans

From: Boris Dali <boris_dali@xxxxxxxx>
To: paul.baumgartel@xxxxxxxxx
Date: Wed, 18 Jan 2006 11:45:41 -0500 (EST)

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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l

Follow-Ups:
- Re: Two users, one query, two optimizer plans
  - From: Paul Baumgartel

References:
- Two users, one query, two optimizer plans
  - From: Paul Baumgartel

Re: Two users, one query, two optimizer plans

Other related posts: