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

On Apr 24, 10:52 am, phancey <d...@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.

--
Sybrand Bakker
Senior Oracle DBA

On 24 Apr, 10:27, sybrandb <sybra...@gmail.com> wrote:
> On Apr 24, 10:52 am, phancey <d...@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.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

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

On Apr 24, 11:47 am, phancey <d...@2bytes.co.uk> wrote:
> On 24 Apr, 10:27, sybrandb <sybra...@gmail.com> wrote:
>
>
>
>
>
> > On Apr 24, 10:52 am, phancey <d...@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.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> 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 force Oracle to behave
like sqlserver. This would be identical to converting a Ferrari in a T-
Ford.
I would URGE you to LEARN Oracle and stop whining 'sqlserver is
better'. It isn't.
And it will NEVER be.


--
Sybrand Bakker
Senior Oracle DBA

On 24.04.2007 11:47, phancey wrote:
> On 24 Apr, 10:27, sybrandb <sybra...@gmail.com> wrote:
>> On Apr 24, 10:52 am, phancey <d...@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

On 24 Apr, 11:35, sybrandb <sybra...@gmail.com> wrote:
> On Apr 24, 11:47 am, phancey <d...@2bytes.co.uk> wrote:
>
>
>
> > On 24 Apr, 10:27, sybrandb <sybra...@gmail.com> wrote:
>
> > > On Apr 24, 10:52 am, phancey <d...@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.

On 24 Apr, 11:52, Robert Klemme <shortcut...@googlemail.com> wrote:
> On 24.04.2007 11:47, phancey wrote:
>
>
>
> > On 24 Apr, 10:27, sybrandb <sybra...@gmail.com> wrote:
> >> On Apr 24, 10:52 am, phancey <d...@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.

On 24.04.2007 13:28, phancey wrote:
> On 24 Apr, 11:52, Robert Klemme <shortcut...@googlemail.com> wrote:
>> On 24.04.2007 11:47, phancey wrote:
>>
>>
>>
>>> On 24 Apr, 10:27, sybrandb <sybra...@gmail.com> wrote:
>>>> On Apr 24, 10:52 am, phancey <d...@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.
>
> 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.

Your application code should only rely on the logical schema. If the
application code in some way restricts physical DB design (indexes,
table partitioning, tablespaces etc.) you likely have a big problem and
you should change that.

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

While that may be true to a certain extent with indexes (i.e. an index
on certain columns that are frequently used in WHERE clauses is a likely
candidate to be created on the other system as well) you still need to
be very careful. Basically you need to test and verify every single
assumption that index X is going to improve performance.

Kind regards

robert

On Apr 24, 2:56 pm, phancey <d...@2bytes.co.uk> wrote:
> On 24 Apr, 11:35, sybrandb <sybra...@gmail.com> wrote:
>
>
>
> > On Apr 24, 11:47 am, phancey <d...@2bytes.co.uk> wrote:
>
> > > On 24 Apr, 10:27, sybrandb <sybra...@gmail.com> wrote:
>
> > > > On Apr 24, 10:52 am, phancey <d...@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

phancey wrote:

> You don't attract anyone to Oracle with that kind of attitude.

Not to make light of your statement as my intent is not to
excuse any lack of civility but we are technologists volunteering
our time ... not marketing or sales people. And not one of us,
AFAIK, works for Oracle, makes a penny when Oracle sells a product,
or cares about attracting anyone.

That said I think Oracle substantially superior in almost every
respect to SQL Server beginning with the choice of operating
systems and the underlying concepts and architecture.

What you are essentially hearing is that those underlying concepts
and architecture are so different that any attempt to tune them
using the thought processes is essentially doomed to failure. You
need to treat them as totally independent in almost every respect.

And the only real answer to your original questions is to build
the indexes and test real-world performance. Explain Plan in Oracle
is an indication of what may happen and how the optimizer views
what may happen. It also may not happen and it may not be the
actual path. There is no substitute for testing.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org