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

On 24 Apr, 11:35, sybrandb <sybra..._at_gmail.com> wrote:
> 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 seemsOraclewon'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
> > > SeniorOracleDBA
>
> > 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 forceOracleto behave
> like sqlserver. This would be identical to converting a Ferrari in a T-
> Ford.
> I would URGE you to LEARNOracleand stop whining 'sqlserver is
> better'. It isn't.
> And it will NEVER be.
>
> --
> Sybrand Bakker
> SeniorOracleDBA

what is it with Oracle DBAs - why the aggression? I never said SQL Server was better, I never said I was trying to get it to behave like it. I was simply questioning WHY it could not have 2 indexes the same. You don't answer that except to say it's a good thing - WHY? Having asked originally why the IOT index is wider, why the narrower index performs better, and having got 2 reasonable answers from me you just go and ignore them and imply that I'm dumb. Frankly this doesn't make you appear any more intelligent.

I understand the OVERFLOW and INCLUDING clauses means there may not be a huge difference but nevertheless it appears that an IOT index is wider and can therefore be less efficient when selecting just the unique columns - is that not a logical conclusion?

You don't attract anyone to Oracle with that kind of attitude. Received on Tue Apr 24 2007 - 05:56:45 CDT