Re: Change this query

Oh dear

we really have got of on the wrong foot ! Sorry if you think I was lecturing.

I was just indicating it was 8.1.7.2 in case someone was going to suggest a 9.x feature, and I am using RBO because the 3rd party application we use was coded that way and only supported that way. This bit of SQL is just a small part of a larger query forming a report.

When I said I had licence to use hints, I meant I had authorisation or permission to use them, not that I was licensed by Oracle.

Lastly, with repsect, tahiti.oracle.com is all well and good for documentation about the various options and syntax available etc, but when it comes to writing SQL, one still has to be rely on experience to understand how queries writen slightly differently can be more efficient than another one written in a different way, and it was for that experience that I posted the question.

Best regards

Neil
On Sun, 30 Nov 2003 10:33:12 -0800, Daniel Morgan <damorgan_at_x.washington.edu> wrote:

>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.
Received on Sun Nov 30 2003 - 15:37:44 CST