Re: Online Dynamic Server 7.3 : Index not used!
try
select {+INDEX(fiche_ft index_name)} ft.fi_nume FROM fiche_ft ft
WHERE fi_zone != '4a' AND fi_mot LIKE 'ethos%';
with optimizer directive INDEX it should work fine.
Alkesh Vipani.
----- Original Message -----
From: "Alkesh Vipani" <alkesh.vipani@wcom.com>
To: "David Van Wallendael" <David.Van.Wallendael@irislink.com>
Cc: <informix-list@iiug.org>
Sent: Monday, February 14, 2000 12:13 PM
Subject: 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
> >
>