Oracle Storage Structure

 

Overall structure of the Oracle database

 

Database Structure

In fact, I introduced the database structure in the previous article about tablespace, but the figure is just a simple hierarchy. This picture looks quite complicated, just focus on several concepts.

Database(Database): A database is a warehouse that organizes, stores, and manages data according to the data structure.

Tablespaces(Tablespace)A tablespace is the logical division of a database. A tablespace can belong to only one database. All database objects are stored in the specified tablespace. But the main object to store is the table, so it is called the tablespace.

Segments(Segment): Segments are important organizational structures of tablespaces. segments refer to the generic names that Occupy data file space or a set of spaces used by database objects; segments can include table segments, index segments, rollback segments, temporary segments, and high-speed cache segments.

Extents): A logical unit for allocating database buckets. It consists of continuous data blocks. The first segment is composed of one or more disk areas. OracleAssign a new range to this segment.

Data Block(Data block):YesOralceThe unit used by the database to manage the storage space in data files.I/OThe minimum unit, which is different from the operating system standard.I/OBlock size.

 

(Storage clause Precedence) Storage specifications are preferred

 

OracleThere are three storage control methods.OracleThe default level, tablespace level, and segment level can be understood at the central, provincial, or county level. Regulations from the central Government to the local government. For example, in order to develop the economy and regulate the investment attraction. The Central Government stipulates that investment attracting can be implemented. A province is relatively poor. To attract investors, it stipulates that corporate income tax should be lowered. In order to attract resources, a county may require free use of investors' land5Year, no land use tax. Then, when an investor comes to the county, county-level regulations will be applied first.

Specific provisions:

1. No matter at which level you modify the storage method, the modified parameters will take effect for the storage extents (Disk Area) to be allocated in the future.

2. Some parameters can only be modified at the specified level. Some parameters can only be set at the tablespace level, and some parameters can only be set at the segment level.

 

 

Types of segments (Segment type)

 

I have made a special introduction to some basic knowledge about tablespace in another section. Here we will look at the common types of segments.

Table:

For a student renewal table with hundreds of thousands of data records, a table isSegments.

Table Partition:

If a table is very large and contains tens of millions of records and hundreds of millions of records, the efficiency of operations on the table is very low.A partitioned table divides data from a large table into many small subsets called partitions. Assume that a table is stored in China 13For a table with hundreds of millions of records, so many records are regular and can be separated based on a field. It can be divided by province-level administrative districts.34Partitions. Each partition is actually an independent table, but logically these partitions belong to the same table.

If a table is a normal table, only oneSegmentsIf a table isPartitionMay occupy multiple tables.Segments.

Cluster:

OracleA common table is called a heap table (Heap table), The data in the heap table is stored unordered, and often becomes unordered after a period of use. As shown in, the same indexKeyThe corresponding data is stored in differentBlock, If you want to queryKeyYou need to access a lot of differentBlockThe cost is very high. 

We found that many tables are related to the table and their data. Because we access data in a table, we often need to access data in another table. We can physically store the data in one partition.

Index

An index is a selection structure related to tables and aggregation. Just like a directory in a book, you can quickly locate the number of pages in a section of a chapter. An index can be created on one or more columns of a table, which greatly improves the query of a table. Then oneIndexIt may also occupy more than oneSegments.

 

Index-organized table:

Index the Organization table (Iot) Is the table stored in an index result. Tables stored in the heap are unorganized (that is, data can be stored anywhere as long as there is space available ),IotStore and sort by primary key. For your application,IotThere is no difference between a table and a "regular" table. When using the heap to organize tables, we must set aside space for the indexes on the table and Table Primary keys. WhileIotThere is no space overhead for the primary key, because data is cited and data is cited.

IotThe benefits not only save disk space usage, but also greatly reduceI/OReduces the access buffer cache (although obtaining data from the buffer cache is much faster than the hard disk, the buffer cache is not free and is never cheap. Multiple latches of the buffer cache are required for obtaining the first buffer cache, And the latches are serialized devices, which limit the scalability of applications)

Index Partition 

ForTableYesPartition, So for a largeIndexYesPartition.

Partition indexes can be classified into global and local partition indexes. Local indexes can also be divided into local prefix indexes and local non-Prefix indexes.

The number of local index partitions is equal to the number of corresponding table partitions. Therefore, each table partition corresponds to the corresponding index partition. When using a local index, you do not need to specify the partition range because the index is local for the table. When creating a local index, Oracle automatically creates an independent index partition for each partition in the table.

The global index creates an index based on the data of the entire table. The index entries in the index partition may be based on the same key value but from different partitions, it may also be a combination of multiple different key values. Global indexes allow the same or different key values of index partitions. There is no direct connection between the global index and the table, which is different from the local index.

Undo segment

Rollback segments are used to store values before data modification (including locations and values before data modification ). The header of the rollback segment contains the information about the transaction in use. A transaction can use only one rollback segment to store its rollback information, while a rollback segment can store the rollback information of multiple transactions.

Temporary segment

When Oracle processes a query, it is often necessary to prepare temporary space for the intermediate results (intermediate stage) of SQL statement parsing and execution. Oracle automatically allocates disk space known as the temporary segment (temporary segment. For example, a temporary segment is required for Oracle sorting. When the sorting operation can be performed in the memory or Oracle tries to use the index to execute the operation, it does not need to create a temporary segment.

Lob segment 

Large Object(Lob) Large objects. We know that the database can not only store data, but also some files such as video sound images.MB, DozensMBNormal. Of course, some databases do not supportLobData block, you can store a pointer in the table to point to the video sound file under a file.

Nested table

Nested tables, tables, andLobSimilarly, a pointer is stored in the table, pointing to another table.

Bootstrap segment

No explanation is found for the boot section.Pai_^

 

 

Extent alloc & dealloc(Disk distribution and release)

 

A Disk Area is a storage unit under a segment, and a disk area is physically a continuous data block.

 

A data file has a file header with several data blocks. This header records the distribution and release of the disk. In this file, some disk areas are used, and some disk areas are idle.

When to create a disk:

Create

Extension

Change (Increase)

When to release a disk:

Delete

Change (change to a smaller value)

 

 

Database Block(Database block)

 

Minimum unit input/Output

A data block is composed of one or more data blocks in the operating system.

A data block is the basic unit of a tablespace.

Db_block_sizeFile to indicate the size of the default Block

 

ViewOracleBlock Size:

 
[Ora10 @ localhost DBS] $ strings spfileora10.ora | grep-I db_block*. Db_block_size =8192(8 KB)
 

 

 

In early databases,OracleOnly one data block size is supported. Slave9iThe version supports setting the size change. However, this is not changed by any user, and the scope of this provision is2 kbTo32kbMust be a multiple, that is2kb \ 4kb \ 8kb \ 16kb \ 32kbFive sizes.

Note that the block size is set during database creation. Once set, the block size cannot be changed. Similar to the storage format of our disks,Fat16,FAT32,NTFS ....To change the storage format, you can only format all the data.

 

Hearder: Blocks to record some control information, helpOracleLocate the block and connect the block to the block.

Free Space: The space in the reproduction state.

Data : The space where data has been written. Data is stored in a bottom-up manner, just like a box in reality.

 

OrcaleHow to manage data storage

 

High-water mark

For a newly created table, the table contains many data blocks,Water markOriginal point to the first data block in the table.

Water markAs the inserted data moves forward. When some inserted data is deleted,Water markIt does not move backward. That isWater markIs the highest level in history.

 

When we want to insert some data,OralceWhat rules are used to select the Insert Location?

 

First, we will first look for the gray blocks, which indicate that data has been inserted, but these blocks are not fully occupied, and some of them will leave some space more or less, the rules for leaving space are described later. If a small data block is inserted, it can be completely inserted into the gray data block.

If the data size is large and all occupied blocks cannot be inserted, a blank block that has been inserted will be selected for insertion, that is, the light gray part.

If the data is very large and the blank blocks that have been inserted cannot be inserted, you have to use the blank blocks that have never been inserted for insertion. Of courseWater markIt moves forward.

 

OKNext we will analyze whether each data block allows data insertion rules.

 

the block header (the data block header) is not in the percentage range. When the remaining space is greater than 20% , freelist , when I want to insert a data entry, freelist scans this block and checks whether other data can be inserted. 20% when the block is full, it starts from freelist is removed. data inserted is not scanned.

A value smaller20%Idle blocks areFreelistWhen a block is re-mountedFreelistWhat about? ForFreelistThe deleted block can be released due to the delete update operation. When the occupied space is smaller40%When the free space is greater60%When this block is considered as a space, it will be re-mountedFreelist.