Re: Query performance question
- From: "Grant Allen" <gxallen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 3 Mar 2006 10:16:58 +1100
On 3/3/06, Mike Schmitt <mschmitt@xxxxxxxxxxxx> wrote:
>
> There have been a number of good ideas on how to rewrite the query. I plan
> to test them out later to help me learn. My query writing ability is
> probably a 0 out of 10.
>
> The only problem with rewriting the query is that this is suppose to
> represent a row based security implementation. For example,
>
> User fred logs into the database and runs the query : select count(*) from
> fred.table_A
>
> User fred's query will automatically be appended with the where clause
> without him knowing about it.
If you have access to whatever's doing the appending, change it to use
a union instead of OR. I've seen numerous cases with 9i and 10g where
previously fine (in 8i) statements with OR predicates tanked badly
until changed.
E.g.
select count(*) from (
select * from fred.table_a A
where A.col_1 in (select col_3 from fred.table_b B where B.col_4 = '662')
union
select * from fred.table_a A
where A.col_2 in (select col_3 from fred.table_b B where B.col_4 = '662'))
This'll should give you the benefit you've seen from the two
independent queries running quickly, with a little overhead from the
implied sort from the union. This also lends itself nicely to dynamic
query construction ... just keep adding further unions, though don't
go mad with it.
Ciao
Fuzzy
:-)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
