ids query optimizer.
As I understand it, most query optimizers are cost-based. Some can be influenced by hints like FIRST_ROWS(). Others are tailored for OLAP. Is it possible to know more detailed logic about how Informix IDS and SE's optimizers decide what's the best route for processing a query, other than SET EXPLAIN? Is there any documentation which illustrates the ranking of SELECT statements? I would imagine that "SELECT col FROM table WHERE ROWID = n" ranks 1st. What are the rest of them?.. If I'm not mistaking, Informix's ROWID is a SERIAL(INT) which allows for a max. of 2GB nrows, or maybe it uses INT9 for TB's nrows?.. However, I think Oracle uses HEX values for ROWID. Too bad ROWID can't be oftenly used, since a rows ROWID can change. So maybe ROWID is used by the optimizer as a counter? Perhaps, it could be used for implementing the query progress idea I mentioned in my "Begin viewing query results before query completes" question? For some reason, I feel it wouldn't be that difficult to report a query's progress while being processed, perhaps at the expense of some slight overhead, but it would be nice to know ahead of time: A "Google-like" estimate of how many rows meet a query's criteria, display it's progress every 100, 200, 500 or 1,000 rows, give users the ability to cancel it at anytime and start displaying the qualifying rows as they are being put into the current list, while it continues searching?.. This is just one example, perhaps we could think other neat/useful features, the ingridients are more or less there. Perhaps we could fine-tune each query with more granularity than currently available? OLTP queries tend to be mostly static and pre-defined. The "what-if's" are more OLAP, so let's try to add more control and intelligence to it? So, therefore, being able to more precisely control, not "hint-influence" a query is what's needed and therefore it would be necessary to know how the optimizers logic is programmed. We can then have Dynamic SELECT and other statements for specific situations! Maybe even tell IDS to read blocks of indexes nodes at-a-time instead of one-by-one, etc. etc.
Ordinary ROWID isn't an INT it is the logical address of the row's page left shifted 8 bits plus the slot number on the page that contains the row's data. The IDS optimizer is a highly advanced cost based optimizer that uses data about the index depth and width, number of rows, number of pages, and the data distributions created by update statistics MEDIUM and HIGH to decide which query path is the least expensive. There is no ranking of statements. The SE optimizer is cost based when it has enough data but it does not use distributions like the IDS optimizer. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Apr 14, 2010 at 3:05 AM, FRANK@ FRANKCOMPUTER.COM < frank@frankcomputer.com> wrote: > As I understand it, most query optimizers are cost-based. Some can be > influenced by hints like FIRST_ROWS(). Others are tailored for OLAP. Is it > possible to know more detailed logic about how Informix IDS and SE's > optimizers decide what's the best route for processing a query, other than > SET > EXPLAIN? Is there any documentation which illustrates the ranking of SELECT > statements? > > I would imagine that "SELECT col FROM table WHERE ROWID = n" ranks 1st. > > What are the rest of them?.. If I'm not mistaking, Informix's ROWID is a > SERIAL(INT) which allows for a max. of 2GB nrows, or maybe it uses INT9 for > TB's nrows?.. However, I think Oracle uses HEX values for ROWID. Too bad > ROWID > can't be oftenly used, since a rows ROWID can change. So maybe ROWID is > used > by the optimizer as a counter? Perhaps, it could be used for implementing > the > query progress idea I mentioned in my "Begin viewing query results before > query completes" question? For some reason, I feel it wouldn't be that > difficult to report a query's progress while being processed, perhaps at > the > expense of some slight overhead, but it would be nice to know ahead of > time: A > "Google-like" estimate of how many rows meet a query's criteria, display > it's > progress every 100, 200, 500 or 1,000 rows, give users the ability to > cancel > it at anytime and start displaying the qualifying rows as they are being > put > into the current list, while it continues searching?.. This is just one > example, perhaps we could think other neat/useful features, the ingridients > are more or less there. Perhaps we could fine-tune each query with more > granularity than currently available? OLTP queries tend to be mostly static > and pre-defined. The "what-if's" are more OLAP, so let's try to add more > control and intelligence to it? So, therefore, being able to more precisely > control, not "hint-influence" a query is what's needed and therefore it > would > be necessary to know how the optimizers logic is programmed. We can then > have > Dynamic SELECT and other statements for specific situations! Maybe even > tell > IDS to read blocks of indexes nodes at-a-time instead of one-by-one, etc. > etc. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c92696f400600484303e28
Art Kagel wrote: > Ordinary ROWID isn't an INT it is the logical address of the row's page left > shifted 8 bits plus the slot number on the page that contains the row's > data. > > The IDS optimizer is a highly advanced cost based optimizer that uses data > about the index depth and width, number of rows, number of pages, and the > data distributions created by update statistics MEDIUM and HIGH to decide > which query path is the least expensive. There is no ranking of statements. > > The SE optimizer is cost based when it has enough data but it does not use > distributions like the IDS optimizer. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > [snip] SE's optimizer was rule based up to V5. This would most definitely apply to ISQL 2.10 -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Truth, they called it "Syntax Based" IB. I think I may still have a document the Informix NY Consulting manager Tony somebody wrote on how to optimize the syntax of SQL to help that optimizer produce better results. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Apr 14, 2010 at 8:35 AM, Marco Greco <marco@4glworks.com> wrote: > Art Kagel wrote: > > Ordinary ROWID isn't an INT it is the logical address of the row's page > left > > shifted 8 bits plus the slot number on the page that contains the row's > > data. > > > > The IDS optimizer is a highly advanced cost based optimizer that uses > data > > about the index depth and width, number of rows, number of pages, and the > > data distributions created by update statistics MEDIUM and HIGH to decide > > which query path is the least expensive. There is no ranking of > statements. > > > > The SE optimizer is cost based when it has enough data but it does not > use > > distributions like the IDS optimizer. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (art@iiug.org) > > > > [snip] > > SE's optimizer was rule based up to V5. This would most definitely apply to > ISQL 2.10 > -- > Ciao, > Marco > > ______________________________________________________________________________ > Marco Greco /UK /IBM Standard disclaimers apply! > > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > 4glworks http://www.4glworks.com > Informix on Linux http://www.4glworks.com/ifmxlinux.htm > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e6d272c7e581d2048432399a