Re: Change this query

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 30 Nov 2003 10:33:12 -0800
Message-ID: <1070217222.938739@yasure>

Comments in-line

NC wrote:

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

Further explanation at the bottom of my response.

> 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 ?

Lecturing me on something when you are 100% incorrect is not going to get you very far. If you check out the links under my signature, below, you may surmise that I am probably going to be quite unsympathetic.

And yes I run queries similar to yours all the time. As examples to my students as to why it will run but produce an invalid answer. I find using bad examples is a great tool to education.

>>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).

That it is 8.1.7.2 has no relevance to RBO vs CBO. 8i, 8.0, even as far back as 7.3 Oracle can be configured to use either optimizer. Neither does your license have any relevance ... the optimizer enabled has nothing to do with licensing. Hints are irrelevant also in the sense that you seem to be referring to them. There is nothing stopping you from modifying your init.ora for CHOOSE and running DBMS_STATS. That you don't is a matter of choice.

> 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.

Please don't take this the wrong way but you have sure fooled me and others. Your lack of knowledge of a common Oracle function and seeming lack of understanding of optimizer options would mark you as a beginner in any job interview.

So as much as I'd like to believe you ... you are spending more time arguing the point than it would take if you just went to tahiti.oracle.com and looked up the answers. Which leads me to wonder why the reluctance?

And I know the above is not what you wanted to read. And I know you will likely be angry. But try to remember that all of us here are volunteering our time. We are not Oracle employees. We are not obligated to read your posts much less respond to them. And we are doing the best we can with what we have. If our lack of belief that you are not a student does not please you ... consider that all we know about you is what you have posted ... and it is you that has created that impression.

-- 
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 - 12:33:12 CST