IDS Forum

Re: Index Or-ing & And-ing

Posted By: Art Kagel
Date: Thursday, 5 November 2009, at 12:53 p.m.

In Response To: Index Or-ing & And-ing (KAMRAN HAQ)

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

Messages In This Thread

• Index Or-ing & And-ing
  KAMRAN HAQ -- Thursday, 5 November 2009, at 8:21 a.m.
  • Re: Index Or-ing & And-ing
    MIKE MAGIE -- Thursday, 5 November 2009, at 10:45 a.m.
  • Re: Index Or-ing & And-ing
    Fernando Nunes -- Thursday, 5 November 2009, at 10:48 a.m.
    • Re: Index Or-ing & And-ing
      Art Kagel -- Thursday, 5 November 2009, at 2:26 p.m.
    • Re: Index Or-ing & And-ing
      Andrew Clarke -- Thursday, 5 November 2009, at 5:50 p.m.
      • Re: Index Or-ing & And-ing
        Art Kagel -- Thursday, 5 November 2009, at 6:05 p.m.
  • Re: Index Or-ing & And-ing
    Art Kagel -- Thursday, 5 November 2009, at 12:53 p.m.
    • Re: Index Or-ing & And-ing
      Fernando Nunes -- Thursday, 5 November 2009, at 1:45 p.m.
    • Re: Index Or-ing & And-ing------WebKitFormBoundary
      ------WebKitFormBoundaryNnreJpc+oxePAGbj -- Monday, 16 November 2009, at 8:46 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.