Atitit. Physical storage structure principle and architecture design and practice of database tables

Atitit. Physical storage structure principle and architecture design and practice of database tables

1. The storage model for Oracle and DB2 databases is shown in figure: 1

1.1.2. Storage of table data in blocks and ROWID information 3

2. The physical storage structure of the data table is natural sequence, with the Btree storage structure 3

2.1. The stored order of the records in the normal table is stored in random order 3

2.2. The database is stored in the B-tree format, 4

3. Partition 4

4. The physical structure of the row Records and the ROWID4

5. Oracle has the following types of data structures: 5

6. Reference 7

1. The storage model for Oracle and DB2 databases is shown in figure:

The storage model for Oracle and DB2 databases is shown in figure:

As you can see, the data in the database is stored in the table space. A tablespace is the management of a database object that maps a logical database design to the physical storage of the operating system to indicate the physical location of the data. About Tablespaces, discussed later.

The logical structure of the Oracle database disk storage is that one database corresponds to multiple table spaces (tablespace), one table space corresponds to multiple segments (Segment), one segment corresponds to multiple zones (Extent), and one region corresponds to multiple blocks (data Block), the real data is stored in the data block

With multiple data blocks, the real data is stored in the data block. Here are the following points to note:

The size of a data block in 1.Oracle is 2KB (supported 2kb,4kb,8kb,16kb,32kb) by default, while DB2 is 4KB (support 4kb,8kb,16kb,32kb);

There is a concept of paragraph (Segment) in 2.Oracle, and there is no such concept in DB2, the tablespace is directly composed of the extents (Extent) in each container (data file), but there is also a very weak concept of Extent group. The contents of the segment mentioned below are all for Oracle;

The block of data in 3.Oracle is called Oracle block, and DB2 is directly referred to as the data page.

Extent in 4.Oracle is called a zone, while DB2 is called an extended data block. For the convenience of reading, this article is called District.

Second, paragraph (segment) (This piece is not detailed records)

A segment is used to store data for a particular type of database object in a tablespace that has an isolated storage structure, and it has one or more contiguous extents. When you create objects such as tables, indexes, clusters, and so on, Oracle allocates storage space (that is, segments) for those objects to store their data. According to the data stored in the paragraph, the characteristics of different uses, can be divided into the following types:

Segment type	Name	Segment type	Name /td>
index partition	Index partition segment	Deferred rol Lback	Deferred fallback segment
table partition	Table sub-section /p>	Undo	Undo segment (Restore segment)
Table	p> table Segment (data segment)	Temporary	Temporary segment
Cluster	Cluster segment	Cache	Cache segment
Index	Index segment	Lob	Binary Large object segment
Roll Back	Fallback segment	Lobindex	Binary Large Object index segment

1.1.2. Table data storage in blocks and ROWID information

Table fields in addition to designer-designed fields, there is a pseudo-column rowid;rowid that is the location information for each table record in Oracle. When a row of records is inserted into a table, the block of this record has a unique physical location, which is displayed as a ROWID pseudo-column when querying records;

Author:: Nickname: Old Wow's paw (full name:: Attilax Akbar al Rapanui Attilaksachanui) Kanji name: Ayron, email:1466519819@qq.com

2. The physical storage structure of the data table is natural sequence, and the Btree storage structure

3. Index 2.1. The storage order of the records in the normal table is stored in random order

, in a large data table, if you do not introduce other means, each time to find a small number of records from the first scan to the last one, so that the system will be slow to use, the use of indexes can effectively solve the problem;

The essential purpose of the index is to make the reading process reduce the range of data blocks when scanning the source table, so the performance is greatly improved.

Iii. Index 2.2. The database is stored in the B-tree format,

Only the problem of finding data by "primary key" is resolved. If you want to find another field, you need to index it.

An index is a B-tree file that is a keyword in a field. Suppose you have an employee table that contains an employee number (primary key) and a name of two fields. You can index a name, which stores the name in the B-tree format, followed by its location in the database (that is, the first few records). When looking for a name, the corresponding first record is found in the index and then read from the table.

This index lookup method, called "Indexed Sequential access Method" (Indexed sequential access Methods), is abbreviated as ISAM. It already has many implementations (such as the C-isam Library and the D-isam library), so you can write your own simplest database by using the codebase.

The question is whether the data is stored according to first served or the btree of the primary key. According to the truth is irregular, or not add a field to reorder the files. Yeyou may prikey self-increase, will automatically be ranked in the back "".

3. Partitioning 4. The physical structure of the row Records and the RowId

rowID can be recorded with a hash

D:\workspace Space \atiplatf\info_schema\columns\shopedb\ form Application Form

Each file is a record. This is a JSON-formatted record of this text

{

"table_name": "Character_sets",

"Character_octet_length": 96,

"Table_catalog": "Def",

"Column_comment": "",

"Column_default": "",

"Is_nullable": "NO",

"Column_type": "varchar (32)",

"Collation_name": "Utf8_general_ci",

"Character_set_name": "UTF8",

"Character_maximum_length": 32,

"Table_schema": "Information_schema",

"column_name": "Img_col",

"EXTRA": "",

"Ordinal_position": 2,

"Numeric_precision": null,

"Privileges": "Select",

"Numeric_scale": null,

"Column_key": "",

"Data_type": "IMG"

} 5. There are several types of data structures in Oracle:

Data files: Store data in the database. Tables and indexes are the logical structures that a database uses to store data, and data files are used to hold tables and index data.

Log file: The file that holds the database operation log. The entire database running process is journaled, the log is stored in the log file, the database system for data recovery operations.

Control file: A file that holds the physical structure and state information of a database, and stores information such as database name, data file and log file name, database log archive status, and system backup status.

The Oracle database consists of the following logical structures:

system table space for centralizing the storage of Oracle's system data.

A temporary tablespace that is used for temporal data processing during database transaction processing.

A user table space that stores the user's database objects and final data.

Restores the tablespace (Roll back table space), which is used to store the pre-data image of the transaction operation process in response to a user's possible fallback operation.

Third, district (extent)

The area is made up of blocks that are physically stored continuously. Area is the smallest unit of Oracle storage allocation. One or more blocks make up a section, one or more sections. A zone can belong to only one data file.

When a schema object with an actual storage structure is created in the database (such as a table, index), Oracle allocates a number of extents (depending on the initial size of the scheme) for the schema object to form a corresponding segment to provide the initial storage space for the schema object. When the allocated area in the segment is full, Oracle allocates the next new white space in the tablespace where the scenario object resides to accommodate more data.

Blocks (block)

Block is the smallest data management unit, that is, all of Oracle's stored I/O operations are in blocks. Correspondingly, the minimum unit of operating system execution I/O operations is the operating system block. Block size is an integer multiple of the size of the operating system block.

The chunk size is the property of a table space. The system and Sysaux table spaces have the same, standard block size, which is specified by the db_block_size initialization parameter when the database is created (both table spaces are created at the same time), but this parameter value cannot be changed after the database is created.

I. Control of documents

The control file is a small (usually the smallest) file in the database, typically in the size of 1~5m, which is a binary file. However, it is a critical file in a database that is critical to the successful startup and uptime of a database because it stores critical information that is not available elsewhere, including:

1, the name of the database

2. Name, location, online \ Offline status, and size of data files and redo log files

3. Information for recovering a database (log sequence number, checkpoint) when a disk failure or user error occurs

As the database runs, Oracle (which can only have Oracle itself) modifies the contents of the control file whenever a database checkpoint occurs or the structure of the database is modified. DBAs can modify some of the content in the control file through OEM tools, but neither the DBA nor the user should consider modifying the contents of the control file, which would otherwise break the control file.

Three, redo log file

When the user modifies the database, the data in memory is actually modified, and after a certain period of time, the results are then batched into the data file. Oracle does this primarily for performance reasons, because memory speeds are tens of thousands of times faster than hard drives for data operations.

Oracle saves the changes at any time using the (online) redo log file, which means that Oracle saves the changes in memory to the redo log files at any time. "At any time" means that you may have written to the redo log file several times before writing the result of the modification to the data file. Therefore, even if a failure results in a database crash, Oracle can use the information in the Redo log file to recover the lost data. As long as the redo information for an operation is not lost, the redo information can be used to reproduce the operation.

Oracle uses redo log files in a circular fashion, so at least two redo log files are required per database. When the first redo log file is full, the background process LGWR (the log write process) begins writing to the second redo log file, and when the second redo log file is full, it is written to the second redo log file, and so on.

6. Reference

Self-realization of a simple database _ Computer Tutorial Learning Network. htm

The simplest implementation of the database-the Nanyi of the network log. htm