RE: Oracle Partitioning

From: TJ Kiernan <tkiernan_at_pti-nps.com>
Date: Mon, 28 Oct 2013 14:26:15 +0000
Message-ID: <196DB2D4BDE5804EAF3158CCC1C698BC09B35076_at_lopez.pti-nps.com>



As with everything, especially Oracle-related, it depends. Query performance is a part of the consideration. Information lifecycle management and contention prevention are some other benefits to consider.

As for your questions on query performance, you can certainly reduce the amount of data scanned if your queries happen to filter on the partition key. Depending on your partitioning scheme, you may actually find that index access is more costly than full scans on a few partitions. Indexes can (and generally should, unless you're maintaining primary/unique keys) be partitioned as well - either local (using the same partition key as the table) or global - partitioning on some other column(s). A little bit of research and planning goes a long way.

HTH,
T. J.
 
The information contained in this message is privileged and confidential information intended only for the use of the individual or entity identified above. If the receiver of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, use or copying of this message is strictly prohibited. If you have received this message in error, please immediately notify the sender by replying to his/her e-mail address noted above and delete the original message, including any attachments. Thank you.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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

.
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 28 2013 - 15:26:15 CET

Original text of this message