RE: HASH Partitioning question

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 13 Feb 2015 09:46:26 -0500
Message-ID: <093601d0479b$d960d810$8c228830$_at_rsiz.com>

First, +42 on Tim’s answer. That is as complete an answer as you’re going to get for the generic case.  

Now that we know you’re actually trying to avoid this change and likely you’re using rolling exchange partition method based on date, and that you are apparently doing a two table join, we can be a bit more specific:  

1. IF we know the particular reason why the person making the suggestion thinks a move to hash partition would be better (probably some specific query), then we could possibly give you aid in determining if the theory holds enough water to warrant a test.
2. IF your major demand query is a union over range pairs of the tables matching the partitioning of both tables, then it is almost certain that you can do a major performance improvement by generating the sets of range specific joins strung together with union alls (or possibly even concatening piecewise generated single partition single range joins report results if the major order of the report is date and there is not overall totaling. In this fortunate event you could use manual piecewise parallelism on each partition range and only the final filewise concatenation would be serial. When such semantic manual parallelism is possible no PX overhead is required because they are separate non-parallel operations from Oracle’s viewpoint. If you’re producing data in a format for archiving or loading to another system, then you don’t even need to jamb the pieces together, you just need a reasonable output filename nomenclature and possibly a plan for making sure the pieces are loaded in the desired order or in parallel.) Please be certain to understand why union all is guaranteed correctly generating no inter-partition duplicates for matching partition ranges not only for equijoins but also for range matches matching the partition boundaries and that the all is required to avoid massive overhead slapping the pieces together with de-duplication.
3. IF (same as 2), it may be possible there is an additional secondary partitioning that will dramatically improve your join mechanics if your major reports are date first, something else second, with the “something else second” dominant enough in requests for it to be worthwhile to justify the extra work to subpartition.

good luck,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Deepak Sharma (Redacted sender "sharmakdeep_oracle_at_yahoo.com" for DMARC) Sent: Friday, February 13, 2015 12:55 AM To: tim_at_evdbt.com; ORACLE-L
Subject: Re: HASH Partitioning question  

Hi Tim,  

Some very good tips and explanation.Thanks.  

The 6TB arch logs is for the whole DB. We've got some tables that are 5-7TB in sizes, and several others that are smaller but in 100-300gb range.  

We do use direct-path loads (picked-up from your seminar in 2005), along with compress on target tables, otherwise the DB size would be even higher (avg compress ratio we get is 60%-70%).  

Let me try to re-frame the question.  

Say, we have 2 tables (5TB each):

First one is RANGE partitioned and the other HASH partitioned (no Sub-Partition), on the same DATE_KEY column.  

When a query that uses specific DATE_KEY value or values, in case of Range, it would prune to the relevant partition(s).  

However, (here's the doubt), in case of HASH, the same query would not Prune to specific partition(s), and the query will need to use Parallel, to access all the partitions.  

Personally, I want to keep the table as is with Range partitioned, but need to give an explanation why changing it to HASH would cause higher resource consumption (by enabling parallel for all the users).    

-Deepak
           

On Thursday, February 12, 2015 10:09 PM, Tim Gorman <tim_at_evdbt.com> wrote:  

Deepak,

One more question: when you say "changing it to HASH partition" from the present RANGE partitioning, do you mean changing it from RANGE to RANGE-HASH sub-partitioned, or just from RANGE to HASH, no sub-partitioning.

I think that the latter choice, changing from RANGE to HASH no sub-partitioning, is going to ruin your data loading scheme, if you're using the EXCHANGE PARTITION load technique. If you're not using the EXCHANGE PARTITION load technique and I suspect that might be the case after you state "no batch window for loads", then that may explain the 6 TB of archive logs per day because you're unable to use direct-path loads, but that's a whole other matter, isn't it?

Anyway, hopefully you're planning to change from RANGE to RANGE-HASH sub-partitioned, so that you can continue to load by date range and purge by date range.

At any rate, the architecture of PX is relevant to this question. There is a single query-coordinator (QC) session and "N" parallel-execution worker sessions. The QC session serves as a collation point for all the data returned from the worker sessions. So, parallel-execution queries can work well when you're scanning a large volume of data, but only returning a small number of rows. That is their primary use-case - throwing lots of CPU and memory at the problem at once.

Now what will happen if you have a parallel query that is scanning a large volume of data and returning a large number of rows? The single QC session will be overwhelmed with huge volumes of data being returned from all of the PX worker sessions, and so queuing will result. In other words, the QC becomes the bottleneck, and the total elapsed time of the parallel query drops to match the capability of that single QC session to return rows. In other words, not very fast. In this situation, it is probably faster to dispense with parallel execution altogether and just run a serial query.

So, if your proposed query is returning a large number (i.e. millions or billions) of rows, then you're just plain doomed. PX won't help. Indexes and partitioning won't help. At that point, it is probable that it isn't your query that needs to be tuned, but your application logic. After all, what use is millions or billions of rows unless they're all going to another table, in which case you should use INSERT ... SELECT. If you're just SELECTing them and then displaying them on a screen, nobody is ever going to look at them all, so what's the point? Likewise if you're going to print them in a report.

However, if your proposed query is returning a small number of rows after scanning a huge number of rows, then you're either filtering or aggregating or both. If you're filtering, then enabling partition-pruning or indexing can be your best bet. If you end up using indexing for filtering, then parallel execution is not likely to work well. If you enable partition-pruning (either by the RANGE partition-key or the HASH sub-partition-key), then you can still do FULL table scan with parallel execution, but now against a smaller volume of data, which will be faster.

Hopefully that helps?

Thanks!

-Tim

On 2/12/15 19:07, Deepak Sharma 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 <mailto:dmarc-noreply_at_freelists.org> <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 <mailto:tim_at_evdbt.com> <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 - 15:46:26 CET