Home     |     Java    |     Php General    |     Oracle Database    |     Oracle Server  

MS Dynamics CRM 3.0

  •  Setting up and Configuring Microsoft Dynamics CRM 3.0
  •  Managing Security and Information Access
  •  Entity Customization: Concepts and Attributes
  •  Entity Customization: Forms and Views
  •  Entity Customization: Relationships, Custom Entities, and Site Map
  •  Reporting and Analysis
  •  Workflow
  •  Server-Side SDK
  •  Client-Side SDK
  •  Integration with External Applications

    • Cervo Technologies
    The Right Source to Outsource

    Sharepoint Portal Server KB

    Microsoft CRM Info

    WPF Interview Questions

    SilverLight Interview Qs

    Asp.Net 2.0 Interview Qs

    Asp.NET 1.1 FAQs

    Oracle Interview Questions

    SAP Interview Questions

    Oracle Database

    IOT seek vs index seek - index sizes and widths.


    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

    -----------------------------------------------Reply-----------------------------------------------

    On 24 Apr, 10:27, sybrandb <sybra@gmail.com> wrote:

    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

    -----------------------------------------------Reply-----------------------------------------------

    On Apr 24, 11:47 am, phancey <d@2bytes.co.uk> wrote:

    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

    -----------------------------------------------Reply-----------------------------------------------

    On 24.04.2007 11:47, phancey wrote:

    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

    -----------------------------------------------Reply-----------------------------------------------

    On 24 Apr, 11:35, sybrandb <sybra@gmail.com> wrote:

    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.

    -----------------------------------------------Reply-----------------------------------------------

    On 24 Apr, 11:52, Robert Klemme <shortcut@googlemail.com> wrote:

    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.

    -----------------------------------------------Reply-----------------------------------------------

    On 24.04.2007 13:28, phancey wrote:

    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

    -----------------------------------------------Reply-----------------------------------------------

    On Apr 24, 2:56 pm, phancey <d@2bytes.co.uk> wrote:

    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

    -----------------------------------------------Reply-----------------------------------------------

    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
    damor@x.washington.edu
    (replace x with u to respond)
    Puget Sound Oracle Users Group
    www.psoug.org

    -----------------------------------------------Reply-----------------------------------------------

    On 24 Apr 2007 04:28:13 -0700, phancey <d@2bytes.co.uk> wrote:

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

    Just for your information: I suffer EVERY DAY from this approach.
    There are too many developers who think Oracle is just sqlserver sold
    by a different vendor, and this is why they 'port' their code, without
    adapting it. If I must guess who you are going to blame when disaster
    strikes, I KNOW it is the POOR Oracle DBA, who can't do much about it,
    except throw the application out of the window.

    So realistically, knowing you can't tweak Oracle to behave like
    sqlserver, you either write a proper API, or you start all over again.

    You are doomed to failed, DOOMED (Frazer in 'Dad's Army')

    --
    Sybrand Bakker
    Senior Oracle DBA

    -----------------------------------------------Reply-----------------------------------------------

    On Apr 24, 4:28 am, phancey <d@2bytes.co.uk> wrote:

    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

    Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc