Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query performance question

Re: Query performance question

From: Grant Allen <gxallen_at_gmail.com>
Date: Fri, 3 Mar 2006 10:16:58 +1100
Message-ID: <e2451e6a0603021516m11f658ffp5daccefa71019970@mail.gmail.com>


On 3/3/06, Mike Schmitt <mschmitt_at_uchicago.edu> 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
:-)

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 02 2006 - 17:16:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US