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

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

It's not so much we're on high horses, as we've seen over and over people try to force Oracle to do things the way other db's do it, without understanding the basic underlying architectural differences. As Robert implied, it is a mistake to use the other RDBMS as a starting point, the optimizations will be different for many functions. I'd recommend the books by Tom Kyte as a good starting point to understand these architectural differences.

Personally, I've worked on several relational db's going back to 1980, and my experience has been that the Oracle architecture is well adapted to the issues of transactions in the sort of concurrent environments business have. The other databases have forced much code into the app layer that should be in the database trying to work around those issues. Your mileage may vary.

As far as worrying about the width of an index using up memory, you may be worrying too much. If you are scanning a significant part of the index, and the index is in the hundreds of megabytes, you may not be worrying too much. It depends. (There are also other things you can do, based on whether you will generally be scanning large parts of the index or table). Vladimir pointed out that with Oracle's way, you can have the narrow index by putting the rest of the columns in the overflow area. Of course, with a normal table and a normal index, that would be the same thing, as you might be losing the primary benefit of IOT, not having to get an index block and then a data (or overflow) block. Which brings us back to designing the table for performance...

jg

--
@home.com is bogus.
http://photo.humuhumu.com/v/jackchicktiki/P1060086-1.jpg.html