2TablesandTableClusters Reading Notes

Why # CNCPT010 is not reasonable for these documents due to its fragmentation

Source of the article Home/Database/Oracle Database Online Documentation 11g Release 2 (11.2)/Database Administration http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT010 additional words for these documents, due to relatively fragmented, chapter is not reasonable is

Article Source

• Home/Database/Oracle Database Online Documentation 11g Release 2 (11.2)/Database Administration
• Http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT010

Additional

• For these documents, it is normal that the chapters are not reasonable because they are scattered, except for the first part. This is the second article in the official document, however, I personally think it is better to read the logical storage structure and physical storage structure, as well as the memory structure and process structure, so the next article will take the logical storage structure for Reading Notes.

Table and cluster table)

• Introduction to schema objects
• Table Overview
• Table ters Overview

Schema object

• Schema is the logical container of a schema object. Examples of schema objects include tables or indexes. schema objects can be created or manipulated using SQL.
• A database user has a password and multiple database permissions. each user has a schema with the same name as the user. The schema contains the user data.
  

Schema object type

• Table: The most important schema object. Tables in oracle are stored in rows.
• Index: the schema object that contains the entry of the index row of the table or cluster table.
• Partition: it is the piece of a table or index. Each partition has its own name and can have its own storage features.
• View: a custom means to display data. The task view only stores queries, and the view does not store the data to be queried.
• Sequence: A schema object created by a user. It provides a shared integer Generation Mechanism for multiple users.
• Dimension: in the same table, a collection of columns acts as a column set. A column set forms a column set pair. A dimension describes the parent-child relationship between column set pairs. Dimensions are often used to classify data.
• Synonym (synonym): alias of the schema object. Actual data is not stored.
• PL/SQL subprograms or packages: PL/SQL is oracle's procedural extension of SQL. Its subroutine is named PL/SQL block and can be called using a set of parameters. A package is a group of associated subprograms.
• Others: there are some other objects, such as database users, roles, context, and directory objects, which are not included in a schema. They are operated by SQL and stored in the database.

Schema Object Storage

• Some schema objects are stored in a logical Storage Structure-segments (segments), such as non-partitioned heap organization tables or indexes. Other schema objects, such as views or sequences, are only composed of metadata.
• The schema object logic is stored in the tablespace. However, the schema and the tablespace are irrelevant. A tablespace can store various schema objects, but they can come from different schemas, schema objects can exist in different tablespaces. The data of each object is stored in one or more data files.
  

Schema object dependency (dependencies)

• Some schema objects reference other objects, which are schema object dependencies. For example, a view is created from other tables or other views. PL/SQL subprograms call other subprograms. If object A references object B, object A is the dependent object of object B, and B is the referenced object of object A (referenced object)
• Oracle has an automatic object dependency management mechanism to ensure that the dependent object is always up-to-date. When a dependent object is created, the database will track the dependency between the dependent object and the referenced object. When the referenced object changes, the dependent object is marked as invalid. For example, if you delete a table, the view of the table will be unavailable.
• After changing the referenced object, the dependent object becomes invalid and must be re-compiled. (Official documentation has example http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT1859)

SYS and SYSTEM schema

• All oracle databases have an administrative account. This account has high-level permissions and is designed to be executed by dba-authorized persons. These tasks can be performed by disabling the database, manage memory and storage, create and manage database users, etc.
• As the database is created, SYS schema has almost all permissions. It stores the base table and view of the data dictionary. The table in SYS schema is only operated by the database and cannot be modified by the user.
• SYSTEM schema is created along with the creation of the database. It stores additional tables and views (information about the administrative information and oracle tools)

Sample Schemas

• It is a set of sample Schemas that are related to each other and used in concert with oracle documents
• The following is an example.
  

Table Overview

• Oracle divides tables into two basic types: Relational Tables and object tables.
• A relational table has the following organizational characteristics:
  
  • Heap organization table (default)
  • Index the Organization table (sort by component)
  • External table (metadata in the database, but data outside)
  • A table can be either a permanent table or a temporary table.
    
    • Permanent Table, data will be permanently stored in the database
    • Temporary tables often only exist during transactions or sessions. Once a transaction or session ends, data cannot be viewed.
• Next we will discuss the following topics:
  
  • Rows and columns
  • Example: Create and modify a table
  • Oracle Data Type
  • Integrity constraints
  • Object table
  • Temporary table
  • External table
  • Table store
  • Table Compression

Rows and columns

• A table consists of table names and column sets. A column identifies an attribute of an object.
• Generally, a column name, data type, and length are given during table creation.
• A table can contain virtual columns. pseudo columns do not occupy disk space. They are usually computed using some expressions.

Example

• See official documents for http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT88807

Oracle Data Type

• Each column has a data type so that the storage format, constraints, and values can be determined.
• Oracle provides common built-in data types. The most common types are as follows:
  
  • Character Data Type
  • Digital Data Type
  • Time Data Type
  • Rowid Data Type
  • Format Models and Data Types
  • There are also some important built-in data types, including raw, LOBs, and collections. Pl/SQL data types with constants and variables, including BOOLEAN, reference types, composite types (records), and user-defined types

Character Data Type

• Character data types are stored as strings. common character data types include VARCHAR2.
• Character-related bytes are encoded in the schema, often called character set and code page. For example, 7-bit ASCII, EBCDIC, and Unicode UTF-8
• The length semantics of character data types can be divided into two types: byte and character. Byte semantics: it refers to the string as a string of bytes (default). It refers to the string as a string of characters. Technically, a character is a code point of the database character set ).
• VARCHAR2 and CHAR
  
  • Varchar2 stores variable-length string constants. String constants should be enclosed by single quotes.
  • Char is a fixed-length String constant.
  • Oracle Database compares VARCHAR2 values using nonpadded comparison semantics and compares CHAR values using blank-padded comparison semantics. (that is, varchar2 will not be filled, and char will be filled with space characters if it is not filled)
• NCHAR and NVARCHAR2
  
  • All characters that store the unicode Character Set
  • Features are similar to char and varchar2

Digital Data Type

• Oracle digital data type storage integer, floating point, 0, infinite and infinitely small, or undefined number, is called "not a number" Or NAN
• The numeric data type is variable-length, and each value adopts scientific notation. one byte is used to store the index. The database uses up to 20 bytes to store fractional parts (valid digits in decimal places, and the database does not store leading 0 or ending 0)
• NUMBER
  
  • This is the most common digital storage type.
  • NUMBER (p, s)
    
    • P is the precision and specifies the total number. If not specified, the number (without any rounding) given by the application is stored in bytes)
    • S is the value range and specifies the number of digits after the decimal point.
    • Example: the salary column is type NUMBER (100,000), so the precision is 8 and the scale is 2. Thus, the database stores a salary of 100000.00.
  • Floating Point -- BINARY_FLOAT and BINARY_DOUBLE
    
    • BINARY_FLOAT and BINARY_DOUBLE use binary precision to perform arithmetic operations faster than number.

Time Data Type

• The basic data types are DATE and TIMESTAMP. oracle also provides time zone support for timestamps.
• DATE
  
  • DATE stores the DATE and time. inside it, It stores numbers. Each date is stored as 7 bytes, which indicates century, year, month, day, hour, and second.
  • The default format is DD-MON-RR, for example 01-JAN-11
  • The default time format is 24-hour format-HH: MI: SS. The default format is 24-hour format-HH: MI: SS.
  • You can change the date format at the instance or session level.
  • When only the time has no date, the date will be set as the first day of this month
• TIMESTAMP
  
  • Date extension, which can store scores in seconds and can be used to store precise time
  • Timestamp with time zone and TIMESTAMP WITH LOCAL TIME ZONE

ROWID Data Type

• Each row in the table has an address in the database, and rowid is the address, which is divided
  
  • Physical rowid: stores the address of the heap organization table, table cluster and table, and the row of the index partition.
  • Logical rowid: stores the address of the rows in the index organization table.
  • Foreign rowid: indicates the foreign tables (Foreign rowids are identifiers in foreign tables, such as DB2 tables accessed through a gateway. They are not standard Oracle Database rowids .)
  • Universal rowid or UROWID supports all types of rowids.
• Usage of rowid: Mainly used for indexing. For example, the B-tree index has a series of keys divided into several parts. Each key is related to rowid, which provides a quick way to access data.
• You can also define a rowid data type column when creating a table. For example, it can be used to store rows that violate the integrity constraints (this is not quite understandable, the original Article is You can also create tables with columns defined using the ROWID data type. for example, you can define an exception table with a column of data type ROWID to store the rowids of rows that violate integrity constraints. columns defined using the ROWID data type behave like other table columns: values can be updated, and so on ).
• Rowid pseudo Column
  
  • Each table exists, but is not stored in the table. You cannot insert or delete the rowid pseudo column, just as the SQL function does not have parameters.

Format Models and Data Types

• Format models is a model used to describe numbers or time formats.
• TO_CHAR and TO_DATE

Integrity constraints

• Integrity constraints are rules used to restrict one or more columns of data. These rules prevent invalid data from entering the table and prevent dependent data from being deleted.
• If the Integrity Constraint is enabled, the database checks the updated or inserted data. If the Integrity Constraint is disabled, the update and insertion of invalid data cannot be prevented.
• You can add constraints after creating a table. The constraints can be temporarily disabled. The database stores integrity constraints in the data dictionary.

Object tables

• In oracle, object type is a user-defined type, including the name, attribute, and method.

CREATE TYPE department_typ AS OBJECT   ( d_name     VARCHAR2(100),     d_address  VARCHAR2(200) );/CREATE TABLE departments_obj_t OF department_typ;INSERT INTO departments_obj_t   VALUES ('hr', '10 Main St, Sometown, CA');

Temporary table

• Temporary table data only exists in sessions or transactions, and session temporary data is only available for this session, that is, private
• You can use create global temporary table to CREATE a temporary table. The on commit clause can indicate whether the data in the temporary table is transaction-specific (default) or session-specific.
• Unlike other databases, temporary oracle tables are statically defined and are stored in data dictionaries for a long time.
• Because it is a static definition, you can create an index (also temporary), or create a view or trigger.
• Temporary segment allocation: only when data is inserted for the first time will it be allocated. In the temporary table of transaction-specific, the temporary segment will be released at the end of the transaction. In the temporary table of session-specific, the temporary segment will be released at the end of the session

External table

• External tables obtain data from external data sources, making the external data look like a database.
• It is very useful for accessing flat files.
• External tables are very useful for data warehouse environments that require etl
• To CREATE an external TABLE, you need to put its metadata into the data dictionary. Generally, create table... Organization external statement
• External Table Access Driver (External Table Access Drivers): an API that allows a database to Access External Table data.
  
• Oracle provides two drivers: ORACLE_LOADER (default) and ORACLE_DATAPUMP to access external tables.
• ORACLE_LOADER uses the SQL * Loader tool to read-only access external tables
• ORACLE_DATAPUMP can unload External table data. This operation includes the process of reading database data and inserting data into an external table. When this external table is created, it cannot be updated or added; similarly, you can load external table data.

Table store

• Oracle uses data segments in tablespaces to store table data. A data segment consists of a partition composed of data blocks (This section will be detailed in the logical storage results section)
• Table Organization
  
  • Generally, tables are organized in the form of heap, which means that the data rows are not in order, but in the physical structure.
  • Tables can store pseudo columns (not occupying space). pseudo columns are usually expressions or functions that can create indexes for fake and shoddy data, collect statistics, and create integrity constraints.
• Row store
  
  • The database stores rows in data blocks. Each row in the table contains one or more row pieces as long as there are no more than 256 columns (attributes ).
  • If possible, oracle stores each row on one row piece. However, if one row of data cannot be stored in one data block, or the data volume after update increases, multiple row pieces will be used.
  • In table cluster, one more cluster key is stored.
• Rowids and row pieces
  
  • Rowid is a 10-byte physical address, which is the physical address of the row. Each row has a unique rowid pointing to its row piece.
  • In table cluster, rows in different tables can have the same rowid if they are in the same data block.
  • Oracle databases use rowid to build Indexes

• Storage of null values

  • A null is the absence of a value in a column. Nulls indicate missing, unknown, or inapplicable data)
  • If the null value is located between columns with data values, 1 byte space is required to store the length of the column.
  • If null is at the end of a row, no storage space is required, because the row header of the next row will mark the value of the remaining columns at the end of the previous row as null.
  Table Compression
  • The database uses table compression to reduce the storage space of the table. The compression reduces the usage space of the database buffer cache in the memory. In some cases, the query speed can be improved. Table compression is transparent to database applications
  • Basic and Advanced Row Compression
    
    • Basic table compression: This method is used for operations on a large amount of data and does not compress data modified using conventional DML. Use the direct path loads method, alter table... MOVE operation, or online table redefinition to achieve basic Compression
    • Advanced row compression: designed for OLTP applications to compress SQL-operated data
    • Under these two types of compression, the database stores compressed rows in row-major format. All columns in each row will be stored together.
      
    • Duplicate values are replaced by a short reference in the data block header. Therefore, the information that requires re-creation of Non-compressed data will be stored in the data block (very easy to understand at present, this is the original article: for basic and advanced row compression, the database stores compressed rows in row-major format. all columns of one row are stored together, followed by all columns of the next row, and so on (see Figure 12-7 ). duplicate values are replaced with a short reference to a symbol table stored at the beginning of the block. thus, information needed to re-create the uncompressed data is stored in the data block itself .)
    • We can also set compression manually.
      

      ALTER TABLE oe.orders COMPRESS FOR OLTP;CREATE TABLE sales (    prod_id     NUMBER     NOT NULL,    cust_id     NUMBER     NOT NULL, ... ) PCTFREE 5 NOLOGGING NOCOMPRESS PARTITION BY RANGE (time_id) ( partition sales_2010 VALUES LESS THAN(TO_DATE(...)) COMPRESS BASIC,   partition sales_2011 VALUES LESS THAN (MAXVALUE) COMPRESS FOR OLTP ); 

• Hybrid Columnar Compression
  
  • In a group of rows, the same columns are stored together, and data blocks are not stored in the row-major format. Instead, a combination of rows and Columnar is used.
  • Hybrid Columnar compression type
    
    • Warehouse Compression
    • Online archival Compression
    • The direct path loads, alter table... MOVE operation or online table redefinition
  • Hybrid Columnar compression optimizes data warehouses and decision-making support applications in Exadata storage devices. Other storage systems can also support this compression, but cannot provide efficient queries like Exadata
  • Compression Units)

Table clusters Overview

• Is a group of table shared columns and stored in the same data block. Assume that two tables, employees and departments, have the same column department_id, so department_id is shared in the two tables, and the corresponding data of the two tables will exist in the same data block.
• Cluster key: the same column, for example, department_id (of course, it is acceptable if multiple columns are the same)
• Cluster key value: value of the same column
• Compared with the table in the first class, it has the following benefits:
  
  • IO can be reduced during join Operations
  • During the join operation, the access time can be accelerated.
  • The storage space for tables and indexes is reduced.
• Not recommended:
  
  • Frequently updated tables
  • Tables that frequently require full table Scan
  • Table to be truncate

• Indexed Clusters

  • The index is used for the table cluster. The B-tree index is created on the cluster key and needs to be created before the row is inserted. (is that true? Original article: A cluster index must be created before any rows can be inserted into clustered tables .)
    

    CREATE CLUSTER employees_departments_cluster   (department_id NUMBER(4))SIZE 512;CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;CREATE TABLE employees ( ... )   CLUSTER employees_departments_cluster (department_id);CREATE TABLE departments ( ... )   CLUSTER employees_departments_cluster (department_id);

• Hash ters Overview

  • Like the index cluster, the index key is replaced by the hash function as the hash key.
  • Recommended scenarios:
    
    • Queries are much larger than modifications
    • Hash keys are often used for equivalent queries.

  • Code example:

    CREATE CLUSTER employees_departments_cluster   (department_id NUMBER(4))SIZE 8192 HASHKEYS 100;SELECT *FROM   employeesWHERE  department_id = :p_id;SELECT * FROM   departments WHERE  department_id = :p_id;SELECT * FROM   employees e, departments d WHERE  e.department_id = d.department_idAND    d.department_id = :p_id;

• There are also two variants
  
  • Single-table hash cluster
  • Sorted hash cluster