MIME-Version: 1.0
From: Woody McKay <woody.mckay@gmail.com>
Date: Tue, 5 Jun 2018 19:33:54 -0400
Message-ID: <CAAxONsSQ4b0BObL9_p_dKCCJJA42dtsx8-ptt3EsGRSqCJvrcw@mail.gmail.com>
Subject: Would you hash partition a hash'ed column?
To: ORACLE-L <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="000000000000c42424056ded7b6c"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: woody.mckay@gmail.com
--000000000000c42424056ded7b6c
Content-Type: text/plain; charset="UTF-8"

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

--000000000000c42424056ded7b6c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi gurus,<div><br></div><div>Oracle 12.1.0.2 on Exadata...=
</div><div><br></div><div>Got a VLDB. Various tables are updated/inserted d=
aily from various data sources. The table from one source has roughly 8 bil=
lion records. The table is currently range/interval partitioned on the rec =
update date/timestamp.=C2=A0 However, the PK and column used for the incomi=
ng data source upsert is a hash key that&#39;s about 30 chars long and made=
 up of digits and alphas.</div><div><br></div><div>Have the thought of chan=
ging the partition to be a hash partition of the PK column that contains a =
hash&#39;ed value.=C2=A0 Does anyone have any thoughts on if that would mak=
e sense of if there are any pros or cons for hashing partitioning a hash va=
lue?</div><div><br></div><div>Update. I found out that records older than x=
 years are never updated, so we&#39;ve moved them to a history table. That =
took the rec count down from 8 billion to about 750 million. That has helpe=
d performance much, but still wondering about hash partitioning the hash ke=
y...=C2=A0<br clear=3D"all"><div><br></div>-- <br><div class=3D"gmail_signa=
ture" data-smartmail=3D"gmail_signature"><div>Thanks for any thoughts...<br=
><br>Woody</div></div>
</div></div>

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