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

From: Jared Still <jkstill@xxxxxxxxx>
To: kjped1313@xxxxxxxxx
Date: Wed, 12 May 2010 12:25:51 -0700

Syncsort:  http://www.syncsort.com

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com



On Wed, May 12, 2010 at 7:33 AM, Kellyn Pedersen <kjped1313@xxxxxxxxx>wrote:

> 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..."
>
>

References:
- Sorting Large Amounts of Data- Oracle to SAS, Part Deux
  - From: Kellyn Pedersen

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

Other related posts: