Re: Strange Problems with 2 Similiar Queries

From: JJ Reynolds <jjreynol_NOSPAM_at_comcast.net>
Date: Tue, 1 Mar 2005 23:54:52 -0500
Message-ID: <QYCdnQ_xdpyu2rjfRVn-pw@comcast.com>

I see no relationship between the table (w100.papp) and the inline view (rn) in your where clause. I am a little surprised that the first query works.

select *
from
(select instruction_nr,

           mach_proc,
           positions_nr,
           row_number() over (partition by instruction_nr, mach_proc order 

by papp.positions_nr) rn
 from w100.papp
)
where rn=2 and mach_proc='3530'

is probably closer to what you need.. (?)

Good luck.

"Paul Izzo" <paul.izzo_at_mosca-ag.com> wrote in message news:1109684424.274708.48960_at_z14g2000cwz.googlegroups.com...
> I'm running a 9i database with OLAP enabled. I'm looking to run 2
> queries on the same table. The 2 queries are pretty much identical
> except for 2 variables.
>
> The problem that I'm having is that 1 query works and the other
> doesn't and I don't know why. Here's an example of the query that I'm
> running:
>
> 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'
>
> This query works perfectly I get all WORK INSTRUCTIONS that have
> MACH_PROC '3530' as the second step. The query searches through 2492
> records.
>
> I run a similiar query that looks for the 3rd step of an WORK
> INSTRUCTION containing MACH_PROC '8860'. This query searches through
> 35273 records. Here's what that query looks like:
>
> 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'
>
> Although the 2 queries look almost identical they run totally
> different. When I run the first query it max's out my CPU for the
> duration of the query and when looking at the session in Enterprise
> Manager it contains an extra tab called "Long Operations".
>
> The Long Operations tab tells how many blocks were processed and how
> long the query should last.
>
> When I run the 2nd query the CPU is not maxed out. The CPU usage
> hovers between 10-30%. When I look in Enterprise Manager I don't see a
> tab for "Long Operations"
>
> Too test the query I narrowed down it's search records. I added an
> extra line at the bottom of the query containing:
>
> and ( ( "instruction_nr" LIKE '0101-010000%') )
>
> This brings the query down to 30 records, but the same problem
> occurs.
>
> I also added this extra line into the query that works and it
> performs like it did before only faster because it searches less
> records.
>
> I don't know why this is happening.
>
Received on Tue Mar 01 2005 - 22:54:52 CST