How Partitions change the way Oracle stores database objects

Oracle Database stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace. A database is divided into one or more logical storage units called tablespaces. A tablespace in an Oracle database consists of one or more physical datafiles. A datafile can be associated with only one tablespace and only one database. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks in a datafile. Tables are the basic unit of data storage in an Oracle database. Data is stored in rows and columns. Tables are defined with a table name (such as employees) and set of columns. Usually a table resides within a single tablespace except for partitioned tables. A partitioned table is a table which has one or more partitions, and each partition may reside on a different tablespace.

Figure: How Oracle stores database objects

At the finest level of granularity, an Oracle database stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk. The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks allocated for storing a specific type of information from a given datafile in a tablespace. The level of logical database storage greater than an extent is called a segment. A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same tablespace. For example, each table's data is stored in its own data segment, while each index's data is stored in its own index segment. If the table or index is partitioned, each partition is stored in its own segment. An Oracle database allocates space for segments in units of one extent. When the existing extents of a segment are full, an Oracle database allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk. A segment and all its extents are stored in one tablespace. Within a tablespace, a segment can include extents from more than one file; that is, the segment can span datafiles. However, each extent can contain data from only one datafile. When a new extent is allocated for a segment, Oracle tries to spread (or stripe) extents among all datafiles in the tablespace at that time. When you run out of free data blocks in a tablespace, you can add additional datafiles. Here is a pictorial representation of logical objects and physical storage.

Figure: Logical objects on storage

Oracle has several types of segments. Depending on the type, the usage pattern of the data within the segment varies. For Storage Tiering relocation, it is important to know what kind of segments is suitable for relocation. Here is a list of segment types.

• Data segments: contain data blocks where table rows are stored. Data segments/extents are prime candidates for relocation to a secondary storage tier. The old extents contain rows inserted earlier than new extents. Segment and extent properties are available by querying dictionary table dba_segments and dba_extents.
• Index segments: contain blocks for table indexes. It is important to database performance that I/O to these blocks are read/written at highest rate and hence should always be in the primary storage tier. Index segments move from one location to other in a tablespace/datafile and are not good candidates for relocating to secondary tier.
• Rollback segment: contain UNDO data which is critical for database recovery and should always be in on primary storage.
• Temp segment: the temporary workspace for intermediate stages of sort operations required by Oracle. Performance depends on I/O time of the temp segments and hence these segments must always be in primary storage.