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

On Apr 24, 2:56 pm, phancey <d..._at_2bytes.co.uk> wrote:
> 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.

Ok, since you know what OVERFLOW and INCLUDING do to the IOT storage, you understand that you can keep those two columns in the index part of the table and throw the rest into the overflow, right? If you only need the key columns, Oracle will scan the index and won't even touch the overflow. Otherwise it will scan the index and then fetch remaining non-key columns from the overflow by rowid stored in the index. How does this differ from SS's performance with clustered index + normal index on PK in both scenarios? I doubt it differs much, though fetching full rows may look like a bit more expensive in Oracle since more I/O will be required. SS's approach is more expensive at maintenance though - it will need to update both indexes on DML involving key columns, while Oracle only has single structure where they are stored. And don't forget extra space SS will have to allocate for the secondary index (though it's not that important these days when disk space is nearly literally dirt-cheap :))

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue Apr 24 2007 - 07:51:24 CDT