Re: IOT seek vs index seek - index sizes and widths.

On 24 Apr, 10:27, sybrandb <sybra..._at_gmail.com> wrote:
> On Apr 24, 10:52 am, phancey <d..._at_2bytes.co.uk> wrote:
>
> > If I only want to select 2 columns out of 5 for example, and those 2
> > columns are the primary key on an IOT, is it possible or even
> > worthwhile to index them separately from the IOT? The IOT would be a
> > much wider index and therefore require more memory? If the 2 columns
> > are likely to be in physical order anyway, wouldn't the normal index
> > perform better?
>
> > In SQL Server I have been able to create a clustered index and a
> > normal index on the same 2 columns. The optimiser chooses the normal
> > index when selecting just those 2 columns. But it seems Oracle won't
> > let me define the same 2 columns(in the same order) twice.
>
> > Phil
>
> It is not worthwhile as the optimizer would automagically select the
> index and the index only.
> Why would the IOT be a much wider index?
> Why would a 'normal' index perform better.
> Why would you need to create 2 indexes on identical columns? If that
> can be done in sqlserver, sqlserver is an even bigger piece of crap
> than I already thought.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

SQL Server shows better performance in the Explain Plan because IO Cost is reduced on the normal index (the IOT is effectively an index on the whole table and therefore covers all the columns whereas the normal index only covers the 2 specific columns, so it's narrower isn't it?)

Yes it can be done in SQL Server, and seems to improve performance (over the same database with the same data only having the IOT, or clustered index as it is known in SS)

So, given it only wants to select the 2 columns, would it not be quicker to select them from the normal index over the clustered? (more so the wider the table)

Phil Received on Tue Apr 24 2007 - 04:47:59 CDT