Simplified Logical and Physical Data Organization

4012010

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.

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.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: