FreeLists / oracle-l / RE: more on 'high cpu...'

["Mark W. Farnham" <mwf@xxxxxxxx>]

Question: Is the "hundreds of rows relatively quickly" the entire indistinct
result set?

Simple test, select count(*) from the view with the same predicates. If that
is fast, then try the view without the distinct, include a dummy column for
the rownum so the plan can't get compressed, and then select distinct from
the redefined view.

If you're just observing that hundreds of rows (possibly of millions or
more) start coming back quickly, then the count(*) will take a long time too
and you'll know someone else earlier in the thread was correct that the
non-distinct version of the view was just doing some variety of first rows
result set or at least not needing to wait for the de-duplicated-tupling
sort.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Joe Armstrong-Champ
Sent: Monday, July 02, 2007 11:35 AM
To: ORACLE-L
Subject: more on 'high cpu...'

I'm still doing the traces but in the meantime I thought I'd ask a 
follow up question. The query is selecting from a view. The view returns 
hundreds of rows relatively quickly. The query itself is selecting 
DISTINCT rows from the view. When I take out the distinct it returns 
almost instantaneously. With the distinct in it takes 40 - 50 secs. Does 
this ring a bell with anyone?<snip>


--
http://www.freelists.org/webpage/oracle-l