> 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

Hello,

And in foxpro .. many times the combined index need some tricks to be sure that the order will be correct. So, I think that the compound index of your sample better be with this expression:

CAST(customer as c (<numeric - customer field width>))+;
PADL(Account,<numeric - account field width>,"0")+DTOC(inv_Date,1)

Best regards

Ammar Hadi ................IRAQ

My Foxite Weblog

The Most Beautiful View ... is the one I Share with You.
--------------------------------------