Index Or-ing & And-ing

Hi, Does IDS can use more than one index for same table to optimize the query plan. DB2 can combine the results of two indexes and they called it index Anding/Oring? Please suggest: 1- index on a field with very low cardinality 2- order of two columns in an index where one column having low cardinality and other with high cardinality (but column with low cardinality used in more queries than other column) 3- is there any performance issue if default value of column in an index is null or it has a non-null value (and not null constraint is also applied on that column) regards, Kamran

It sounds like fluff to me. I was always under the impression (could be wrong) that an index on a low cardinality column could be a performance problem as it will take the engine longer to scan all the index pages than it would to do a sequential scan of the table. But I admit that I am not an expert at this stuff. MM

I believe currently IDS can use more than one index for OR clauses. It may be dificult to get a query plan with it. Hopefuly in not too distant future IDS will be able to do what you are writing about. XPS can do it, and I believe this may be a candidate to be included in IDS. I don't really understand what you mean by "suggest". If you want comments on your items: 1) It may be relevant in lots of cases. It really depends on the ability of that index to filter a significant number of records when there is no better index... It may also be needed (and automatically created) if that field is a foreign key to another table 2) Typically IDS would not use an index if you don't include the colum header in the list of filters. Currently, specially after the introduction of INDEX_SJ (index self-join) it may use the index in these cases. 3) Informix indexes NULLs. Other databases don't. So, from the index perspective a NULL is like any other value (not sure where they're stored - begin/end of index -) Regards. On Thu, Nov 5, 2009 at 1:21 PM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > Hi, > > Does IDS can use more than one index for same table to optimize the query > plan. DB2 can combine the results of two indexes and they called it index > Anding/Oring? > > Please suggest: > 1- index on a field with very low cardinality > 2- order of two columns in an index where one column having low cardinality > and other with high cardinality (but column with low cardinality used in > more > queries than other column) > 3- is there any performance issue if default value of column in an index is > null or it has a non-null value (and not null constraint is also applied on > that column) > > regards, > Kamran > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd58e8074de740477a1a956

Version information would be VERY helpful and it's always a good idea to post such info along with your platform details. The current releases of Informix do not use more than one index for any single table in a query. There is not and'ing and or'ing of index search results. Answers to your other questions are below: Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Nov 5, 2009 at 8:21 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > Hi, > > Does IDS can use more than one index for same table to optimize the query > plan. DB2 can combine the results of two indexes and they called it index > Anding/Oring? > > Please suggest: > 1- index on a field with very low cardinality > In general it's a bad idea. You should append another column with better cardinality for best results. > 2- order of two columns in an index where one column having low cardinality > and other with high cardinality (but column with low cardinality used in > more > queries than other column) > High cardinality column first is better for the optimizer, however, if you are running IDS v11.50 or later (see I told you version info is helpful) the optimizer can take advantage of the new Index Self Join feature to use such an index even for queries that only specify the second and following columns, but not the lead column. In older releases, however, you would need to have two indexes. One with the low cardinality column leading for queries that only specify that column and one with the higher cardinality column leading for more efficient queries where it can be used. Either that or just create the first index and suffer the lower query efficiency in exchange for the storage efficiency. > 3- is there any performance issue if default value of column in an index is > null or it has a non-null value (and not null constraint is also applied on > that column) > No, unless a majority of values contain NULL for that column and it is an indexed column, but that would be true for any single value that dominates most of the rows of an indexed column. NULL isn't special in that way. > > regards, > Kamran > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bddb80a84ff0477a36643

Art, Current IDS versions (I believe I've seen this on IDS 10, but I wouldn't bet on it) can in fact use two indexes for the same table in a single query: Nevertheless it's true that in real life it's hard to see a query plan like this: (run on IDS 11.50 with stores demo database) QUERY: (OPTIMIZATION TIMESTAMP: 11-05-2009 18:43:07) ------ select customer_num,zipcode from customer wherecustomer_num = 101 or zipcode = "08540" Estimated Cost: 2 Estimated # of Rows Returned: 2 1) informix.customer: INDEX PATH (1) Index Name: informix. 100_1 Index Keys: customer_num (Serial, fragments: ALL) Lower Index Filter: informix.customer.customer_num = 101 (2) Index Name: informix.zip_ix Index Keys: zipcode (Serial, fragments: ALL) Lower Index Filter: informix.customer.zipcode = '08540' Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 customer type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 2 2 2 00:00.00 2 On Thu, Nov 5, 2009 at 5:53 PM, Art Kagel <art.kagel@gmail.com> wrote: > Version information would be VERY helpful and it's always a good idea to > post such info along with your platform details. > > The current releases of Informix do not use more than one index for any > single table in a query. There is not and'ing and or'ing of index search > results. Answers to your other questions are below: > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. 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 Thu, Nov 5, 2009 at 8:21 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > > > Hi, > > > > Does IDS can use more than one index for same table to optimize the query > > plan. DB2 can combine the results of two indexes and they called it index > > Anding/Oring? > > > > Please suggest: > > 1- index on a field with very low cardinality > > > > In general it's a bad idea. You should append another column with better > cardinality for best results. > > > 2- order of two columns in an index where one column having low > cardinality > > and other with high cardinality (but column with low cardinality used in > > more > > queries than other column) > > > > High cardinality column first is better for the optimizer, however, if you > are running IDS v11.50 or later (see I told you version info is helpful) > the > optimizer can take advantage of the new Index Self Join feature to use such > an index even for queries that only specify the second and following > columns, but not the lead column. In older releases, however, you would > need to have two indexes. One with the low cardinality column leading for > queries that only specify that column and one with the higher cardinality > column leading for more efficient queries where it can be used. Either that > or just create the first index and suffer the lower query efficiency in > exchange for the storage efficiency. > > > 3- is there any performance issue if default value of column in an index > is > > null or it has a non-null value (and not null constraint is also applied > on > > that column) > > > > No, unless a majority of values contain NULL for that column and it is an > indexed column, but that would be true for any single value that dominates > most of the rows of an indexed column. NULL isn't special in that way. > > > > > regards, > > Kamran > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --0023545bddb80a84ff0477a36643 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd5391cd363250477a422af

IDS will ONLY use different indexes for OR clauses if the optimizer decides to break the OR condition into a UNION ALL of two separate SELECTs which it VERY RARELY does. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Nov 5, 2009 at 10:48 AM, Fernando Nunes <domusonline@gmail.com>wrote: > I believe currently IDS can use more than one index for OR clauses. It may > be dificult to get a query plan with it. > Hopefuly in not too distant future IDS will be able to do what you are > writing about. XPS can do it, and I believe this may be a candidate to be > included in IDS. > > I don't really understand what you mean by "suggest". If you want comments > on your items: > > 1) It may be relevant in lots of cases. It really depends on the ability of > that index to filter a significant number of records when there is no > better > index... > It may also be needed (and automatically created) if that field is a > foreign > key to another table > > 2) Typically IDS would not use an index if you don't include the colum > header in the list of filters. Currently, specially after the introduction > of INDEX_SJ (index self-join) it may use the index in these cases. > > 3) Informix indexes NULLs. Other databases don't. So, from the index > perspective a NULL is like any other value (not sure where they're stored - > begin/end of index -) > > Regards. > > On Thu, Nov 5, 2009 at 1:21 PM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > > > Hi, > > > > Does IDS can use more than one index for same table to optimize the query > > plan. DB2 can combine the results of two indexes and they called it index > > Anding/Oring? > > > > Please suggest: > > 1- index on a field with very low cardinality > > 2- order of two columns in an index where one column having low > cardinality > > and other with high cardinality (but column with low cardinality used in > > more > > queries than other column) > > 3- is there any performance issue if default value of column in an index > is > > null or it has a non-null value (and not null constraint is also applied > on > > that column) > > > > regards, > > Kamran > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --000e0cd58e8074de740477a1a956 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747b45c6745430477a4b3da

> 3) Informix indexes NULLs. Other databases don't. So, from the index > perspective a NULL is like any other value (not sure where they're stored - > begin/end of index -) > For an integral type and DATE, NULL is represented by the smallest possible number, which is -32768 for smallint and ..... the big ugly negative number for 32 bit INTEGER. -2^31. Therefore NULL will be "up front" For char, a NULL field is represented by null bytes (ascii 0) so once again NULL will be up front For decimal, I believe null is also represented by all zero bytes 'cos in the representation used for decimals, that does not mark a valid number So in general, NULL sorts first because it's bytes are always lower than real data.

Double and float NULLs sort to the beginning for a type specific sort and in the middle of the negative for a byte oriented sort on bigendian hardware. FWIW. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Nov 5, 2009 at 5:50 PM, Andrew Clarke <aclarke@civica.com.au> wrote: > > 3) Informix indexes NULLs. Other databases don't. So, from the index > > perspective a NULL is like any other value (not sure where they're stored > - > > begin/end of index -) > > > > For an integral type and DATE, NULL is represented by the smallest possible > number, which is -32768 for smallint and ..... the big ugly negative number > for 32 bit INTEGER. -2^31. Therefore NULL will be "up front" > > For char, a NULL field is represented by null bytes (ascii 0) so once again > NULL will be up front > > For decimal, I believe null is also represented by all zero bytes 'cos in > the > representation used for decimals, that does not mark a valid number > > So in general, NULL sorts first because it's bytes are always lower than > real > data. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174760426fe9320477a7c229