IDS Forum

Re: Index Or-ing & And-ing

Posted By: Fernando Nunes
Date: Thursday, 5 November 2009, at 1:45 p.m.

In Response To: Re: Index Or-ing & And-ing (Art Kagel)

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 where
customer_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

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.