Column order in indexes

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!

You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:

You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

NotAllThere replied

29 October 2012, 18:23
Thanks. I'll give it a go.
Leave a comment:
eek replied

29 October 2012, 14:07
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.
Leave a comment:
NotAllThere replied

29 October 2012, 13:49
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.
Leave a comment:
Kanye replied

29 October 2012, 11:01
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.
Leave a comment:
eek replied

29 October 2012, 10:17
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.
Leave a comment:
NotAllThere started a topic Column order in indexes

29 October 2012, 09:16
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)
Tags: None