Re: HASH Partitioning question
From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Thu, 12 Feb 2015 17:26:53 -0500
Message-ID: <54DD28AD.3040409_at_yahoo.com>
On 02/12/2015 03:55 PM, 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 ?
>
Date: Thu, 12 Feb 2015 17:26:53 -0500
Message-ID: <54DD28AD.3040409_at_yahoo.com>
On 02/12/2015 03:55 PM, 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 ?
>
The answer is very characteristic of the Oracle community and it reads "it depends". It depends on the nature of your access. If you need only a single row, then access by rowid is the most efficient method of accessing any table, bar none. If you are accessing only the data with the same hash function value, hash partitioned table is a very good thing. If you are accessing all the data in the table, then yes, PQ with one reader per partition will be the most efficient method. Needless to say, you will also need hardware that can support that.
-- Mladen Gogala Oracle DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 12 2015 - 23:26:53 CET