RE: Sorting Large Amounts of Data- Oracle to SAS, Part Deux

From: Pankaj Jain <pjain_at_ibasis.net>
Date: Wed, 12 May 2010 11:56:21 -0400
Message-ID: <D98F41646724244FAC1DC0B4462220250392645A_at_SERVER719C.VIPCALLING.CORP>

If oracle version is prior to 11g, you can use /*+ BYPASS_UJVC */ hint.

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kellyn Pedersen Sent: Wednesday, May 12, 2010 10:33 AM
To: oracle Freelists
Subject: Sorting Large Amounts of Data- Oracle to SAS, Part Deux

OK, so I'm looking for some help with this one again- I've found a compromise that has been quite successful for both groups so far: sorted hash cluster.  

I've been able to easily sort 1.4 billion rows to SAS without a blip on Oracle's side- now here's the challenge that I need help with:  

Can I do it in parallel?? If you knew my company, you'd understand-
"Let's just grab it all as fast as we can and use every resource on the
server!" :) I can't seem to find any parallel hint to force parallelism in the explain plans. Everytime I do get parallel, I lose the hash on the cluster and as it disregards it, also losing the sort.  

Here's the very simple, elegant query... :) Select * from affinity_summary_sort;  

Here's the table DDL:
create table AFF_SUM_SORT
(
IBID NUMBER,
IBHID NUMBER,
IND_MOD_KEY NUMBER,
RECENCY_KEY NUMBER,
VARIABLE_CD VARCHAR2(20),
VARIABLE_VALUE NUMBER
)
cluster SORTED_AFFSUM_CLSTR (RECENCY_KEY, IBID, IBHID);  

Here's my explain plan:

Description	 Owner	 Object	 Cost	 Rows	 Bytes	 Temp	 I/O
Cost	 CPU Cost	
SELECT STATEMENT, GOAL = ALL_ROWS	 		1
1471655172	 94185931008	 	1	 14986	
 SORT GROUP BY NOSORT	 		1	 1471655172
94185931008	 	1	 14986	
  TABLE ACCESS HASH	 DMART_SCHMA	 AFF_SUM_SORT	 1
1471655172	 94185931008	 	1	 14986	
 

Any ideas? Can this be forced to perform a parallel exec when a sorted hash cluster exists or does this bypass the cluster then?  

Thanks for any and all assistance on this one!

Kellyn Pedersen
Sr. Database Administrator
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen www.dbakevlar.blogspot.com <http://www.dbakevlar.blogspot.com/>  

"Go away before I replace you with a very small and efficient shell
script..."

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

Received on Wed May 12 2010 - 10:56:21 CDT