RE: more on 'high cpu...'

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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 Received on Mon Jul 02 2007 - 17:13:32 CDT