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
Extra words
- For these documents, due to the relatively fragmented, the chapter is not reasonable is normal, of course, except for the beginning, this article in the official document is the second, but the individual think should read the logical storage structure and physical storage structure and memory structure and process structure to see better, so the next article will take the logical storage structure to do reading notes
Table and cluster table (table clusters)
- Introducing schema Objects
- Table Overview
- Cluster table (table clusters) Overview
Schema Object
- Schema is the logical container for the schema object, with examples of schema objects with tables or indexes, and schema objects can use SQL to create or manipulate
- Database users have a password and a variety of database permissions, each user has a schema, the schema and the user name, the schema contains the user's data
Schema Object type
- Table: The most important schema object, the tables in Oracle are row-stored
- Index: The schema object that contains the entry of the index row of the table or cluster table
- Partition (partition): is a table or index of piece, each partition has its own name, can also have their own storage features
- View: A personalized way to customize the presentation of data, the task view stores only queries, and the view does not store the data to be queried
- Sequence (sequence): A user-created schema object that can provide shared, integer-generating mechanisms for multiple users
- Dimension (Dimension): In the same table, a collection of columns is used as a collection of columns, a column collection pair is formed between the column collections, and a dimension is a parent-child relationship that describes the pair of column sets. Dimensions are often used to classify data
- Synonym (synonym): the alias of the schema object, does not store the actual data
- PL/SQL subroutine or package: PL/SQL is a procedural extension of Oracle for the process Its subroutines are named PL/SQL blocks and can be called using a set of parameters. A package is a group of associated subroutines
- Other: There are other objects, such as database users, roles, contexts, directory objects, which are not contained in a schema, which are manipulated by SQL and stored in the database
Schema Object Storage
- There are schema objects stored in a logical storage structure-segment (segment), such as a non-partitioned heap organization table or index. Other schema objects, such as views or sequences, consist only of metadata.
- Schema object logic is stored in a tablespace (tablespace), however, schema and tablespace do not have a relationship, a tablespace can store various schema objects, and they can come from different schemas, and schema objects can exist in different tablespaces. Data for each object is stored in one or more data files
Schema object Dependency (dependencies)
- Some schema objects refer to other objects, which is the schema object dependency. For example, a view is created from another table or other view, and the PL/SQL subroutine calls other subroutines. If you define an object A that references object B, object A is a dependent object of B (dependent object) and B is a reference object (referenced objects)
- Oracle has an object-dependent automatic management mechanism that ensures that dependent objects are always up-to-date and that when a dependent object is created, the database will track dependencies on dependent and referenced objects. When the reference object changes, the dependent objects are marked as invalid. For example, if a user deletes a table, the view of the table will not be available
- After changing the reference object, the dependent object becomes invalid and must be recompiled. (Official document has example http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT1859)
SYS and SYSTEM schema
- All Oracle databases have a administrative account, which has very high privileges and is specifically designed to perform tasks for those with DBA authority, which can be enabled to turn off the database, manage memory and storage, create and manage database users, etc.
- The SYS schema, created with the creation of the database, has almost all permissions, and it stores the base tables and views of the Data Dictionary (dictionary). The tables in the SYS schema are manipulated only by the database and cannot be modified by the user
- The SYSTEM schema is created with the creation of the database, storing additional tables and views (information on administrative information and Oracle tools)
Sample Schemas
- is a set of sample schemas that are linked to each other, with some explanation of the Oracle documentation
- One example is given below
Table Overview
- Oracle divides a table into two basic categories: relational tables and Object tables
- The relational table has the following organizational characteristics:
- Heap Organization table (default)
- Index organization table (ordered by component)
- External tables (metadata in the database, but data outside)
- A table can be a permanent table or a temporary table
- Permanent tables, data is persisted in the database
- Temporary tables, which often exist only during a transaction or session, will not be viewable once the object or session ends
- The following topics will be discussed next
- Rows and Columns
- Example: Creating tables and modifying tables
- Oracle data types
- Integrity constraints
- Object Table
- Temp table
- External table
- Table Storage
- Table compression
Rows and Columns
- A table consists of a collection of table names and columns, and a column identifies an attribute of the entity.
- Typically, when you create a table, you give a column name, data type, and length.
- A table can contain pseudo-columns (virtual column), and pseudo-columns do not occupy disk space, which is usually the calculation of some expressions
Example
- See Official documents to http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT88807
Oracle data types
- Each column has a data type so that it can develop its storage format, constraints, and range of values.
- Oracle provides a common set of built-in data types, the most common of which are as follows:
- Character data type
- Numeric data types
- Time Data type
- ROWID data types
- Format Models and Data Types
- There are also some important built-in data types, including Raw,lobs, and collections. PL/SQL has constants and variable data types, including Boolean, reference types, composite types (records), and user-defined types
Character data type
- Character data types store characters as strings and commonly used character data types are VARCHAR2
- The bytes associated with the character are encoded in the schema, often called the character set and the 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--bytes and characters. Byte semantics, which is the string as a sequence of bytes (the default), and a character semantics, which is a sequence of characters. Technically, a character is a code point for the database character set.
- VARCHAR2 and Char
- VARCHAR2 stores variable-length string constants. String constants should be enclosed in quotation marks.
- 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. (meaning that varchar2 will not fill, and char will be filled with whitespace without filling the established characters)
- NCHAR and NVARCHAR2
- Is the character that stores the Unicode character set.
- Features similar to the CHAR,VARCHAR2
Numeric data types
- Oracle Numeric data types store integer numbers, floating-point numbers, 0, infinity, infinity, or undefined numbers, called "not numbers" or Nan
- Numeric data types are variable-length, and each value is in scientific notation, where one byte is used to store the exponent. The database uses up to 20 bytes to store the fractional portion (a valid number in a decimal, the database does not store a leading 0 or 0 at the end)
- Number
- This is one of the most common types of digital storage
- You can specify the number of digits in the decimal point (p,s)
- P is the precision that specifies the total number of digits, and if not specified, bytes will be stored by the app given the number (without any rounding)
- S is a range of values that specifies the number of digits after the decimal point
- Example: The salary column is type number (8,2), so the precision are 8 and the scale is 2. Thus, the database stores a salary of 100,000 as 100000.00.
- Floating-point--binary_float and Binary_double
- Binary_float and binary_double use binary precision, which is faster than number when performing arithmetic operations
Time Data type
- The basic data type is date and timestamp,oracle also provides time zone support for timestamps
- DATE
- Date stores dates and times, within which numbers are stored. Each date is stored as 7 bytes, supporting the representation of the century, year, month, day, and minute seconds
- The default format is DD-MON-RR, such as 01-jan-11
- Default time format 24-hour format-hh:mi:ss, default is 24-hour Format-hh:mi:ss
- You can change the date format at the instance level or session level
- The date will be set to the first day of the month when there is only time when there is no date
- TIMESTAMP
- The extension of date can store fractional seconds, which can be used to store precise time
- TIMESTAMP with time zone and TIMESTAMP with LOCAL timezone
ROWID data types
Each row in the
- table has an address in the database, ROWID is the address, which is divided into
- physical ROWID: The address of the row that stores the heap organization table, the table family and the table, and the index partition
- logical ROWID: The index organization is stored The address of the table's row
- Foreign rowid: Is the flag of the Foreign tables (Foreign rowids is identifiers in Foreign tables, such as DB2 tables a Ccessed through a gateway. They is not standard Oracle Database rowids.)
- Universal rowID or UROWID supports all types of rowID
- rowi Use of D: Mainly used for indexing, such as B-Tree index, a series of keys are divided into several parts, each key is related to ROWID, which provides a quick way to access the data
- can also define a column of the ROWID data type when the table is created, for example, can be used to store rows that violate integrity constraints (this place is not quite understood, the original is you can also create tables with columns defined using the ROWID data type. For example, can define a exception table with a column of data type ROWID to store the rowids of rows that violate I ntegrity constraints. Columns defined using the ROWID data type behave like other table columns:values can is updated, and so on).
- rowid pseudo-column
- each table has, but is not stored in a table, ROWID pseudo-columns cannot be inserted, update the delete operation as if the SQL function has no 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
- An integrity constraint is a rule that restricts the data in one or more columns, which prevents illegal data from entering the table, or prevents dependent data from being deleted
- If the integrity constraint is enabled, the database checks for updated or inserted data. If the integrity constraints are disabled, you will not be able to prevent illegal data from being updated and inserted
- Constraints can be temporarily disabled when a table is created or after a constraint is added. The database stores the integrity constraints in the data dictionary
Object tables
- The object type in Oracle is a user-defined type, including the name, attributes, and methods.
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‘);
Temp table
- The data for a temporary table exists only in a session or transaction, and the session's temporary data is available only to that session, which is private
- Using the Create GLOBAL temporary table, a temporary table can be created using the on commit clause to refer to whether the data for the staging table is transaction-specific (default) or session-specific
- Unlike other databases, Oracle temporal tables are statically defined and exist in long-term data dictionaries
- Because it is statically defined, you can create an index (as temporary), or you can create a view or trigger
- Allocation of temporary segments: only when the data is first inserted, in the temporary table of Transaction-specific, the temporary segment is released at the end of the transaction, and in the temporary table in Session-specific, the temporary segment is released at the end of the session
External table
- External tables get data from external data sources, making these external data like in the database.
- Access to flat files is very useful
- External tables are useful for data Warehouse environments where ETL work is required
- Creating an external table means that you want to put its metadata in a data dictionary, typically using the CREATE TABLE ... ORGANIZATION external Statements
- External Table Access Driver (External table Access Drivers): is an API that the database uses to access external table data
- ORACLE provides two driver--oracle_loader (default) and Oracle_datapump drivers to access external tables
- Oracle_loader using the SQL * Loader tool to read-only access to external tables
- Oracle_datapump can unload external table data, which includes the process of reading database data and inserting it into an external table, and when the external table is created, it is not possible to update and add data; Similarly, it allows you to load data from external tables
Table Storage
- Oracle uses data segments in the tablespace to store data for the table, and a data segment includes a chunk of data blocks (described in more detail in the Logical Store results section)
- Table Organization
- In general, tables are organized in heaps, which means that data rows are not sequential, but how best to fit on the physical structure
- Tables can hold pseudo-columns (no space), pseudo-columns are usually expressions or functions that create indexes for shoddy, collect their statistics, and create integrity constraints
- Row storage
- The database stores rows in a block of data, and each row in the table, as long as no more than 256 columns (attributes), contains one or more row pieces
- If possible, Oracle stores each row on a row piece. However, if a row of data cannot be stored in a block of data, or if the amount of data is increased after the update, 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 that is the physical address of the line. Each row has a unique rowid that points to its row piece
- In table cluster, rows in different tables can have the same rowid if they are in the same block of data
- Oracle database uses 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. (This paragraph is not good translation, or directly given)
- If the null value is between columns that have data values, 1byte space is required to store the length of the column
- If NULL is at the end of a line, no storage space is required because the next line of the outfit marks the end of the last row with a null value for the remaining column
Table compression
- The database uses table compression to reduce the storage space of the table, and compression reduces the amount of space used in the in-memory database buffer cache and, in some cases, increases the query speed. Table compression is transparent to database applications
- Basic and Advanced Row compression
- Basic Table Compression: This method is used for operations of large amounts of data and does not compress data modified with conventional DML. You need to use the direct path loads method, ALTER TABLE ... MOVE action, or online table redefinition to achieve basic compression
- Advanced Row compression: Designed for OLTP applications that compress data that is manipulated by SQL
- Under these two types of compression, the database stores the compressed rows in the Row-major format. All columns in each row will be stored together
- Duplicate values are replaced by a short reference to the size of the data. Therefore, the information that needs to be recreated for non-compressed data will be stored in the data block (very awkward, I do not quite understand at the moment, this place is the original: for basic and Advanced row compression, the database stores Compressed rows in row-major format. All columns of one row is stored together, followed by all columns of the next row, and so on (see figure 12-7). Duplicate values is 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 are stored in the data block itself.)
- We can also artificially set the compression
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
- The same columns are stored together in a set of rows, and the data blocks are not stored in the row-major format, but instead use a combination of a row and cylinder (columnar) method
- Hybrid types of columnar compression
- Warehouse compression
- Online Archival compression
- These two classes also need to use direct path loads, ALTER TABLE ... Move action or online table redefinition
- Hybrid Columnar compression optimizes data warehousing and decision support applications in exadata storage devices. Other storage systems can also support this compression, but do not provide the same efficient query as Exadata
- Compression unit (Compression Units)
Table clusters overview
- is a set of table shared columns, and is stored in the same data block. Assuming that there are two tables employees and departments, the same columns are department_id, then department_id is shared between the two tables, and the corresponding data for both tables will be in the same data block
- Cluster key: Same column, such as department_id (of course, if more than one column is also possible)
- Cluster key value: Values of the same column
- Compared to the first class of the table, there are the following benefits:
- When you do a join operation, you can reduce the IO
- Faster access time when you are doing a JOIN operation
- The space used to store tables and indexes is reduced
- Non-recommended cases:
- Frequently-Updated tables
- Tables that often require full table scans
- Tables that require TRUNCATE
Indexed Clusters
Hash clusters overview
- Like index cluster, the index key is replaced with a hash function, as a hash key
- Recommended Use scenarios:
- Query much more than modify
- Hash key is often used for equivalent queries
code example:
CREATE CLUSTER employees_departments_cluster (department_id number (4))SIZE 8192 hashkeys ; 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< /c4> d.department_id =:p _id;
- There are two variants.
- Single-table Hash Cluster
- Sorted hash Cluster
2 Tables and Table clusters reading notes
