+ Reply to Thread
Results 1 to 5 of 5

Multi-column indexes vs Single Column Indexes

  1. Multi-column indexes vs Single Column Indexes

    I am using Orale9i and Oracle 10g on Windows and UNIX boxes.
    When creating new indexes which are non-unique, what are the
    pros and cons of creating a single index of multiple columns vs
    creating a separate index on each column.

    For example, if I created separate indexes: one on col1 and other
    on col2,
    Oracle could use these indexes if either col1 or col2 was used in
    queries.
    However if I had an index on multiple columns (co1, col2), Oracle will
    not use
    Index if query used only col2. I know in Oracle9i, Oracle can use
    parts of multi-column index for col2, but this is not as efficient as
    having a separate index on col2. Disk space is not an issue for me, so
    I will be better off using a separate index for each column.


    Thanks


  2. Re: Multi-column indexes vs Single Column Indexes

    zigzagdna@yahoo.com wrote:
    > I am using Orale9i and Oracle 10g on Windows and UNIX boxes.
    > When creating new indexes which are non-unique, what are the
    > pros and cons of creating a single index of multiple columns vs
    > creating a separate index on each column.
    >
    > For example, if I created separate indexes: one on col1 and other
    > on col2,
    > Oracle could use these indexes if either col1 or col2 was used in
    > queries.


    And if both col1 and col2 are both used in a query, then it quite likely
    you will benefit from only use of those indices even when the joint of the
    two is highly selective compared to either one separately. That is the
    main benefit of an index on multiple columns--you get the join selectivity
    instead of just one or the other.


    > However if I had an index on multiple columns (co1, col2), Oracle will
    > not use
    > Index if query used only col2. I know in Oracle9i, Oracle can use
    > parts of multi-column index for col2, but this is not as efficient as
    > having a separate index on col2. Disk space is not an issue for me, so
    > I will be better off using a separate index for each column.


    If you will use both col1 and col2 equalities in the where clause of the
    same query, it might be better to make one index on (col1,col2) and one on
    col2). Or maybe one on (col2,col1) and one on (col1), depending on the
    data and the uses to which you put it.

    Obviously these are just generalities.

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB

  3. Re: Multi-column indexes vs Single Column Indexes

    On Aug 12, 11:50 am, zigzag...@yahoo.com wrote:
    > I am using Orale9i and Oracle 10g on Windows and UNIX boxes.
    > When creating new indexes which are non-unique, what are the
    > pros and cons of creating a single index of multiple columns vs
    > creating a separate index on each column.
    >
    > For example, if I created separate indexes: one on col1 and other
    > on col2,
    > Oracle could use these indexes if either col1 or col2 was used in
    > queries.
    > However if I had an index on multiple columns (co1, col2), Oracle will
    > not use
    > Index if query used only col2. I know in Oracle9i, Oracle can use
    > parts of multi-column index for col2, but this is not as efficient as
    > having a separate index on col2. Disk space is not an issue for me, so
    > I will be better off using a separate index for each column.
    >
    > Thanks


    Every index means extra work when you do DML on the table. You have to
    weigh these things up. Skip-scanning of indexes means reasonably
    efficient access to the Column2 of the single index, but only one
    index to maintain. Separate indexes mean (perhaps) single block access
    to the index, but multiple indexes to maintain. Two lots of redo, two
    lots of undo, two lots of latching and locking...

    It's what performance tuning is all about, really...


  4. Re: Multi-column indexes vs Single Column Indexes

    wrote in message
    news:1186887182.684865.249580@g12g2000prg.googlegroups.com...
    > On Aug 12, 11:50 am, zigzag...@yahoo.com wrote:
    >> I am using Orale9i and Oracle 10g on Windows and UNIX boxes.
    >> When creating new indexes which are non-unique, what are the
    >> pros and cons of creating a single index of multiple columns vs
    >> creating a separate index on each column.
    >>
    >> For example, if I created separate indexes: one on col1 and other
    >> on col2,
    >> Oracle could use these indexes if either col1 or col2 was used in
    >> queries.
    >> However if I had an index on multiple columns (co1, col2), Oracle will
    >> not use
    >> Index if query used only col2. I know in Oracle9i, Oracle can use
    >> parts of multi-column index for col2, but this is not as efficient as
    >> having a separate index on col2. Disk space is not an issue for me, so
    >> I will be better off using a separate index for each column.
    >>
    >> Thanks

    >
    > Every index means extra work when you do DML on the table. You have to
    > weigh these things up. Skip-scanning of indexes means reasonably
    > efficient access to the Column2 of the single index, but only one
    > index to maintain. Separate indexes mean (perhaps) single block access
    > to the index, but multiple indexes to maintain. Two lots of redo, two
    > lots of undo, two lots of latching and locking...
    >
    > It's what performance tuning is all about, really...


    Then again, having only one index to maintain doesn't necessarily mean
    skip-scanning will occur at all as it requires the cardinality of the
    leading column to be somewhat greater than two times the average number of
    rows referenced per index leaf page for it to be even considered by the CBO.
    In many cases, this simply rules out skip-scanning as a viable option.

    Although I agree more indexes makes subsequent DML less efficient, the
    general guideline is that a row is inserted once, is generally updated
    rarely if at all, is deleted rarely or once if at all but can be queried
    continually for years and years. Therefore if an index means business
    functionality can be resolved instantaneously or dramatically quicker than
    without the index, then the justification of having whatever indexes are
    necessary to satisfy business requirements usually overwrite the (generally)
    unnoticed overheads from the business perspective of maintaining such
    indexes.

    Cheers

    Richard



  5. Re: Multi-column indexes vs Single Column Indexes

    zigzagdna@yahoo.com wrote:
    > I am using Orale9i and Oracle 10g on Windows and UNIX boxes.
    > When creating new indexes which are non-unique, what are the
    > pros and cons of creating a single index of multiple columns vs
    > creating a separate index on each column.
    >
    > For example, if I created separate indexes: one on col1 and other
    > on col2,
    > Oracle could use these indexes if either col1 or col2 was used in
    > queries.
    > However if I had an index on multiple columns (co1, col2), Oracle will
    > not use
    > Index if query used only col2. I know in Oracle9i, Oracle can use
    > parts of multi-column index for col2, but this is not as efficient as
    > having a separate index on col2. Disk space is not an issue for me, so
    > I will be better off using a separate index for each column.
    >
    >
    > Thanks


    You've had responses from two of the best in the business, Howard
    Rogers and Richard Foote, but their both in Australia so likely asleep
    now, moving me to throw in a slightly different, though perhaps less
    academic, response.

    There is no single answer to your question ... no magic bullet ... the
    only way to answer your question is to build both types and test how
    Oracle will use them with your data and your optimizer settings (such as
    optimizer_index_cost_adj and optimizer_index_caching).

    If it is a production system that will not allow you to create and drop
    indexes at will; at least in 10g you can use nosegment indexes. There
    is a demo on this at: http://www.psoug.org/reference/indexes.html#ixns.
    The cost of building a nosegment index, for all practical purposes is zero.
    --
    Daniel A. Morgan
    University of Washington
    damorgan@x.washington.edu (replace x with u to respond)
    Puget Sound Oracle Users Group
    www.psoug.org

+ Reply to Thread