IDS Forum

Re: Index Or-ing & And-ing

Posted By: Art Kagel
Date: Thursday, 5 November 2009, at 2:26 p.m.

In Response To: Re: Index Or-ing & And-ing (Fernando Nunes)

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

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.