Re: Change this query

Well Daniel and Frank - i'm a bit disappointed with your belief that this is homework, but never mind, i'll try again.

Firstly Daniel - what is wrong with the having clause - have you tried running such a query. It doesn't need the group by in this instance (go on, set up the test and try it !) which is (I presume) what you are inferring ?

Frank - I no there is an inferred relationship rather than one imposed by an oracle foreign key constraint, not sure why that matters here.

Look, all I was after was some suggestion on other ways to write this query that might enable the use of the index on urole,uid and avoid the sort and filter it is doing currently.

The table urole is quite large (11.5 million) and uname has some 3.5 million.

Changing it to do a select SELECT u.uid FROM uname u WHERE u.uid in (SELECT ......
doesn't help. The best I can do so far is to do

SELECT u.uid FROM uname u WHERE EXISTS ( SELECT r.uid FROM urole r WHERE u.uid = r.uid and .user_role < 3)
AND NOT EXISTS (SELECT r.uid FROM urole r WHERE u.uid = r.uid and .user_role
>2)

This at least enables an index range scan on the index (user_role,uid) but it has to be done twice.
I was looking perhaps for some clever inline view or scalar query.

Lastly, this is RBO i'm afraid (version 8.1.7.2), so no stats on the tables (although I do have licence to use
hints which would of course let it use CBO).

Daniel / Frank - I am an experienced DBA, (but that doesn't mean my SQL skills are that good !) but I am not some kid from University looking for help with homework.

Cheers

Neil

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1070142930.701496_at_yasure...
> NC wrote:
> > Hi,
> >
> > can anyone suggest a better way of writing this query so that it doesn't
use
> > the MAX function
> >
> > SELECT u.uid FROM uname u WHERE EXISTS ( SELECT r.uid FROM urole r WHERE
> > u.uid = r.uid HAVING MAX(r.user_role) < 3 )
>
> Several of them but I am in agreement with Frank that this is homework
> and you need to do it yourself.
>
> A far bigger problem is your total misuse of the HAVING clause. I
> suggest you go back and reread the sections on GROUP BY and HAVING as
> well as search the materials at http://tahiti.oracle.com.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Sun Nov 30 2003 - 03:47:15 CST