> 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.ukSteve,
There are enormous differences between the way that VFP handles indexes and the way server databases do. You really cannot extrapolate from one to the other. Apart from anything else indexes in Database Servers are ONLY for optimizing queries - you don't (and can't) use an index to sort data for display.
[1] I doubt that the instructor was talking rubbish. I also doubt that he/she said 'create a compound index' because as far as I know you cannot do that in Oracle (or SQL Server for that matter). What may have been said was to create a single index that contains multiple columns in the order in which you use them in queries.
[2] No, - at least, you cannot create compound indexes without concatenating columns, which means converting them to the same data type. In SQL you can define an index as:
CREATE INDEX acct_sch ON ACCOUNT ( customer, account, invoicedate)
and it would be usable for single, or multiple, column based queries. But in VFP you would have to build a compound key like:
INDEX ON customer+STR(account)+DTOS(invoicedate) TAG acct_sch
and it would only be usable with concatenated strings or partial strings. So in VFP you are probably better off to stick to single column tags in your indexes (i.e. build a cdx with three tags, one each for customer, account and date)
[3] SQL Server behaves much like Oracle.
Regards
Andy Kramek
Microsoft MVP (Visual FoxPro)
Tightline Computers Inc, Akron Ohio, USA