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

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: kjped1313@xxxxxxxxx
  • Date: Wed, 12 May 2010 16:27:35 +0100

I'm probably skim reading, rushing for a train, but would an alternative be
hash partitioning based on the same key - obviously you like rebuilding muti
billion row tables so that'll be nice and easy to test :)

Niall

On Wed, May 12, 2010 at 3:33 PM, 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..."
>
>


-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2010