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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Query Optimization

Re: Query Optimization

From: rgb <roy.ball_at_gmail.com>
Date: 27 Feb 2007 06:42:30 -0800
Message-ID: <1172587350.792682.203050@h3g2000cwc.googlegroups.com>


Excellent! I was indeed looking for distinct values. The tip for alias' helps as well. I think I've learned more here in this amount of time than in triple time spent with any of my books.

On Feb 27, 6:18 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Feb 27, 12:07 am, "Roy" <roy.b..._at_gmail.com> wrote:
>
> > Suggestions are much appreciated. The last two of which both worked
> > quite well - especially after convincing the client DBA to move on the
> > indexes.
>
> > Having no understanding of my market, offering NO solution and feeling
> > compelled to dictate what is and is not good policy - a complete waste
> > of time.
>
> It is important to keep in mind that the two solutions provided may or
> may not yield the same results. This will be evident in those cases
> where there is more than one row in REMINDER for a PROJ_ID WHERE
> REM_TYPE = 'LAST', and in cases where there is more than one row in
> NOTES for a PROJ_ID WHERE NOTE_TYPE = 'QA'.
>
> To work around the above issue, if it may occur, modify AlterEgo's
> solution:
>
> select count(a.projectid)
>
> Can be modified as (assuming that PROJECTID is the primary key of the
> PROJECT table):
> SELECT
> COUNT(DISTINCT A.PROJECTID)
>
> With the above change, you may find that AlterEgo's SQL statement
> executes slightly faster than the solution that I provided (this may
> be Oracle version dependent).
>
> One final note. When building SQL statements with aliases, it is
> easier to troubleshoot problems with the SQL statements if the alias
> name is somehow related to the object name that it represents:
> Rather than using:
> PROJECT A,
> REMINDER B,
> NOTES C
> I would use:
> PROJECT P,
> REMINDER R,
> NOTES N
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Tue Feb 27 2007 - 08:42:30 CST

Original text of this message

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