Re: Query Performance Question

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? Received on Wed Mar 02 2005 - 04:12:30 CST