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

Hi Woody

The strategy Jonathan outlines below is similar to the one we use for what sounds a fairly similar use case. In our case, we have a large number of incoming batches from various sources, PK includes the source and an ID field, queries are almost entirely date (and id) driven. We, in fact, use list instead of hash partitioning since our source list changes fairly infrequently and we wish to segregate sources that provide a large amount of data from sources that provide a much smaller amount (these we tend to group in the same list) - I think there might be restrictions on matching ranging and list partitioning in earlier versions.

Niall

On Wed, Jun 6, 2018 at 1:31 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

> Woody,
>
> If you have partitioned by some date but the PK is something completely
> different then I assume the index covering your PK is global or globally
> partitioned - which means a fairly large chunk of work on eliminating old
> data even if you did it by dropping/exchanging partitions - though you
> could do it "asynchronously" in 12c (see, e.g. Tim Hall:
> https://oracle-base.com/articles/12c/asynchronous-global-index-maintenance-for-drop-and-truncate-partition-12cr1
> ).
>
>
> Have considered using range/hash composite partitioning and making the PK
> index globally partitioned by hash ? This would give you the benefit of
> precision on DML with the benefit on dropping partitions for aged data ?
>
>
> Regards
> Jonathan Lewis
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Woody McKay <woody.mckay_at_gmail.com>
> Sent: 06 June 2018 13:15
> To: Mark W. Farnham
> Cc: knecht.stefan_at_gmail.com; ORACLE-L
> Subject: Re: Would you hash partition a hash'ed column?
>
> Hi all,
>
> Yes, Stefan, the problem I'd like to solve is data load performance
> (update/insert).
>
> The data is moved to staging and ETL'ed into the reporting DB, so no probs
> there.
>
> Mark, yes, the rec update/insert date is the current partition key. Yes,
> I'd lose the archival partition exchange if we change to hash partitioning,
> but the ongoing archival process in not as important as the daily data load
> right now. The source data load SLA is in jeopardy. But, that is an
> important discussion I'll have to have with our team.
>
> Outside of the model, seems like there is no problems with using hash
> partitioning on a column containing a hashed value?
>
> Thank you very much for your insight and thoughts.
>
> Woody
>
>
> On Tue, Jun 5, 2018 at 9:22 PM, Mark W. Farnham <mwf_at_rsiz.com<mailto:
> mwf_at_rsiz.com>> wrote:
>
> a) Good questions by Stephan, and knowing your requirement could help
> fine tune the answer
>
> b) 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>
> [mailto: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<mailto:
> 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 zztat.net<http://zztat.net/> | _at_zztat_oracle | fb.me/zztat<
> http://fb.me/zztat> | zztat.net/blog/<http://zztat.net/blog/>
>
>
>
> --
> Sincerely,
>
> WoodyMcKay
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

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