RE: Would you hash partition a hash'ed column?

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 5 Jun 2018 21:46:12 -0400
Message-ID: <00a901d3fd38$26c42120$744c6360$_at_rsiz.com>



Sorry for the autocorrect error on your name, S T E F A N.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Tuesday, June 05, 2018 9:23 PM
To: knecht.stefan_at_gmail.com; 'Woody McKay' Cc: 'ORACLE-L'
Subject: RE: Would you hash partition a hash'ed column?  

  1. Good questions by Stephan, and knowing your requirement could help fine tune the answer
  2. The rec update date or the rec insert date is the current partition key? If the former, that implies row movement enabled, and I can’t quite figure out what the rolling age date is that allows you to know rows can get shoved into history. If the latter, that is sweet, because archiving can be done via partition exchange. You would lose that with hash partitioning.

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stefan Knecht Sent: Tuesday, June 05, 2018 8:08 PM
To: Woody McKay
Cc: ORACLE-L
Subject: Re: Would you hash partition a hash'ed column?  

What's the problem you're trying to solve by changing the partitioning method? Improve data load performance? Improve query performance of reports, etc ?      

On Wed, Jun 6, 2018 at 6:33 AM, Woody McKay <woody.mckay_at_gmail.com> wrote:

Hi gurus,  

Oracle 12.1.0.2 on Exadata...  

Got a VLDB. Various tables are updated/inserted daily from various data sources. The table from one source has roughly 8 billion records. The table is currently range/interval partitioned on the rec update date/timestamp. However, the PK and column used for the incoming data source upsert is a hash key that's about 30 chars long and made up of digits and alphas.  

Have the thought of changing the partition to be a hash partition of the PK column that contains a hash'ed value. Does anyone have any thoughts on if that would make sense of if there are any pros or cons for hashing partitioning a hash value?  

Update. I found out that records older than x years are never updated, so we've moved them to a history table. That took the rec count down from 8 billion to about 750 million. That has helped performance much, but still wondering about hash partitioning the hash key...  

--

Thanks for any thoughts...

Woody  

--

//

zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!

Visit us at <http://zztat.net/> zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 06 2018 - 03:46:12 CEST

Original text of this message