RE: Fragmenting tables/indexes
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_01BCD897.B8EF19B0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit What is your "optimizer option" value set to? Save then explain output for your value if it was "1" or "2". Then change it to "0" and run the sql again with a new explain. See what changes in the explain and what index it chooses to use. On an additional note: I tested detached and attached indexes on fragmented by expression tables and found that detached indexes are faster than attached. Dave DeLuca Development DBA ddeluca@paymentech.com Paymentech, Inc. 4 Northeastern Blvd. Salem, NH 03848 (603) 896-8288 ---------- From: Clem Akins[SMTP:clem@informix.com] Reply To: Clem Akins Sent: Tuesday, October 14, 1997 12:16 AM To: informix-list@rmy.emory.edu Subject: Re: Fragmenting tables/indexes Cosmo advises: >Are you sure that your queries are _actually_ using the fragments to >eliminate data from the searches? We had a similar situation where data >was fragmented by dates. But when we ran certain queries, the optimizer >ignored the fragmentation scheme altogether, performing searches of all >fragments when it should have been able to eliminate subsets of data. Be sure that your understanding matches what the optimiser is built to do. It is easy to get out of sync there, as the rules for fragment elimination are not terribly straightforward. See the OnLine Performance Guide, page 3-49 (in my 7.22 CD-ROM version.) And to reinforce what Cosmo says: see the query plan with SET EXPLAIN to know for sure what the optimiser is doing. ___________________________________________________________ Clem Akins (aka clem@informix.com) <- NEW ACCOUNT! Informix Software, Inc (Standard disclaimers apply) International Technical Support Last seen: Corporate HQ, Menlo Park California, US of A ------ =_NextPart_000_01BCD897.B8EF19B0 Content-Type: application/ms-tnef Content-Transfer-Encoding: base64 eJ8+IgIQAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQmAAQAhAAAANTZGMzhFMjhBMDQ0RDExMTk5NDAwMDYwOTc4RUVC NzQA9gYBIIADAA4AAADNBwoADgALADUAAAACAC4BAQWAAwAOAAAAzQcKAA4ACwAwACgAAgBRAQEN gAQAAgAAAAEAAQABBIABAB8AAABSRTogRnJhZ21lbnRpbmcgdGFibGVzL2luZGV4ZXMAHQsBA5AG AMQIAAAYAAAAAwAmAAEAAAADAC4AAAAAAEAAOQBwT4cGwdi8AR4AcAABAAAAHwAAAFJFOiBGcmFn bWVudGluZyB0YWJsZXMvaW5kZXhlcwAAAgFxAAEAAAAWAAAAAbzYwQaCKI7zV0SgEdGZQABgl47r dAAAAwAGEIvX3BoDAAcQRwUAAB4ACBABAAAAZQAAAFdIQVRJU1lPVVIiT1BUSU1JWkVST1BUSU9O IlZBTFVFU0VUVE8/U0FWRVRIRU5FWFBMQUlOT1VUUFVURk9SWU9VUlZBTFVFSUZJVFdBUyIxIk9S IjIiVEhFTkNIQU5HRUlUVE8AAAAAAwAQEAAAAAADABEQAAAAAAIBCRABAAAATgYAAEoGAACNCwAA TFpGdf2NTBgDAAoAcmNwZzEyNf4yAP8CBgKkA+QF6wKDAFATA1QCAGNoCsBzZXT+MgYABsMCgw5Q AvMHEwKDNjMDxhQoNATWAgBwcuZxE7ESdjU1BgAFAwKDdjUQPxFEfQqACM8J2TvrGr8OIDgb32UX cR0jHPEvGwwZ4Q5iC2BuDhAwM4UUwGgFsHpkb2MAANoqElUgApEhYGwhlQr7yxOxC/JjE6AgVxKA BUCJBAAgeQhhICJvBTBpB3BpegSQICUiAiAiLCB2B0AKUCASsSB0WG8/IAqFBhB2JoB0YmgJ8CBl eAtTJbB19HRwKMAgAhAFwCTDJkQnBpAkgAVAd2EEICIxQyYgBbEiMiIuJxZUvyfyEnEgYCnxJsIl ADAmIMIgAHBkIHJ1A6An4eUmkHEDIGFnKHID8Cfg+S1AIG4H0Sg1K1Av7zDt/ycmCeAqYCRSLDQE ICiBLdLfKDYtUjKjC4AOcHgqMhJwXG9vErAEICzRdRKwLs0KhU80Qi1AZGQqQCXxAwdALxBvdGU6 IEn/JtAHkDgQLXAOcAGQEnA4sf8tUiRgOQU08weRAiAnFgNQzy5QB4ACMDixYnkoIhrA7wQQJfEm 0AGgbAeRLUMCEP8toC1wJ+AkYTjnOkYKhQrA/SaAZiqAOBAFwD4RNEE5xXc2Zgr7FfI0I+IAUAwx IAJEJ6JEZUx1Y2GfQj1DYkF3C2QYYjE2QxQ3Q+EnsBpQcDuiQ5BCQe9BdTdQR3BEIUBBoAbAO7JV EnAuBaBtRfs3I/Ezr0V/C9IV8EbkUEk3LDhAOG5jLkoMSy9KljEgPjQHsBphJ/BAMwOgQmwudkFX BhA9IG1NkE5IIy0wIJA4NDhBdSg2ASCQKSA4OTYtOB8dAFMmIs8j1VSLbGkxhDgwAtFpLTE0QvH/ DNBX4wtZV2BUoANgSYEFQL4tWgdO7VkVDDBZhkYDYc84MFifWaQMgiBDUlEUEAJrC4BzW1NNVFB0 OmNSUUALgCkhJWB4zUnCXVqvW71SZQtQPCD8VG9c713/XwRg71u9BmCXAjBjb2R7VApQc2RJMOVN kE9Z0G9iJZFX4E2Q2DE5OU7ADiA6RrAUEL5NZe9bvWNPZF1gFi1XQOU4kEBgUHkuUmAFsHEhjGR1 bC9m/nVialnBf25PZHti4DgwXKA7hAuAZ9085S86RVSFVn0zU7ALRm8UwgwBWYYIUHMEYDcxdusE AAeQOlSFPgcQJoAkwd8mkAhwJ8IkYSTDcQpQCIHLBCA/4l8A0HR1B0BjEP5fNiF24y3hO2Y14nvm R3D/JVE3sDgQONAkYC8AA1ItxPVQ8HI5IXMnACQwJoASgP85USaQJVELYAXAAJB+wSXi/zKRBJCB pHvmKnI7bIHBB5D9L9FCKPGFAS6RJoA7cCwR/wSQAZAogX3VTZAt0iUne+b8aWc38BrBf5uEpATw J/C7B4AtQGwm4CxwJ+FyTZD+cASQYDN28YKmJbAqIH7hL3vmf+iIAypBcyDQdWz/LXASgCexatAo AT0CLMKBKH184GISsY8TgcI2ZlSFQv+TQn0LPdEEkDiQLVF24gDAfnSO4zKjidgSsAXAJJFirnUD ECbCSDZvL9FJJHP/UPE8ICzRjXEooo8imuBNwP8n0hrATZAqgS3SLZA9IikifztmVIWBJjyyP+I3 8Thhcr8FEAJgPCA4kDtwiyBoADB/BbAqcAsglF0yYi3SNuBMOwuAJoBQjgQAcIjAIEcfmTAOcI3R LlAmgDMtNA45U5AogXEQIDcuMoES4ENELVJPTSYw05aRJfEuKXgMQT3iLODfGsBgE6PhMqN7BHNJ MHvAfy0wErCihH3SPCALUS6VUwBFVCBFWFBMQexJTlSFLNFrN/AH4Ckiv3zjl8+Y0yEQduGUXV+w Tz+xX7JvsuVUhV7IMOEoYZ5rLwBfz1PgMOU8LQewBEVXFBBDQ09VTnxUIVSFTbBgNAYAjzB0z6Ex nIFNsTDnKFOWwgsRPzjQBAC1gAtwB4AhwWFwv2MBptZNsFEihKM3wVRJkX8DAEQwAyBz0LvgGmFU hUzbQDGp4m6pwQhQcr5RgYJ0SFFNkE0J8BpQTOFyvmtesAdABpAFsAMAYU2Q/lUF8I8xSCZ47yN3 WYZUhQUZ4QDFwAAAAwA2AAAAAAACAfk/AQAAAB4AAAAAAAAA3KdAyMBCEBq0uQgAKy/hggEAAAAA AAAALgAAAB4A+D8BAAAAFQAAAFN5c3RlbSBBZG1pbmlzdHJhdG9yAAAAAAIB+z8BAAAAHgAAAAAA AADcp0DIwEIQGrS5CAArL+GCAQAAAAAAAAAuAAAAHgD6PwEAAAAVAAAAU3lzdGVtIEFkbWluaXN0 cmF0b3IAAAAAQAAHMABufSHA2LwBQAAIMEQldKHB2LwBAwANNP0/AAACARQ0AQAAABAAAABUlKHA KX8QG6WHCAArKiUXHgA9AAEAAAAFAAAAUkU6IAAAAAALACkAAAAAAAsAIwAAAAAAAgF/AAEAAABO AAAAPGM9VVMlYT1fJXA9UGF5bWVudGVjaCVsPVBBWU1FTlRFQ0gvREFMTEFTLzAwMDZENEJGQGRh bGxhc19wby5wYXltZW50ZWNoLmNvbT4AAAChzw== ------ =_NextPart_000_01BCD897.B8EF19B0--