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: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 27 Feb 2007 03:18:52 -0800
Message-ID: <1172575132.062288.179360@p10g2000cwp.googlegroups.com>


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

Original text of this message

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