Simplified Logical and Physical Data Organization

January 4, 2010

This post shows a rather busy slide from a presentation I gave in 2008.  The slide shows an overview of logical and physical data organization in a basic Oracle database.

One of the keys to being able to administer Oracle databases efficiently is understanding how data in its most basic form is organized in a database.  Table data and its indexes are stored in logical entities called tablespaces.  This diagram depicts two tablespaces, which might be the SYSTEM tablespace and a second tablespace for user data.

Tablespaces store the tables and indexes in one or more datafiles.  There are operating system and Oracle limitations to the size and number of tablespaces.  For instance, with the default settings (8KB block size), there must be more than one data file to contain 33GB of data.

Tables and indexes are two types of segments, both of which may have parts (extents) stored in multiple datafiles for a single tablespace.  As segments grow in size, the parts of segments (extents) are rarely stored in adjacent areas of the data files.  This slide shows 5 tables and 6 indexes for one of those tables in a single tablespace.

Each segment may be composed of one or more extents.  Even though extents for a single segment may be scattered throughout the datafiles, the tablespace is not considered to be suffering from fragmentation unless there are small areas, for instance between E3 and E4, which are too small to contain a new extent.  It is important to standardize on a common extent size for all objects in a tablespace to avoid fragmentation.

Each extent contains 5 or more adjacent data blocks.  This graphic illustrates a 64KB extent size, composed of eight 8KB blocks.

In simple cases, if  a block contains table data, the block will be logically divided into one or more rows, typically with a little empty space to allow the rows to grow later.

Each row is composed of one or more columns, which contain the data stored in the database.  The book “Troubleshooting Oracle Performance” provides evidence with a test case that indicates accessing the left-most columns in a row is more efficient than accessing the right-most columns in the same row.