A database table is a collection of two-dimensional arrays that represent and store the relationships between different objects in a database. This topic describes database objects related to database tables and the use of database tables, such as table storage, table compression, table groups, and temporary tables.
A table is the basic unit of data organization in a database. It consists of vertical columns and horizontal rows. For example, a table named Authors is created to store information about authors. Each column stores a specific attribute of all authors, such as their names. Each row contains all attributes of a specific author, such as the first name, last name, and address of the author. The number of columns in a table is specified when the table is created, and the columns are identified by column names. The number of rows can dynamically change.
Tables are divided into normal tables and temporary tables. In the Oracle mode of OceanBase Database, the lifecycle of a temporary table is the same as that of a normal table. After a temporary table is created, it becomes visible to any sessions (session/sess), and you can clear it only by explicitly running the DROP command. Temporary tables occupy namespaces of normal tables. Therefore, object names in temporary tables must be different from those in normal tables. After you run the DROP command on a temporary table, it is cleared without entering the recycle bin.
Session- and transaction-level data clearance strategies are defined for temporary tables. A session-level temporary table stores data of the specific session, and the data is cleared when the session is disconnected. Session-level temporary tables of different sessions are independent of each other. Data in a transaction-level temporary table is cleared when the transaction is committed.
Columns
In a database, a column in a table records the values of an attribute field, and the attribute name is the column name. Apart from the column name, information about a column also includes the data type and maximum length (precision).
In addition to normal columns, the Oracle mode of OceanBase Database also supports virtual columns (also known as generated columns) and invisible columns.
Virtual columns do not provide physical storage space as regular columns do. Values in a virtual column are calculated by expressions or functions defined for the column.
An invisible column is displayed only when you explicitly specify the column name during a query. If you execute the SELECT * statement to query data in a table, invisible columns are not displayed. Invisible columns are often used to migrate data or extend the features of published applications.
Rows
In a database, a row stores the values of all columns of a specific record. Simply put, a database consists of columns and rows. Each row in a table stores a set of related data in the same structure.
For example, a table is created to store information about companies. Each row contains a set of data about a specific company. Each column contains data about a specific attribute of all companies, such as the company name, address, or tax number.