RE: more on 'high cpu...'

Yes. By doing a distinct you are basically telling Oracle not to send you anything until it has fetched the entire results. When you take out the distinct then Oracle can start sending you results while it continues to execute the query. This is because In order to give you a "distinct" set it must retrieve the complete result set then do a sort on it to find the unique values. Without the distinct qualifier you are probably getting a "first rows" type execution.

        -Randy    

Randy Johnson
Sr. Technical Consultant
Enkitec, LLP

Office ..... 817-255-3580
Mobile .... 817-564-6583
Email ..... randy.johnson_at_enkitec.com 

-----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 10: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?

--

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

No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.5.476 / Virus Database: 269.9.14/883 - Release Date: 7/1/2007 12:19 PM  

No virus found in this outgoing message. Checked by AVG Free Edition.
Version: 7.5.476 / Virus Database: 269.9.14/883 - Release Date: 7/1/2007 12:19 PM  

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jul 02 2007 - 11:22:19 CDT