Logical Structure of ORACLE Database Learning

Welcome to the Oracle community forum and interact with 2 million technical staff. A range (EXTENT) is a logical unit for database storage space allocation, which consists of continuous data blocks. Each segment is composed of one or more ranges. When all the space in the middle of a section is fully used, ORACLE assigns a new range to the Section. To maintain

Welcome to the Oracle Community Forum, interact with 2 million technical staff> A range of entry (EXTENT) is a logical unit for database storage space allocation, which consists of continuous data blocks. Each segment is composed of one or more ranges. When all the space in the middle of a section is fully used, ORACLE assigns a new range to the Section. To maintain

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Range

A range (EXTENT) is a logical unit for database storage space allocation. It consists of continuous data blocks. Each segment is composed of one or more ranges. When all the space in the middle of a section is fully used, ORACLE assigns a new range to the Section.

For the purpose of maintenance, each segment of the database contains the segment header block to describe the features of the segment and the range directory in the segment.

Data Block

Data block is the unit for ORACLE to manage the storage space in data files. It is the smallest unit of I/O used by the database, the size is different from the standard I/O block size of the operating system.

Data block format:

Common variable-length titles

Table directory

Row directory

Unused space

Row data

1. mode and mode object

A schema is a collection of schema objects. Each database user corresponds to a schema. A schema object is a logical structure that directly references database data. schema objects include tables, views, indexes, aggregates, sequences, synonyms, database chains, processes, and packages. A mode object is a logical data storage structure. Each mode object does not have a corresponding file on the disk to store its information. A schema object is logically stored in a tablespace in the database. The data of each object is physically included in one or more data files in the tablespace.

Table

A table is the basic unit for data storage in a database. Its data is stored in rows and columns. Each table has a set of table names and columns. Each column has a column name, data type, width, precision, and proportion. A row is a set of column information corresponding to a single record.

View

A view is a custom representation of data in one or more tables (or other views). It is defined by a query, therefore, it can be considered a stored query or a virtual table ). Views can be used in many places where tables are used.

Because a view is exported from a table, there are many similar views and tables. A view image table can define a maximum of 254 columns. A view can be queried, but it has certain restrictions when it is modified, inserted, or deleted. All operations performed on the view actually affect the data in the basic table of the view, restricted by the integrity constraints and triggers of basic tables.

Different from a table, a view does not allocate any storage space and does not actually contain data. The view defined by the query corresponds to the data in the view reference table. A view only stores its definition in the data dictionary.

Introducing a view has the following benefits:

Restrict access to the pre-defined set of rows in a table to provide additional security for the table.

Hide data complexity.

Simplify commands for users

It provides another idea for the data in the basic table.

Allows you to isolate applications from modifying basic table definitions.

Used for queries that cannot be expressed by views.

It can be used to save complex queries.

Aggregation

Clustering is an optional method for storing table data. A group of tables stores rows with the same public column value and is often used together. These public columns constitute a clustering code. For example, each DEPT table in the EMP table shares the DEPTNO column, so the EMP table and DEPT table can be clustered together. The DEPTNO column of the clustering code is the DEPTNO column, this aggregation physically stores all employee lines of each department in the same data block.

Index

An index is a selection structure related to tables and aggregation. An index is created to improve the performance of data retrieval. It can be used to quickly determine the specified information. ORACLE indexes provide quick access paths for table data. An index is applicable to a range of row queries or a specified row query.

Indexes can be created on one or more columns of a table. Once an index is created, it is automatically maintained and used by ORACLE, which is completely transparent to users. Indexes are logically and physically independent from data. The Creation or Deletion of indexes does not affect the table, and the application can continue to process the indexes. The retrieval performance of index data is almost constant. When many indexes exist in a table, the performance of modification, deletion, and insertion operations will decrease.

Each index has a unique index. The unique index ensures that no two rows in the table have duplicate values on the columns that define the index. ORACLE automatically defines UNIQUE indexes on UNIQUE codes to implement UNIQUE integrity constraints.

A composite index is an index created on a column of a table. A full set of indexes can accelerate the search speed of SELECT statements. In its WHERE clause, You can reference all or major parts of a composite index. Therefore, the column order is given in the definition, and the columns frequently accessed or most selected are put in the first place.

When an index is created, an index segment is automatically created in the tablespace. The space allocation and retention of the index segment are controlled by the following methods:

The allocation of the index range is stored in the parameter control of the index segment.

The unused space in the data block can be controlled by the PCTFREE parameter settings of this section.

Sequence generator

The sequence generator generates the serial number. This sequence generator is particularly useful in multi-user environments. It can generate various returned serial numbers without disk I/O or transaction blocking.

The serial number is an ORACLE integer and can contain up to 38 digits. A sequence definition specifies the general information: the name of the sequence, ascending or descending, the spacing between serial numbers, and other information. Definitions of all sequences are indeed stored in the data dictionary table in the SYSTEM tablespace, so all sequence definitions are always available. The SQL statement that references the serial number uses the serial number to generate a new serial number or use the current serial number. Once a serial number is generated for an SQL statement in a user session, the serial number is only available for this session. Serial number generation is independent of tables, so the same sequence generator can be used for one or more tables. The generated serial number can be used to generate a unique primary code.

Synonym

A synonym (synonym) is an alias for any table, view, snapshot, sequence, process, function, or package. Its definition is stored in the data dictionary. Synonyms are frequently used for security and convenience. They can be used:

The name and holder of the blocked object.

Provides location transparency for remote objects in distributed databases.

Simplifies SQL statements for users.

There are two synonyms: public and private. A public synonym is held by a special PUBLIC user group and can be accessed by every user in the database. A special synonym is included in the specified user mode and is only used by this user and authorized users.

Miscellaneous

Hashing is an alternative way to store table data and improve data retrieval performance. To use a collection, you need to create a collection to load the table to this aggregation. The table rows in the aggregate perform physical storage and retrieval based on the results of the aggregate function. The aggregate function is used to generate a value distribution. The value is called the aggregate value, which is based on the specified aggregate code value.

Program unit

A program unit is a stored procedure, function, and PACKAGE ). A process and function are combined by SQL statements and PL/SQL statements, which is an executable unit for executing a task. A process or function can be created and stored in the database. It can be executed by users or database applications. The difference between a process and a function is that a single value is returned to the caller, but no value is returned to the caller.

A package provides a method for encapsulating and storing related processes, functions, variables, and other package structures, allowing managers and application developers to use this method to organize such programs (routine ), to provide more functions and improve performance.

Database chain

A database chain is a named object, indicating a PATH from a database to another database ). In distributed databases, database chains are implicitly used when global object names are referenced.

1. Start and close databases and Instances

An ORACLE database is not always available to all users. The database administrator can start the database and open it. When the database is opened, you can access the information in the database. When the database is not in use, the DBA can shut down the database, and the user cannot access its information.

[1] [2] [3] [4]