RE: Would you hash partition a hash'ed column?
Date: Tue, 5 Jun 2018 21:22:57 -0400
Message-ID: <009e01d3fd35$001c3920$0054ab60$_at_rsiz.com>
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-lReceived on Wed Jun 06 2018 - 03:22:57 CEST