RE: Oracle Partitioning
- From: "Larry Elkins" <elkinsl@xxxxxxxxxxx>
- To: <JBECKSTROM@xxxxxxxxx>, "'oracle-l-freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 29 Oct 2013 07:50:30 -0500
As others have touched on the benefits you can see in terms of general
maintenance capabilities, ETL processing, and your specific
questions below, one last thing to consider is the problem you are trying to
solve, the root cause(s), and will partitioning solve
them.
For example, with a recent client one of the teams wanted the partitioning
option to speed up some queries, processes, and extracts.
But when looking at the problem areas they were trying to address partitioning
would *not* have done much for them.
They had numerous large queries/extracts using single row fetches, where even
though they might have been full scanning 300 million
rows, fetching maybe 100,000, or 1 million, whatever, the *bulk* of their time
was not on the scan, but on the network and/or
inserting into remote DB's. And they had numerous poorly written *processes*
that were procedural in nature, programmatic joins,
etc. Then throw in some poorly written SQL as well as some well written SQL
with poor plans requiring tweaking of how stats were
gathered, and it was clear that partitioning wasn't going to solve their
issues, at least not to the degree they hoped/thought it
would by simply throwing partitioning into the mix. But orders of magnitude
performance improvements *were* realized by addressing
their root issues.
Don't get me wrong, I love partitioning. And while it can provide dramatic
performance increases in query performance, ETL
processing, etc, you would need to make sure it is the solution to the problem
you are trying to solve. Which of course requires a
good understanding of the "problem" areas and where time is spent.
Larry G. Elkins
elkinsl@xxxxxxxxxxx
Cell: 214.695.8605
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Jeffrey
> Beckstrom
> Sent: Monday, October 28, 2013 8:58 AM
> To: oracle-l-freelists; oracle-db-l
> Subject: Oracle Partitioning
>
> 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?
>
>
>
> Jeffrey Beckstrom
> Database Administrator
> Greater Cleveland Regional Transit Authority Information Systems
> 1240 W. 6th Street
> Cleveland, Ohio 44113
>
>
>
>
>
> .
> --
> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
