Re: IDS 7.30.UC7 on HP-UX 10.20 ignores RESIDENT settings

Posted in 1999

David Williams — Mon, 5 Apr 1999 22:38:53 +0100 — source: Informix-list mailing list archive (1991-1998)

In article <044CD796C702D111B56800608CCC51D00DA269@INT_04>, Richard Auslander <rich@airflash.com> writes >> > >> > 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 ??? >> Don't instead alter index i_2 on be on bonus(promo_id,promo_type,member_id,bonus_type). It will there for be used when searching on just promo_id and promo_type and also used for this query... >> 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 > > -- David Williams