RE: Oracle Partitioning

From: Larry Elkins <>
Date: Tue, 29 Oct 2013 07:50:30 -0500
Message-id: <001001ced4a5$75543bb0$5ffcb310$_at_net>

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
Cell: 214.695.8605

> -----Original Message-----
> From: [] 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
> .
> --

Received on Tue Oct 29 2013 - 13:50:30 CET

Original text of this message