On 24.04.2007 13:28, phancey 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.
>
> 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
Received on Tue Apr 24 2007 - 07:35:34 CDT
