General discussion

Locked

File organization in Access & Oracle.

By d_hainline ·
Am I right in believing that files are organized in Access and Oracle as B-trees, and are therefore not necessarily in physical sequential order after updates? (That is, if I add new records whose keys are "in between" others, these records may be held in blocks/pages which are not "in between" those of their predecessors/successors. (Hope this is clear!)

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

File organization in Access & Oracle.

by Bob Sellman In reply to File organization in Acce ...

I'm going to assume that when you say "files" you actually mean data records added to tables.

Data records may or may not be in physical sequential order as far as where they are stored. Oracle manages space available for deleted records within each tablespace, so that you don't have to run a procedure comparable to the Access "compact" operation (under Tools, Database Utilities). When you delete records in Access it creates holes in the database file that are never used and can slow down the database. This is the reason the compact operation needs to be run periodically, depending upon the deletion activity in the Access database. Oracle does the management of space from deleted records itself because each file used for the database is a predefined size and the DBA must add a new file when the database begins to fill up.

The indexes for tables contain pointers to the physical record locations based on the order dictated by the index (including the primary key, which is another index).

If you need to have the ability to look at records based on the sequence that they were added, an autonumber column can provide that capability, since every time a record is added the autonumber column is incremented (usually by 1). If a record is deleted or not actually added, the autonumber column may contain "holes" in its sequence, but the higher the number, the more recently the record was added to the table.

Collapse -

File organization in Access & Oracle.

by d_hainline In reply to File organization in Acce ...

Poster rated this answer

Collapse -

File organization in Access & Oracle.

by d_hainline In reply to File organization in Acce ...

This question was closed by the author

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums