In the past I have always create indexes on single fields that were going to be part of my WHERE clause, except for fields like boolean or with little varitions in values.

I was recently on a beginners course with Oracle and the instructor spent a bit of time talking about indexes and b-tree and was quite adamant that if you had a primary key made up of multiple fields or regularly did searches on a group of fields that to get the best results you should create a compound index using those field and then use that expression in your where clause.

With that in mind, say I have a table with Customer,Account,Invoice Date. In the past I would have created individual indexes on these fields and then used a where clause like:

 Customer = 'MyCustomer'
 AND Account = 1234
 AND Invoice Date = {01/02/2009}

However if I go by the instructors advice I would be better served with compound index, in the same order, with a where like:

Customer+Account+Invoice Date =  'MyCustomer 1234 {01/02/2009}'

I know the above isn't syntactically correct but you know what I mean. The way the instructor explained it, it did make sense.

So assuming I have a table where 90% of my queries will be on all three fields, another 5% be on customer+account and maybe a further 3% on just customer, my questions are:

1)Was the instructor taking rubbish in terms of Oracle - I am very new to Oracle .

2)Does this index strategy also work in VFP or does the Rushmore technology make the individual field approach more efficient. I am looking more towards efficiency than flexibility these days

3)How about with SQL Server?

I do understand that these type of things are different depending on the size of the tables and various other criteria and that one must fully test for all alternatives but I am just looking for some guidelines

Thanks much,

Steve Dingle
http://weblogs.foxite.com/SteveDingle/
www.dsbusinesssolutions.co.uk