Re: Online Dynamic Server 7.3 : Index not used!

Because you are selecting with "!=" and "like". In this case it normally don't use index. Alkesh Vipani. ----- Original Message ----- From: "David Van Wallendael" <David.Van.Wallendael@irislink.com> To: <informix-list@iiug.org> Sent: Monday, February 14, 2000 10:21 AM Subject: Re: Online Dynamic Server 7.3 : Index not used! > Hi, > > I've drop et re-create the index, > I've run oncheck -cI -y, > I've run "update statististics" > but with no succes! > > Any other idea ? > > David Van Wallendael > > > Michael Krzepkowski wrote: > > > HI! > > > > Have you run "update statistics" ? > > > > You can also force 7.3 to use index with the directives in SELECT > > statement. > > > > HTH > > > > Michael > > > > David Van Wallendael wrote: > > > > > Hello Everybody! > > > > > > I've recently migrated my unix server with the informix online 5.0 to > > > informix dynamic server 7.3. My problem is this : When i make a simple > > > query on a table, the index are not used > > > > > > Here's my table "fiche_ft" with the index : > > > > > > Column name Type Nulls > > > > > > fi_nume integer yes > > > fi_zone char(3) yes > > > fi_mot varchar(50,0) yes > > > > > > Index name Owner Type Cluster Columns > > > > > > ft1 informix dupls No fi_nume > > > > > > ft2 informix dupls No fi_mot > > > > > > ft3 informix dupls No fi_zone > > > > > > Here's the file sqexplain.out : > > > =================== > > > > > > QUERY: > > > ------ > > > SELECT ft.fi_nume FROM fiche_ft ft > > > WHERE fi_zone != '4a' AND fi_mot LIKE 'ethos%'> > > > > > Estimated Cost: 8888 > > > Estimated # of Rows Returned: 44593 > > > > > > 1) informix.ft: SEQUENTIAL SCAN > > > > > > Filters: (informix.ft.fi_zone != '4a' AND informix.ft.fi_mot LIKE > > > 'ethos%' > > > > > > As you can see , the index are not used... > > > > > > Did someone had already the same problem ? > > > Did someone has an idea how to force the use of index ? > > > > > > thanks, > > > > > > David Van Wallendael > > > www.irislink.com >

This is not true. Indexes are used for LIKE if the matching string does not start with a wildcard. Besides the UPDATE STATS issue: - How many rows are in the table? - How many fi_mot values actually DO match 'ethos%' If too many fi_mot values match 'ethos%', it will not use the index. June -- june_t@hotmail.com Living on Starbucks Cafe Mocha. Alkesh Vipani wrote: > > Because you are selecting with "!=" and "like". In this case it normally > don't use index. > Alkesh Vipani. > ----- Original Message ----- > From: "David Van Wallendael" <David.Van.Wallendael@irislink.com> > To: <informix-list@iiug.org> > Sent: Monday, February 14, 2000 10:21 AM > Subject: Re: Online Dynamic Server 7.3 : Index not used! > > > Hi, > > > > I've drop et re-create the index, > > I've run oncheck -cI -y, > > I've run "update statististics" > > but with no succes! > > > > Any other idea ? > > > > David Van Wallendael > > > > > > Michael Krzepkowski wrote: > > > > > HI! > > > > > > Have you run "update statistics" ? > > > > > > You can also force 7.3 to use index with the directives in SELECT > > > statement. > > > > > > HTH > > > > > > Michael > > > > > > David Van Wallendael wrote: > > > > > > > Hello Everybody! > > > > > > > > I've recently migrated my unix server with the informix online 5.0 to > > > > informix dynamic server 7.3. My problem is this : When i make a simple > > > > query on a table, the index are not used > > > > > > > > Here's my table "fiche_ft" with the index : > > > > > > > > Column name Type Nulls > > > > > > > > fi_nume integer yes > > > > fi_zone char(3) yes > > > > fi_mot varchar(50,0) yes > > > > > > > > Index name Owner Type Cluster Columns > > > > > > > > ft1 informix dupls No fi_nume > > > > > > > > ft2 informix dupls No fi_mot > > > > > > > > ft3 informix dupls No fi_zone > > > > > > > > Here's the file sqexplain.out : > > > > =================== > > > > > > > > QUERY: > > > > ------ > > > > SELECT ft.fi_nume FROM fiche_ft ft > > > > WHERE fi_zone != '4a' AND fi_mot LIKE 'ethos%'> > > > > > > > Estimated Cost: 8888 > > > > Estimated # of Rows Returned: 44593 > > > > > > > > 1) informix.ft: SEQUENTIAL SCAN > > > > > > > > Filters: (informix.ft.fi_zone != '4a' AND informix.ft.fi_mot LIKE > > > > 'ethos%' > > > > > > > > As you can see , the index are not used... > > > > > > > > Did someone had already the same problem ? > > > > Did someone has an idea how to force the use of index ? > > > > > > > > thanks, > > > > > > > > David Van Wallendael > > > > www.irislink.com > >