Tablespaces
The tablespace is another logical organizational unit used by the Oracle database, which acts as the
intermediary between logical tables and physical storage. Every table or index, when created, is placed
into a tablespace. Atablespace can contain more than one table or index, or the table or index can be
partitioned across multiple tablespaces, as described later in the section on partitions. A tablespace maps
to one or more files, which are the physical entities used to store the data in the tablespace. A file can
only belong to one tablespace. A tablespace can contain row data or index data, and a table and an index
for a table can be in different tablespaces.
Atablespace has a number of attributes, such as the block size used by the tablespace. This block size is the
smallest amount of data retrieved by a single I/O (input/output) operation by the Oracle database. Your
database administrator (hopefully) selected an appropriate block size for a tablespace, which depends on a
wide range of factors, including the characteristics of the data and the block size of the underlying operating
system.
intermediary between logical tables and physical storage. Every table or index, when created, is placed
into a tablespace. Atablespace can contain more than one table or index, or the table or index can be
partitioned across multiple tablespaces, as described later in the section on partitions. A tablespace maps
to one or more files, which are the physical entities used to store the data in the tablespace. A file can
only belong to one tablespace. A tablespace can contain row data or index data, and a table and an index
for a table can be in different tablespaces.
Atablespace has a number of attributes, such as the block size used by the tablespace. This block size is the
smallest amount of data retrieved by a single I/O (input/output) operation by the Oracle database. Your
database administrator (hopefully) selected an appropriate block size for a tablespace, which depends on a
wide range of factors, including the characteristics of the data and the block size of the underlying operating
system.
Prior to Oracle9i, you could only have a single block size for an entire database. With Oracle9i, multiple
block sizes are supported within a single database, although each block size must have its own data
buffers in the SGA.
block sizes are supported within a single database, although each block size must have its own data
buffers in the SGA.
A tablespace is the basic administrative unit in an Oracle database. You can take a tablespace online or
offline (that is, make it available or unavailable through the Oracle database instance) or back up and
recover a tablespace. You can make a tablespace read-only to prevent write activity to the tablespace.
You can also use a feature called transportable tablespaces as a fast way to move data from one database
to another. The normal way to take data out of one Oracle database and move it to another Oracle
database is to export the data and then import the data. These processes operate by reading or writing
each row. A transportable tablespace allows you to move the entire tablespace by simply exporting
descriptive information about the tablespace from the data dictionary. Once you have exported this
information, you can move the files that contain the tablespace and import the descriptive information
into the data dictionary of the destination database (this is described in detail in Chapter 6, “The Oracle Data
Dictionary.” This method of moving data is significantly faster than importing and exporting data.
offline (that is, make it available or unavailable through the Oracle database instance) or back up and
recover a tablespace. You can make a tablespace read-only to prevent write activity to the tablespace.
You can also use a feature called transportable tablespaces as a fast way to move data from one database
to another. The normal way to take data out of one Oracle database and move it to another Oracle
database is to export the data and then import the data. These processes operate by reading or writing
each row. A transportable tablespace allows you to move the entire tablespace by simply exporting
descriptive information about the tablespace from the data dictionary. Once you have exported this
information, you can move the files that contain the tablespace and import the descriptive information
into the data dictionary of the destination database (this is described in detail in Chapter 6, “The Oracle Data
Dictionary.” This method of moving data is significantly faster than importing and exporting data.
The 10g release of the Oracle database includes the ability to use transportable tablespaces between
Oracle databases on different operating system platforms.
Oracle databases on different operating system platforms.
Segments and Extents
Individual objects in the database are stored in segments, which are collections of extents. Data blocks are
stored in an extent. An extent is a contiguous piece of disk storage. When Oracle writes data to its data
files, it writes each row to a data block that has space available for the data. When you update information,
Oracle tries to write the new data to the same data block. If there is not enough space in that block, the
updated data will be written to a different block, which may be in a different extent.
If there are no extents that have enough space for new information, the Oracle database will allocate a
new extent. You can configure the number and size of extents initially and subsequently allocated for
a tablespace.
stored in an extent. An extent is a contiguous piece of disk storage. When Oracle writes data to its data
files, it writes each row to a data block that has space available for the data. When you update information,
Oracle tries to write the new data to the same data block. If there is not enough space in that block, the
updated data will be written to a different block, which may be in a different extent.
If there are no extents that have enough space for new information, the Oracle database will allocate a
new extent. You can configure the number and size of extents initially and subsequently allocated for
a tablespace.
Partitions
As mentioned previously, you can use partitions to spread data across more than one tablespace. Apartition
is a way to physically segregate the data in a table or index based on a value in the table or index. There are
four ways you can partition data in an Oracle database:
❑ Range partitioning, which divides data based on a range of values, such as putting customers
with a last name starting with the letters A–G in one partition, the customers with last names
starting with the letters H–M in another partition, and so on.
❑ Hash partitioning, which divides data based on the result of a hashing algorithm. Hash parti-
tioning is used when range partitioning would result in unbalanced partitions. For instance, if
you are partitioning a table based on a serial transaction identifier, the first partition will com-
pletely fill before the second partition is used. A hash algorithm can spread data around more
evenly, which will provide greater benefits from partitioning.
❑ Composite partitioning, which uses a hash algorithm within each specific range partion.
❑ List partitioning, which partitions based on a list of values. This type of partitioning is useful when a
logical group, such as a list of states in a region, cannot be easily specified with a range description.
is a way to physically segregate the data in a table or index based on a value in the table or index. There are
four ways you can partition data in an Oracle database:
❑ Range partitioning, which divides data based on a range of values, such as putting customers
with a last name starting with the letters A–G in one partition, the customers with last names
starting with the letters H–M in another partition, and so on.
❑ Hash partitioning, which divides data based on the result of a hashing algorithm. Hash parti-
tioning is used when range partitioning would result in unbalanced partitions. For instance, if
you are partitioning a table based on a serial transaction identifier, the first partition will com-
pletely fill before the second partition is used. A hash algorithm can spread data around more
evenly, which will provide greater benefits from partitioning.
❑ Composite partitioning, which uses a hash algorithm within each specific range partion.
❑ List partitioning, which partitions based on a list of values. This type of partitioning is useful when a
logical group, such as a list of states in a region, cannot be easily specified with a range description.
Partitions are defined when you create a table or an index. Since each partition of a table can be placed in
a different tablespace, you can perform maintenance on a single partition, such as backup and recovery
or moving tablespaces.
a different tablespace, you can perform maintenance on a single partition, such as backup and recovery
or moving tablespaces.
Partitions can also contribute to the overall performance of your Oracle database. The Oracle query optimizer is aware of the use of partitions, so when it creates a plan to execute a query, the plan will skip partitions that have been eliminated from the query with selection criteria. In addition, partitions can be in different tablespaces, which can be in different parts of your disk storage, which can reduce disk head contention during retrievals.
No comments:
Post a Comment