Re: Query Performance Question

Paul Izzo wrote:

> Stepen,
>
> The 2 queries are as follows:
>
> 1. select
> distinct(papp.instruction_nr),papp.mach_proc,papp.positions_nr
> from w100.papp,
> (select row_number() over (partition by papp.instruction_nr
> order by papp.positions_nr) rn
> from w100.papp)x
> where x.rn = 2
> and papp.mach_proc = '3530'
>
> 2. select
> distinct(papp.instruction_nr),papp.mach_proc,papp.positions_nr
> from w100.papp,
> (select row_number() over (partition by papp.instruction_nr
> order by papp.positions_nr) rn
> from w100.papp)x
> where x.rn = 3
> and papp.mach_proc = '8860'
>
> I have a table that contains work instruction information. What I'm
> looking to do is find specific "WORK INSTRUCTIONS" that contain
> specific "MACHINE PROCESSES" in the 2nd or 3rd step in the "WORK
> INSTRUCTION"
>
> The 1st query works perfect. I can even look for the 3rd step of a
> INSTRUCTION by changing the "where x.rn = 3" and so on.
>
> The only time I have problems with the queries is when I use
> "papp.mach_proc = '8860' "
>
> I don't know why this is happening. All the data lies on the same
> table. The only difference I se between the 2 MACHINE PROCESSES is
> that "8860" has 35,000 records and "3530" has 3500.
>
> The other MACHINE PROCESSES that I've tested all have records under
> 6,500 none have as many as "8860".
>
> Daniel,
>
> What are your ideas as to why this problem occurs? Can it be a
> problem with my SQL command or does this lie in my database
> configurations?

My thought is that there is something about your data of which you are not aware. Try a CTAS (CREATE TABLE AS) for the data contributing to the query and examine it, however painfully, row by row.

One question ... you have integers being presented as strings. Have you verified your data types? Is mach_proc really a VARCHAR2? Eliminate all implicit conversions.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)