Re: HASH Partitioning question

From: Ram Raman <veeeraman_at_gmail.com>
Date: Thu, 12 Feb 2015 20:53:33 -0600
Message-ID: <CAHSa0M0FzQ=9UYExDnmkz_41BXWpThEqvx6m5Qxg7c5y6hSgiA_at_mail.gmail.com>

ok, this does not answer your question, but I am left curious: 6TB of archives per day?? I am guessing most of that is due to loads since you mention '24x7 loading'. If that is the case, is there a way to create empty tables with nologging and load data in there using direct path and then do exchange partitions? That should help a lot given 90% of tables are partitioned.
I am trying to understand what creates 6TB of archives per day. What kind of application is it.

On Thu, Feb 12, 2015 at 8:07 PM, Deepak Sharma <dmarc-noreply_at_freelists.org> wrote:

> It's a DW environment close to 150TB with almost 90% of tables
> partitioned, generating 6TB archive logs a day (24x7 loading with no batch
> window for loads).
>
>
>
> On Thursday, February 12, 2015 8:02 PM, Deepak Sharma <
> dmarc-noreply_at_freelists.org> wrote:
>
>
> Sure Tim (btw, I attended your DW seminar several years ago in Minneapolis
> and have implemented a few ideas that have really helped).
>
> 600gb table with 14 Billion rows. It's currently partitioned on a loading
> date column by range, and has a rolling window retention. Someone suggested
> changing it to HASH Partition on another column (lets say ABC) which is
> more frequently used, but may not have even distribution (so Range would
> have skewed data). My guess is that in order for the queries to use that
> new ABC HASH partitioned column (it could be equi-join, In-List etc.), PX
> is needed.
>
> Let me know if you need more details.
>
> -Deepak
>
>
> On Thursday, February 12, 2015 3:19 PM, Tim Gorman <tim_at_evdbt.com>
> wrote:
>
>
> C'mon, you need to give us more than that, please?
>
> Query for one row? Query for 10 million rows? Aggregating? Not
> aggregating? Exadata or laptop or something in between? Oracle7?
> Oracle8i? Windows? Linux? Android?
>
>
>
> On 2/12/15 13:55, Deepak Sharma (Redacted sender
> sharmakdeep_oracle_at_yahoo.com for DMARC) wrote:
>
> Is it true that if a table (say 1 billion rows) is HASH Partitioned, then
> the most efficient way to query it needs to use Oracle parallel threads ?
>
>
>
>
>
>

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

Received on Fri Feb 13 2015 - 03:53:33 CET