Re: [LIKELY JUNK]After index creation

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]