Re: [LIKELY JUNK]After index creation

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

-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[email protected]