Partitioning Questions

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01BFCF1A.A67C3340

Content-Type: text/plain;

        charset="iso-8859-1"

We are just now beginning to work with partitioned tables and views. I have a few nagging questions. One of them is -- What is the true performance benefits of partitioning? I am not talking about the maintenance benefits, just the query performance benefits.

For example, since our queries will be using the indexes, no reason to partition the table right? I guess there would be some benefit on partitioning the table so that multiple disk drives could be working to retrieve the data. Any other performance benefits (not including the DDL benefits)?

And what about the index partitioning that we are going to do. It kind of seems like we are doing Oracle's work for them. I mean, we are telling the optimizer that this partition has these values, this other partition has these other values, etc. Is this not just a leaf block in a b-tree index or am I missing something? Why would this help the optimizer?

Ok, you can blast away. I am sure that I am missing something obvious and maybe someone can explain it to me. What are the true performance benefits of table and index partitioning? (again, not the DDL benefits).

Tom

Tom Terrian
Oracle DBA
(937) 221-1647
Thomas.Terrian_at_standardregister.com

------_=_NextPart_000_01BFCF1A.A67C3340

Content-Type: application/ms-tnef
Content-Transfer-Encoding: base64

eJ8+IjsSAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQWAAwAOAAAA0AcGAAUADgATADcAAQA7AQEggAMADgAAANAHBgAF AA4AEwA5AAEAPQEBCYABACEAAAA3ODhEOERBMzAzM0JENDExOUFBRTAwQTBDOUQ1RDdGQQBVBwEE gAEAFwAAAFBhcnRpdGlvbmluZyBRdWVzdGlvbnMA4wgBDYAEAAIAAAACAAIAAQOQBgCECQAAMgAA AAIBcQABAAAAFgAAAAG/zxrNUpF32Ns6vBHUndkAwE/yhfoAAB4ACYAIIAYAAAAAAMAAAAAAAABG AAAAADaFAAABAAAAAQAAAAAAAAAeAAuACCAGAAAAAADAAAAAAAAARgAAAAA4hQAAAQAAAAEAAAAA AAAAAwDeP69vAAADAAGACCAGAAAAAADAAAAAAAAARgAAAABShQAA8BMAAB4AAoAIIAYAAAAAAMAA AAAAAABGAAAAAFSFAAABAAAABAAAADguNQALAA6ACCAGAAAAAADAAAAAAAAARgAAAAAGhQAAAAAA AAMAA4AIIAYAAAAAAMAAAAAAAABGAAAAAAGFAAAAAAAACwAEgAggBgAAAAAAwAAAAAAAAEYAAAAA A4UAAAAAAAALAAWACCAGAAAAAADAAAAAAAAARgAAAAAOhQAAAAAAAAMABoAIIAYAAAAAAMAAAAAA AABGAAAAABCFAAAAAAAAAwAHgAggBgAAAAAAwAAAAAAAAEYAAAAAEYUAAAAAAAADAAiACCAGAAAA AADAAAAAAAAARgAAAAAYhQAAAAAAAAsADIALIAYAAAAAAMAAAAAAAABGAAAAAACIAAAAAAAACwAN gAsgBgAAAAAAwAAAAAAAAEYAAAAABYgAAAAAAAACAQkQAQAAAKsDAACnAwAADwYAAExaRnXDk6lX AwAKAHJjcGcxMjXiMgNDdGV4BUEBAwH3/wqAAqQD5AcTAoAP8wBQBFY/CFUHshElDlEDAQIAY2jh CsBzZXQyBgAGwxEl9jMERhO3MBIsETMI7wn3tjsYHw4wNREiDGBjAFCzCwkBZDM2FlALp2MBMDAg V2UgCsAdQGp1EnMFQG5vB+BiZWcDC4ADAG5nIHRvIAp3BbBrHvBpdGgg9QqxdB9gaQIgCYAewAGg LmwHkQBwIEB2CJB3c6AuICBJIBPgdh1BxCBmB9FuYWceUR6wXnEKUB3AIAEhUk8gICCEb2YewGhl bSAEAPggLS0dIBPgBUAkYSQRvR7AcgpQH5AEkAIQcgOB7mMdQB4wICBmH2AEICPh6x+nHpE/IXJh JEAd8AVA+QGQbGsekgGgCGAosSVR3wDAC4AOsCJgJlosHZQlQtki4XJ5Jc8m0i4KogqEvQqARgWx DsAoYAtQZSswfwCQJlIIYSvTCJAEIAPwbPsDIB4wIB2wHpMlUQuAAQAueAeQKzAd8CAYIGFzPwIg HsIfpyU0IHIyEGln9Gh0KBNnIvElIx1xHwD8dWwgQDCRMlAHgCaGI9C/A6AnWjNZMlAkASThbTWA sx/QLtEgZAQAHzBkBRD/IdAEIAWgNYUfAh6VGCAlgFcIkCHRJUJkJOBhIWFBvm4sICigNREsPybT KCiSjy8xCkA5cDD1RERMJofcKT8tmjywIEB3JNIpWJcxYzbuJOF3HURnbx6V/GRvIWIFQCkBIEAj 4RQQ4yQwBCBsaWs1QR1ERSDZHpJPcgDQIJAnMCEfEv89gSQDIWMHgABwKzBEJQ6wmzBgMOZvBTAH cGl6EoH/OJMfcCRhMsgT4CUjFBAhAP8HQCLxKzBL0zz1TD8jwT0D+01mFCBjIWIlIiRhKJIdo5si ACCQYSPwAmBvYx8w8wuAIfFiLSWACeBClQWx/yhhSTEEAR6SNfJL0SfzJMDPLCA1ZEvTJCBscEqc KBHpLZpPaysweQhgOiADkccCYDJAQeF3YXkhYyhhfnMIcDviJOEoQ1SfI9Bi7yEQCGAgtADAeTXF IBFZQ/8OwAtTJFAosR7gB4AhYSTDf0njJV8mbTOkINJCrygCKL8icAtxMdIplD/bLYtUA3D5Zk0g VASQByELkFiER7MJP8BCQS2UKDkzNwApIDIyMS0xNnQ0N2alaANxIVBoNUB/HcAg0QsRGCAeUB3A BJAuCwWgZyV9beAAHgBwAAEAAAAXAAAAUGFydGl0aW9uaW5nIFF1ZXN0aW9ucwAACwACAAEAAAAD AP0/5AQAAB4ACoAIIAYAAAAAAMAAAAAAAABGAAAAADeFAAABAAAAAQAAAAAAAABAADkAQDN8phrP vwEDAPE/CQQAAB4AMUABAAAACAAAAFRURVJSSUEAAwAaQAAAAAAeADBAAQAAAAgAAABUVEVSUklB AAMAGUAAAAAAAwAmAAAAAAADADYAAAAAAAMAgBD/////CwDyEAEAAAACAUcAAQAAADUAAABjPVVT O2E9IDtwPVN0ZFJlZztsPU9IX0RBWVROX1hDSDAtMDAwNjA1MTgxOTU1Wi00MzI4AAAAAAIB+T8B AAAAYwAAAAAAAADcp0DIwEIQGrS5CAArL+GCAQAAAAAAAAAvTz1TVEFOREFSRCBSRUdJU1RFUiBD T01QQU5ZL09VPVNSQ19DT1JQT1JBVEUvQ049UkVDSVBJRU5UUy9DTj1UVEVSUklBAAAeAPg/AQAA ABAAAABUZXJyaWFuLCBUaG9tYXMAHgA4QAEAAAAIAAAAVFRFUlJJQQACAfs/AQAAAGMAAAAAAAAA 3KdAyMBCEBq0uQgAKy/hggEAAAAAAAAAL089U1RBTkRBUkQgUkVHSVNURVIgQ09NUEFOWS9PVT1T UkNfQ09SUE9SQVRFL0NOPVJFQ0lQSUVOVFMvQ049VFRFUlJJQQAAHgD6PwEAAAAQAAAAVGVycmlh biwgVGhvbWFzAB4AOUABAAAACAAAAFRURVJSSUEAQAAHMJYiN6Yaz78BQAAIMCa4pqcaz78BHgA9 AAEAAAABAAAAAAAAAB4AHQ4BAAAAFwAAAFBhcnRpdGlvbmluZyBRdWVzdGlvbnMAAB4ANRABAAAA RQAAADw3NzgyQkU2Q0ZENjREMjExOUE3QjAwQTBDOUQ1RDdGQTA1MDFFMTY5QG9oX2RheXRuX3hj aDAyLnN0ZHJlZy5jb20+AAAAAAsAKQAAAAAACwAjAAAAAAADAAYQyL16FQMABxDWAwAAAwAQEAAA AAADABEQAAAAAB4ACBABAAAAZQAAAFdFQVJFSlVTVE5PV0JFR0lOTklOR1RPV09SS1dJVEhQQVJU SVRJT05FRFRBQkxFU0FORFZJRVdTSUhBVkVBRkVXTkFHR0lOR1FVRVNUSU9OU09ORU9GVEhFTUlT LS1XSEFUSVMAAAAAAgF/AAEAAABFAAAAPDc3ODJCRTZDRkQ2NEQyMTE5QTdCMDBBMEM5RDVEN0ZB MDUwMUUxNjlAb2hfZGF5dG5feGNoMDIuc3RkcmVnLmNvbT4AAAAAXDM= Received on Mon Jun 05 2000 - 13:19:55 CDT