Re: Oracle Partitioning
- From: Hans Forbrich <fuzzy.graybeard@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 28 Oct 2013 10:07:24 -0600
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
