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

On Apr 24, 11:47 am, phancey <d..._at_2bytes.co.uk> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

First of all: You can't put multiple indices on identical columns. And actually : that is GOOD.
So, if you want to select them from a 'normal' index: if your table is an IOT: there won't be one. If you insist on 'normal' indices, convert your table to a 'normal' heap table.
6 million isn't big nowadays. If you are complaining about 6 million rows, you probably better concentrate on other bigger problems.

Furthermore: I would URGE you to stop trying to force Oracle to behave like sqlserver. This would be identical to converting a Ferrari in a T-

I would URGE you to LEARN Oracle and stop whining 'sqlserver is better'. It isn't.
And it will NEVER be.

--
Sybrand Bakker
Senior Oracle DBA