Re: Oracle Partitioning
Date: Mon, 28 Oct 2013 10:07:24 -0600
Message-ID: <526E8BBC.1030202_at_gmail.com>
On 28/10/2013 7:58 AM, Jeffrey Beckstrom wrote:
> We are looking into the possibility of licensing the Partitioning option. My understanding of partitioning is that it only helps when the optimizer can eliminate partitions. Doesn't that mean that it is primarily of benefit when accessing the tables via full table scans? In that case, the optimizer could eliminate partitions from scanning. If, however, you are accessing the data via an index\, would there be any benefit?
>
> Is my understanding of this correct?
>
"Partition Pruning" is one area that partitioning can help us - as you describe - by not needing to go through portions of the table because the filter says "it can not be there"
Partition pruning can come into play even with a table scan - it may eliminate chunks even when not using an index. It can also help when an index on 'other columns' is used.
Remember that in general only one index will be used per 'table' in the FROM clause. Partitioning and pruning can potentially eliminate one column from being needed as an index candidate, opening up other columns for indexing. (Flip side - indexing and index maintenance does get more complicated as a result of partitioning.)
Therefore, partitioning could let the optimizer say "use the non-partition index related to this subset of the table".
There are also other areas that partitioning help, in particular DBA-related maintenance. If you have a data archive policy that says "eliminate, or push to nearline or offline, any data in this table older than X days/years/months", this operation can potentially be simplified and sped up using partitions. If you load data on a periodic basis, and need to integrate it into an existing table, partitioning could change the process and possibly speed it up. If you can take advantage of transportable tablespaces by scrubbing data at a remote database and copying/attaching the tablespace as a moderately fast operation.
You might want to review the concepts manual around Partitions - at http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT112 - for some other ideas.
It is one of the areas that the DBA can make a significant access path change without any change to the application.
But It's not a silver bullet. Your mileage may vary, depending on your needs, your typical queries as well as DML, your environment, the effort you can put in, and so on.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 28 2013 - 17:07:24 CET