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

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Tue, 24 Apr 2007 12:52:54 +0200
Message-ID: <5965rnF2ien71U2@mid.individual.net>

On 24.04.2007 11:47, phancey 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.

>
> 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)

There are no clustered indexes in Oracle. While I am not sure about the "Oracle will always be better than SQL Server" piece I certainly strongly agree with Sybrand that you need to tune both databases completely independently. It is a plain waste of time to figure out an optimal setup on one product and try to "copy" that to the other - regardless of direction.

        robert Received on Tue Apr 24 2007 - 05:52:54 CDT