Hi, folks...
A question about the significance of the physical order of large DBF files.
Our application has several large (1 million + records) historical files. Typically, the key is Account + Item + Date. We have tags on acct+item, and on date.
Most of the joins to get historical data for reporting is acct = acct, item = item, and date between
and .
Some of our databases are built so that the physical order of the database is by acct, item, and date. Others are built so that the physical order is purely by date.
Typical queries using the former go very quickly. The same query against the latter goes VERY slowly.
I've already reproduced this in isolation, but was wondering if anyone immediately had any additional 'supporting information' to back this, as I'm putting together an internal memo to our database group.
Thanks in advance,
Kevin