I am learning SQL (trial by fire) and have a question regarding a
query optimization. Lets say I have three tables 'project', 'notes' &
'reminder'. Key on 'project' is 'proj_id'. The other two tables
reference this key as 'notes' contains note entries on a given project
and 'reminder' tracks todo type and due dates.

select count(a.proj_id)
from project a
where a.stat_cd = 'CLOSED'
and exists
(
select b.proj_id
from reminder b
where b.rem_type = 'LAST'
and a.proj_id = b.proj_id
)
and exists
(
select c.proj_id
from notes c
where c.note_type = 'QA'
and a.proj_id = c.proj_id
)

I am trying to determine the number of projects that have a 'CLOSED'
status, contain a reminder type of 'LAST' and has a note type of 'QA'

I get the result I am looking for but these are very large tables and
it takes over 30 minutes for this to run. Is there a better way to
write this query? I'm betting there is. Thank you in advance for your
advice.

Regards...

Also - for whatever the reasons, indexing is not an option (per my
developers)

Roy wrote:
> Also - for whatever the reasons, indexing is not an option (per my
> developers)

Please explain this. If "my developers" ever said this to me my first
reaction would be to ask them to work on their resumes.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

We do not do non-billable, in-house improvements (matter of policy -
yes, even for something as easy as adding an index). I'm a BA on a
customer site who has lost his Oracle guru to the military. I don't
have the immediate budget to grab a contractor so... I'm temporarily
up the proverbial creek, sans paddle.

The goal here is to mine potential data for a new interface I'm
designing. Now - what would a script look like that adds the indexes
(or temp tables?), performs the query, and then drops the temporary
elements?

-rgb

On Feb 26, 4:54 pm, DA Morgan <damor...@psoug.org> wrote:
> Roy wrote:
> > Also - for whatever the reasons, indexing is not an option (per my
> > developers)
>
> Please explain this. If "my developers" ever said this to me my first
> reaction would be to ask them to work on their resumes.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

On Feb 26, 3:53 pm, "Roy" <roy.b...@gmail.com> wrote:
> I am learning SQL (trial by fire) and have a question regarding a
> query optimization. Lets say I have three tables 'project', 'notes' &
> 'reminder'. Key on 'project' is 'proj_id'. The other two tables
> reference this key as 'notes' contains note entries on a given project
> and 'reminder' tracks todo type and due dates.
>
> select count(a.proj_id)
> from project a
> where a.stat_cd = 'CLOSED'
> and exists
> (
> select b.proj_id
> from reminder b
> where b.rem_type = 'LAST'
> and a.proj_id = b.proj_id
> )
> and exists
> (
> select c.proj_id
> from notes c
> where c.note_type = 'QA'
> and a.proj_id = c.proj_id
> )
>
> I am trying to determine the number of projects that have a 'CLOSED'
> status, contain a reminder type of 'LAST' and has a note type of 'QA'
>
> I get the result I am looking for but these are very large tables and
> it takes over 30 minutes for this to run. Is there a better way to
> write this query? I'm betting there is. Thank you in advance for your
> advice.
>
> Regards...

Give this re-write a try to see if it improves performance - I have
essentially moved the exists statements into two inline views (some
versions of Oracle may automatically perform such transformations):
SELECT
COUNT(A.PROJ_ID)
FROM
PROJECT A,
(SELECT DISTINCT
B.PROJ_ID
FROM
REMINDER B
WHERE
B.REM_TYPE = 'LAST') B,
(SELECT DISTINCT
C.PROJ_ID
FROM
NOTES C
WHERE
C.NOTE_TYPE = 'QA') C
WHERE
A.STAT_CD = 'CLOSED'
AND A.PROJ_ID = B.PROJ_ID
AND A.PROJ_ID = C.PROJ_ID;

Very likely, you will need an index on the PROJ_ID column for each
table, especially if the REMINDER and NOTES tables contain columns
that are wide. An index on REMINDER.REM_TYPE might help. An index on
NOTES.NOTE_TYPE also might help. Take a look at the explain plan
(preferrably DBMS_XPLAN) for your query, and compare it to the explain
plan for the one above. Also, make certain that the tables and
indexes are analyzed (use DBMS_STATS for Oracle 8i and above).

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Roy,

Unless I'm missing something or have misunderstood ... have you tried just
straight inner join syntax? It should be the most optimized (but not
always).

select count(a.projectid)
from project a
inner join reminder b on a.proj_id = b.proj_id
inner join notes c on a.proj_id = c.proj_id
where
a.stat_cd = 'CLOSED'
and b.rem_type = 'LAST'
and c.note_type = 'QA'

-- Bill

"Roy" <roy.ball@gmail.com> wrote in message
news:1172523215.449990.175370@v33g2000cwv.googlegr oups.com...
>I am learning SQL (trial by fire) and have a question regarding a
> query optimization. Lets say I have three tables 'project', 'notes' &
> 'reminder'. Key on 'project' is 'proj_id'. The other two tables
> reference this key as 'notes' contains note entries on a given project
> and 'reminder' tracks todo type and due dates.
>
> select count(a.proj_id)
> from project a
> where a.stat_cd = 'CLOSED'
> and exists
> (
> select b.proj_id
> from reminder b
> where b.rem_type = 'LAST'
> and a.proj_id = b.proj_id
> )
> and exists
> (
> select c.proj_id
> from notes c
> where c.note_type = 'QA'
> and a.proj_id = c.proj_id
> )
>
> I am trying to determine the number of projects that have a 'CLOSED'
> status, contain a reminder type of 'LAST' and has a note type of 'QA'
>
> I get the result I am looking for but these are very large tables and
> it takes over 30 minutes for this to run. Is there a better way to
> write this query? I'm betting there is. Thank you in advance for your
> advice.
>
> Regards...
>

Roy wrote:
> We do not do non-billable, in-house improvements (matter of policy -
> yes, even for something as easy as adding an index). I'm a BA on a
> customer site who has lost his Oracle guru to the military. I don't
> have the immediate budget to grab a contractor so... I'm temporarily
> up the proverbial creek, sans paddle.
>
> The goal here is to mine potential data for a new interface I'm
> designing. Now - what would a script look like that adds the indexes
> (or temp tables?), performs the query, and then drops the temporary
> elements?
>
> -rgb
>
> On Feb 26, 4:54 pm, DA Morgan <damor...@psoug.org> wrote:
>> Roy wrote:
>>> Also - for whatever the reasons, indexing is not an option (per my
>>> developers)
>> Please explain this. If "my developers" ever said this to me my first
>> reaction would be to ask them to work on their resumes.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor...@x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

Good recommendations from Charles and AlterEgo ... but without the
ability to build an index ... it sounds like the "policy" is rather
expensive ... unless you are donating your time.

A policy that costs more than the alternative is a bad policy.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

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.


On Feb 26, 8:21 pm, DA Morgan <damor...@psoug.org> wrote:
> Roy wrote:
> > We do not do non-billable, in-house improvements (matter of policy -
> > yes, even for something as easy as adding an index). I'm a BA on a
> > customer site who has lost his Oracle guru to the military. I don't
> > have the immediate budget to grab a contractor so... I'm temporarily
> > up the proverbial creek, sans paddle.
>
> > The goal here is to mine potential data for a new interface I'm
> > designing. Now - what would a script look like that adds the indexes
> > (or temp tables?), performs the query, and then drops the temporary
> > elements?
>
> > -rgb
>
> > On Feb 26, 4:54 pm, DA Morgan <damor...@psoug.org> wrote:
> >> Roy wrote:
> >>> Also - for whatever the reasons, indexing is not an option (per my
> >>> developers)
> >> Please explain this. If "my developers" ever said this to me my first
> >> reaction would be to ask them to work on their resumes.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor...@x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org
>
> Good recommendations from Charles and AlterEgo ... but without the
> ability to build an index ... it sounds like the "policy" is rather
> expensive ... unless you are donating your time.
>
> A policy that costs more than the alternative is a bad policy.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

On Feb 27, 12:07 am, "Roy" <roy.b...@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.

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...@yahoo.com> wrote:
> On Feb 27, 12:07 am, "Roy" <roy.b...@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.