2007/5/23, Becker, Holger <[EMAIL PROTECTED]>:
Robert Klemme wrote:
> 2007/5/23, Becker, Holger <[EMAIL PROTECTED]>:
> > Daniel Castro wrote:
> >
> > > Hi,
> > >
> > > I have some queries doing a SCAN. I identify then and I
> > > create a index.
> > >
> > > For example:
> > >
> > > explain SELECT PSV.PSV_COD , PSV.PSV_APEL ,
> > > PSV.PSV_TRAT , PSV.PSV_TIPO FROM PSV WHERE (
> > > PSV.PSV_TIPO<> 'I' ) ORDER BY
> > > PSV.PSV_APEL ASC
> > >
> > > Results:
> > > DBA PSV TABLE SCAN 1019
> > > DBA RESULT IS NOT COPIED , COSTVALUE IS 1019
> > >
> > > I created a index on PSV.PSV_TIPO <> 'I' but the query is
> > > scanning the
> > > table again.
> > > After the index xreatino I have to do some other thing?
> > >
> > > Best regards,
> >
> > Hi,
> >
> > there is nothing to do after index creation.
> > The index would be used if the optimizer would estimate
> less I/O costs
> > for the possible index strategy.
> > In your case the scan seems to be less expensive than the
> index access.
> > This would be the case if the condition PSV.PSV_TIPO <> 'I' is not
> > very selective.
>
> Which is usually the case for *exclusions*. Also, that an index seek
> can usually be done only for equality (i.e. col = 1) - for all others
> typically scans of part or the whole index occur.
>
> Kind regards
>
> robert
>
Hi,
only one small remark on Roberts posting.
MaxDB optimizer transforms conditions like col <> 1 on index or key columns to (col
> 1 or col < 1).
So the optimizer could use two different strategies for each part which could
be better than the scan.
Holger, thanks for the insight!
Daniel, do you have statistics up to date?
robert
--
Have a look: http://www.flickr.com/photos/fussel-foto/
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
