How does Access use Indexes? 1

[BNPMike]

My understanding is if you have a usable index, Access will only fetch the index pages, locate the target records and then fetch the data pages associated with the target records, thus cutting down the need to fetch entire tablespaces.

However my question is how does it manage to retrieve individual pages if the database is just a single file - mydatbase.mdb? Does Windows allow you to access bits of files?

As a matter of interest, does SQLite do things in the same sort of way?

 

[papadba]

Does Windows allow you to access bits of files?

All database systems have an internal mechanism to directly read parts of a file (usually known as pages in a database environment). This is the way it works on all 3 main platforms (Win-based, UNIX, and Mainframes).

The internal structure of the underlying database files and the algorythms used to manage them are most often proprietary.

 

[jsteph]

BNPMike,
If you think about it--you might have 100 tables in that single .mdb file--and Access selectively accesses those doesn't it?

So think of an index as just another table. For example with a single-field index this 'table' would have two fields--one containing the field value itself and the other a row ID (or logical file position pointers) of the row where the data with that value starts.
--Jim