• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Column order in indexes

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Column order in indexes

    I've a select query that brings back matching records for a specific document number and company code. There's no index containing these, so I'm creating one. There are ~40 company codes in the system and many thousands of document numbers per company code.

    Is there a preferred order for the index? I.e. should I used CoCode, DocNum or DocNum CoCode? Or does it not matter?

    (Oracle 11.2)
    Down with racism. Long live miscegenation!

    #2
    Originally posted by NotAllThere View Post
    I've a select query that brings back matching records for a specific document number and company code. There's no index containing these, so I'm creating one. There are ~40 company codes in the system and many thousands of document numbers per company code.

    Is there a preferred order for the index? I.e. should I used CoCode, DocNum or DocNum CoCode? Or does it not matter?

    (Oracle 11.2)
    probably wisest to have a bitmap index on cocode and a separate index on docnum.
    merely at clientco for the entertainment

    Comment


      #3
      The order of a compound index definetly has an impact.

      Best thing to do would be to create a compound index in both orders, cocode/docnum and docnum/cocode.

      If you have to choose one (for space concerns or write performance), go with the column with the most distinct values first (DocNum) as this will reduce the top level cardinality.

      Edit - check index organised tables too - you could organise on the company code depending on the use case.

      http://asktom.oracle.com/pls/asktom/...D:627128171401
      Last edited by Kanye; 29 October 2012, 11:06.

      Comment


        #4
        Unfortunately, all of this is being filtered through a SAP layer, so I don't have control over the type of index . The purpose the index is to improve read access - write access can take a running jump (it's BW).

        My instinct was to go for docnum first.
        Down with racism. Long live miscegenation!

        Comment


          #5
          Originally posted by NotAllThere View Post
          Unfortunately, all of this is being filtered through a SAP layer, so I don't have control over the type of index . The purpose the index is to improve read access - write access can take a running jump (it's BW).

          My instinct was to go for docnum first.
          docnum would be the best option then. An index on cocode may fall apart based on the distribution of documents across companies.
          merely at clientco for the entertainment

          Comment


            #6
            Thanks. I'll give it a go.
            Down with racism. Long live miscegenation!

            Comment

            Working...
            X