Atitit. Physical storage structure principle and architecture design and practice of database tables
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