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

From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Date: Wed, 12 May 2010 07:33:11 -0700 (PDT)
Message-ID: <876959.40634.qm_at_web114510.mail.gq1.yahoo.com>

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
 

"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 - 09:33:11 CDT