Re: HASH Partitioning question

Your query might run really quickly if you are selecting 10 million rows and using DOP of 300, but the rest of the applications accessing your database might suffer badly as your one query has just used up all of the parallel slaves in the instance and lit up all of the disks like a christmas tree.

Other considerations might include indexes. Are they partitioned, too? Locally?

Sure, if you are querying a hash-partitioned table and your query is going to hit every partition in the table, there's a good chance (but not a guarantee!) that PQ will make the query run faster -- but there is time spent setting up and tearing down the parallel slaves, so PQ is *not* always beneficial even to the single query at hand. What *will* happen, though, is your query *will* consume resources with (much?) greater intensity, and this easily could impact something else -- unless your computer has infinite CPUs and infinite (concurrent) IO capacity.

If you have 400 cores to work with, you'll probably want to be using at least some parallelism. But if you have only 4, probably not so much.

On Thu, Feb 12, 2015 at 4:17 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