Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Query Optimization
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 - 05:18:52 CST