Oninit cdi / Archive
Talk to an expert →

Re: How to force a query to use a particular index ??

Posted in 1999

Topics: Performance & Tuning, Connectivity: ESQL/C, 4GL & Embedded SQL

Mark D. Stock — — source: Informix-list mailing list archive (1991-1998)

Alan Wong wrote: > > I have a problem, ...I am using Informix 4GL version 4.20 on Online 5.10 > UC1. > > My application have to query for a record in a table which have 2 composite > indexes on it, say .. > > Table bonus ( > member_id integer, > promo_id integer, > promo_type char(1), > bonus_type char(5), > balance dec(16,2)) > with indexes > i_1 on bonus (member_id, bonus_type) > i_2 on bonus (promo_id, promo_type) > > On querying for a record using member_id, promo_id, promo_type, bonus_type > in the WHERE clause, with SET EXPLAIN ON, it shows index i_2 is used. This > index gives a set of about 20,000 record to search on. > > At other times, the same query uses index i_1, which gives a set of 5 > records !! The better index to use. > > Unfortunately, I cannot do without index i_2. So how can I force a SELECT > to use index i_1 in its query plan instead of i_2 ??? In simple terms, you can't until version 7.30. When did you last run UPDATE STATISTICS? Otherwise it might help to see the results of SET EXPLAIN for both types of SELECT. Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| |http://www.iiug.org +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 838250 2325 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+

↪ replying to Mark D. Stock
Richard Auslander — — source: Informix-list mailing list archive (1991-1998)

Mark - How IS a particular index selected using v7.30 of Informix? I'm new to this release and don't konw the latest features. Rich "Mark D. Stock" wrote: > Alan Wong wrote: > > > > I have a problem, ...I am using Informix 4GL version 4.20 on Online 5.10 > > UC1. > > > > My application have to query for a record in a table which have 2 composite > > indexes on it, say .. > > > > Table bonus ( > > member_id integer, > > promo_id integer, > > promo_type char(1), > > bonus_type char(5), > > balance dec(16,2)) > > with indexes > > i_1 on bonus (member_id, bonus_type) > > i_2 on bonus (promo_id, promo_type) > > > > On querying for a record using member_id, promo_id, promo_type, bonus_type > > in the WHERE clause, with SET EXPLAIN ON, it shows index i_2 is used. This > > index gives a set of about 20,000 record to search on. > > > > At other times, the same query uses index i_1, which gives a set of 5 > > records !! The better index to use. > > > > Unfortunately, I cannot do without index i_2. So how can I force a SELECT > > to use index i_1 in its query plan instead of i_2 ??? > > In simple terms, you can't until version 7.30. > > When did you last run UPDATE STATISTICS? > > Otherwise it might help to see the results of SET EXPLAIN for both types > of SELECT. > > Cheers, > -- > Mark. > > +----------------------------------------------------------+-----------+ > |Mark D. Stock - Informix SA http://www.informix.com |//////// /| > |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| > |http://www.iiug.org +-----------------------------------+//// / ///| > | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| > | Fax: +27 838250 2325 |If it's fast, the users keep quiet.|// / /////| > |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| > +----------------------+-----------------------------------+-----------+ -- Richard C. Auslander Database Manager AirFlash, Inc. 1733 Woodside Rd., Suite #110 Redwood City, CA 94061 (650) 556-7928 www.airflash.com

↪ replying to Richard Auslander
Thomas J. Girsch — — source: Informix-list mailing list archive (1991-1998)

In 7.3X you can do: SELECT {+INDEX(table_name,"index_name")} stuff FROM table blah blah blah See the documentation on Optimizer Directives. This is new to version 7.3. You should be able to modify legacy apps to make use of this, because their parsers will view the directive as a comment and pass it along to the engine without complaint. Richard Auslander <rich@airflash.com> wrote in message news:3702BC94.FEE1A5A0@airflash.com... > Mark - How IS a particular index selected using v7.30 of Informix? I'm new to > this release and don't konw the latest features. > > Rich > > "Mark D. Stock" wrote: > > > Alan Wong wrote: > > > > > > I have a problem, ...I am using Informix 4GL version 4.20 on Online 5.10 > > > UC1. > > > > > > My application have to query for a record in a table which have 2 composite > > > indexes on it, say .. > > > > > > Table bonus ( > > > member_id integer, > > > promo_id integer, > > > promo_type char(1), > > > bonus_type char(5), > > > balance dec(16,2)) > > > with indexes > > > i_1 on bonus (member_id, bonus_type) > > > i_2 on bonus (promo_id, promo_type) > > > > > > On querying for a record using member_id, promo_id, promo_type, bonus_type > > > in the WHERE clause, with SET EXPLAIN ON, it shows index i_2 is used. This > > > index gives a set of about 20,000 record to search on. > > > > > > At other times, the same query uses index i_1, which gives a set of 5 > > > records !! The better index to use. > > > > > > Unfortunately, I cannot do without index i_2. So how can I force a SELECT > > > to use index i_1 in its query plan instead of i_2 ??? > > > > In simple terms, you can't until version 7.30. > > > > When did you last run UPDATE STATISTICS? > > > > Otherwise it might help to see the results of SET EXPLAIN for both types > > of SELECT. > > > > Cheers, > > -- > > Mark. > > > > +----------------------------------------------------------+-----------+ > > |Mark D. Stock - Informix SA http://www.informix.com |//////// /| > > |mailto:mdstock@informix.com http://www.informix.com/idn |///// / file://| > > |http://www.iiug.org +-----------------------------------+//// / ///| > > | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| > > | Fax: +27 838250 2325 |If it's fast, the users keep quiet.|// / /////| > > |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| > > +----------------------+-----------------------------------+-----------+ > > -- > Richard C. Auslander > Database Manager > > AirFlash, Inc. > 1733 Woodside Rd., Suite #110 > Redwood City, CA 94061 > (650) 556-7928 > > www.airflash.com > >

Glossary