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

On 24 Apr, 11:52, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> 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

thanks robert,

trouble is when the historic application code is tightly coupled to the data layer (and no code changes are allowed), it restricts what is possible. Also, you don't really want to be starting from scratch having optimized one setup. Realistically, you are not going to entirely ignore what you have spent time doing on one RDBMS as though it has no place in the other. It becomes your starting point and then you tweak it to optimize it for the second. And that is exactly the process I am asking for help with, but some people just get on their high horses and condemn anything that isn't Oracle. Actually what probably annoys them is that I started with SQL Server and did it this way round. Plus the fact they've invested so much time and money in Oracle and always been told it's way better, that they can't actually open their minds to any other possibility. Received on Tue Apr 24 2007 - 06:28:13 CDT