Re: partitioning question

--openmail-part-0919cb24-00000001

Content-Type: text/plain; charset=US-ASCII; name="BDY.TXT"
Content-Disposition: inline; filename="BDY.TXT"
Content-Transfer-Encoding: 7bit

Hi Nigel,

>Can anybody give a definitive answers to these questions:

Yes, see below. + Oracle 8i concepts/admin/tuning for more details. It's all in the Fine Manual :)

>Will 8.1.5 running under the RULE based optimiser recognise that a table or
index has been partitioned, and thus access only the some partitions to get the
required data for the query?

Straight from the Oracle 8i Concepts:

The following features can only use cost-based optimization:
* partitioned tables

• partition views
• index-organized tables
• reverse key indexes
• bitmap indexes
• function-based indexes
• SAMPLE clause in a SELECT statement
• parallel query and parallel DML
• star transformation
• star join
• extensible optimization

The operation that determines which partitions to limit the probes is called 'SQL Pruning'.

>Will I have to use a hint combined with statistics to make use of this facility?

What did you mean? The only hint applicable for RBO is the /*+ RULE */ hint - there is no hint that combines stats...

John Kanagaraj
Brunei Shell Petroleum
http://www.geocities.com/john_sharmila

God so loved the world that He didn't send a committee! (See John 3:16 for details)
** Opinions expressed here are solely mine and not necessarily those of my employer **

--openmail-part-0919cb24-00000001

Content-Type: application/ms-tnef; name="WINMAIL.DAT"
Content-Disposition: attachment; filename="WINMAIL.DAT"
Content-Transfer-Encoding: base64

eJ8+IsgfAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5N aWNyb3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEDkAYADAAAAAEAAAADABcAAQAA ABwAAQOQBgAMAAAAAQAAAAMANgAAAAAAOgABBIABABoAAABSZTogcGFydGl0aW9uaW5nIHF1 ZXN0aW9uAMEJAQOQBgAMAAAAAQAAAAsAAgABAAAADwABA5AGABAAAAABAAAAQAA5AIB/qKfo ur8BKgUBA5AGACQAAAABAAAAHgBCAAEAAAASAAAASm9obiBKLiBLYW5hZ2FyYWoAAAA7BgED kAYAEAAAAAEAAABAAEgAwIYjqei6vwH9BAEDkAYALAAAAAEAAAAeAHAAAQAAABoAAABSZTog cGFydGl0aW9uaW5nIHF1ZXN0aW9uAAAAawoBA5AGACgAAAABAAAAAgFxAAEAAAAWAAAAAb+6 6KejEXwF+SbJEdSTPgBgl5kRswAAvAoBA5AGAAwAAAABAAAAAwAGEAOvQ7vKAQEDkAYADAAA AAEAAAADAAcQBAQAACMAAQOQBgB4AAAAAQAAAB4ACBABAAAAZQAAAEhJTklHRUwsQ0FOQU5Z Qk9EWUdJVkVBREVGSU5JVElWRUFOU1dFUlNUT1RIRVNFUVVFU1RJT05TOllFUyxTRUVCRUxP VytPUkFDTEU4SUNPTkNFUFRTL0FETUlOL1RVTklOR0YAAAAAXh0BA5AGAAwAAAABAAAAAwAQ EAAAAAAkAAEDkAYADAAAAAEAAAADABEQAAAAACUAAQOQBgAQAAAAAQAAAEAABzDAhdqa5rq/ AZEFAQOQBgAQAAAAAQAAAEAACDDg8Q1c6Lq/ARUFAQOQBgAgAAAAAQAAAAIBCzABAAAAEAAA APcFfBHJJtQRkz4AYJeZEbPSBgEDkAYADAAAAAEAAAADAN4/r28AAD8CAQOQBgAkAAAAAQAA AAMAiYEIIAYAAAAAAMAAAAAAAABGAAAAABCFAAAAAAAA1wIBA5AGACQAAAABAAAAAwCKgQgg BgAAAAAAwAAAAAAAAEYAAAAAAYUAAAAAAADJAgEDkAYAJAAAAAEAAAADAIuBCCAGAAAAAADA AAAAAAAARgAAAAARhQAAAAAAANoCAQOQBgAkAAAAAQAAAAMAjIEIIAYAAAAAAMAAAAAAAABG AAAAAFKFAADjFQAAFAQBA5AGACwAAAABAAAAHgCNgQggBgAAAAAAwAAAAAAAAEYAAAAAVIUA AAEAAAAEAAAAOC41ANoDAQOQBgAkAAAAAQAAAAsAjoEIIAYAAAAAAMAAAAAAAABGAAAAAAaF AAAAAAAA2gIBA5AGACQAAAABAAAACwCPgQggBgAAAAAAwAAAAAAAAEYAAAAADoUAAAAAAADj AgEDkAYAJAAAAAEAAAADAJCBCCAGAAAAAADAAAAAAAAARgAAAAAYhQAAAAAAAOYCAQOQBgAs AAAAAQAAAB4AkYEIIAYAAAAAAMAAAAAAAABGAAAAADaFAAABAAAAAQAAAAAAAAAiAwEDkAYA LAAAAAEAAAAeAJKBCCAGAAAAAADAAAAAAAAARgAAAAA3hQAAAQAAAAEAAAAAAAAAJAMBA5AG ACwAAAABAAAAHgCTgQggBgAAAAAAwAAAAAAAAEYAAAAAOIUAAAEAAAABAAAAAAAAACYDAQOQ BgAkAAAAAQAAAAsAlIELIAYAAAAAAMAAAAAAAABGAAAAAACIAAAAAAAA4AIBA5AGACQAAAAB AAAACwCVgQsgBgAAAAAAwAAAAAAAAEYAAAAABYgAAAAAAADmAgEDkAYAFAAAAAEAAAAeAD0A AQAAAAEAAAAAAAAAXgABA5AGAAwAAAABAAAAAwCAEP////+QBAEJAAQAAgAAAAAAAAABA5AG AAwAAAABAAAACwAjAAAAAAAvAAEDkAYADAAAAAEAAAALACkAAAAAADUAAQSQBgBUBAAAAgAA ABIAAAADAAAwBAAAAAsADw4AAAAAAgH/DwEAAABHAAAAAAAAAIErH6S+oxAZnW4A3QEPVAIA AAEAb3JhY2xlLWxAZmF0Y2l0eS5jb20AU01UUABvcmFjbGUtbEBmYXRjaXR5LmNvbQAAHgAC MAEAAAAFAAAAU01UUAAAAAAeAAMwAQAAABUAAABvcmFjbGUtbEBmYXRjaXR5LmNvbQAAAAAD ABUMAQAAAAIB+Q8BAAAARwAAAAAAAACBKx+kvqMQGZ1uAN0BD1QCAAABEG9yYWNsZS1sQGZh dGNpdHkuY29tAFNNVFAAb3JhY2xlLWxAZmF0Y2l0eS5jb20AAAMA/g8GAAAAHgABMAEAAAAX AAAAJ29yYWNsZS1sQGZhdGNpdHkuY29tJwAAAgELMAEAAAAaAAAAU01UUDpPUkFDTEUtTEBG QVRDSVRZLkNPTQAAAAMAADkAAAAACwBAOgAAAAADAHE6AAAAAB4A9l8BAAAAFQAAAG9yYWNs ZS1sQGZhdGNpdHkuY29tAAAAAAIB918BAAAARwAAAAAAAACBKx+kvqMQGZ1uAN0BD1QCAAAB AG9yYWNsZS1sQGZhdGNpdHkuY29tAFNNVFAAb3JhY2xlLWxAZmF0Y2l0eS5jb20AAAMA/V8B AAAAAwD/XwAAAAACAfYPAQAAAAQAAAAAAAAEEgAAAAMAADAFAAAACwAPDgAAAAACAf8PAQAA AFcAAAAAAAAAgSsfpL6jEBmdbgDdAQ9UAgAAAQBUaG9tYXMuTmlnZWxAdmlhZ2ludGVya29t LmRlAFNNVFAAVGhvbWFzLk5pZ2VsQHZpYWdpbnRlcmtvbS5kZQAAHgACMAEAAAAFAAAAU01U UAAAAAAeAAMwAQAAAB0AAABUaG9tYXMuTmlnZWxAdmlhZ2ludGVya29tLmRlAAAAAAMAFQwC AAAAAgH5DwEAAABXAAAAAAAAAIErH6S+oxAZnW4A3QEPVAIAAAEQVGhvbWFzLk5pZ2VsQHZp YWdpbnRlcmtvbS5kZQBTTVRQAFRob21hcy5OaWdlbEB2aWFnaW50ZXJrb20uZGUAAAMA/g8G AAAAHgABMAEAAAAfAAAAJ1Rob21hcy5OaWdlbEB2aWFnaW50ZXJrb20uZGUnAAACAQswAQAA ACIAAABTTVRQOlRIT01BUy5OSUdFTEBWSUFHSU5URVJLT00uREUAAAADAAA5AAAAAAsAQDoA AAAAAwBxOgAAAAAeAPZfAQAAAB0AAABUaG9tYXMuTmlnZWxAdmlhZ2ludGVya29tLmRlAAAA AAIB918BAAAAVwAAAAAAAACBKx+kvqMQGZ1uAN0BD1QCAAABAFRob21hcy5OaWdlbEB2aWFn aW50ZXJrb20uZGUAU01UUABUaG9tYXMuTmlnZWxAdmlhZ2ludGVya29tLmRlAAADAP1fAQAA Received on Wed May 10 2000 - 20:31:42 CDT