Introduction to Oracle solution objects

 

Introduction to solution objects

 

Schema is a set of Logical Data Structures (or schema objects.Each database user has a solution with the same name. Solution objects can be created and operated through SQL statements.

 

The object types that can be included in the solution include:

Cluster)

Database Link)

Database trigger)

Dimension)

External procedure Library)

Index and index type)

Java class, Java resource, and Java source)

Materialized View (materialized view) and materialized view log (materialized view log)

Object table, object type, and object view)

Operator)

Sequence)

Stored functions, procedure, and Package)

Synonym (Synonym)

Table and index-organized table

View)

 

Oracle logically stores the solution object in the table space of the database, and the data of the solution object is physically stored in one or more datafiles of the tablespace.

 

1. Table Overview

Table Storage:When you create a table, Oracle automatically allocates data segments to the table in the corresponding tablespace to accommodate the data.

Note: you cannot set the storage parameter for a cluster Table when creating or modifying it ). All cluster tables in a cluster use the storage parameters of the cluster to control their space allocation.

Row Storage: Oracle uses one or more row fragments to store the first 255 columns of each row of data in the table.

When Oracle must use multiple row fragments to store the same row (and each row segment is in a different data block ), this row will form a row Link (row chaining) between multiple data blocks)

When a table contains more than 255 columns, the data after 255th columns in each row is stored as a new row piece in the same data block, this is called intra-block chaining ).

Note: The row links between different blocks increase the I/O overhead, and the intra-block links do not affect the I/O overhead (Consistent rowid)

Null storage:To save storage space, if a column value is null, the length of the column (with a value of 0) is stored in the database in only one byte, without storing any data. For the trailing null column at the end of a row, the column length is ignored in the database.

Rowid in a row segment: after each row segment obtains a rowid, the value remains unchanged until the row is deleted or exported and imported again by Oracle.

Column order:Columns of a table are generally stored in the order of table creation. The exception is that a table contains columns whose data type (datatype) is long. Oracle stores this column at the end of the row. When you modify the table definition and add new columns to it, these columns will also be stored at the end of the row.

Processing of null columns:Generally, columns that contain many null values are placed at the end.

Table compression:Bulk insert or bulk load. You can use the alter table... Move statement to convert existing data in the database to the compression mode.

 Session-related temporary tables:The truncate statement executed for the session-related temporary table only clears the data of the session, the temporary table does not clear data that belongs to other sessions.

 Temporary table space allocation:When creating a temporary table and a temporary index, Oracle does not allocate segments for it. segments are allocated when the first insert (or create table as select) Statement is executed.

Temporary tables related to transactions:If the insert operation is performed on the temporary table in the child transaction, the existing data in the temporary table is cleared.

External table:The default type is oracle_loader. You can use this type to read data from external tables and load the data to the database.

Oracle also provides the oracle_datapump type. You can use this type to first Load External tables (unload data) and then load the data to another database.

 

2. View Overview

1. Views and tables:All modifications to the view data are eventually reflected in the base table of the view. These modifications must comply with the integrity constraints of the base table and also trigger triggers defined on the base table.

All View queries are eventually converted into base tables as much as possible, that is, the base table index is used as much as possible.

2. Materialized View:A solution object that can be used to summarize, compute, copy, and publish data. It is applicable to data warehouses, decision support, distributed computing, mobile computing, and other environments.

 

3. Sequence

Sequence storage: Oracle stores all sequence definitions in a database in a data dictionary table in the system tablespace. Since the system tablespace is always online, the definition of all sequences is always available.

Sequence usage: sequence numbers generated by different tables for the same sequence object are independent of each other.

If the user's application does not allow missing serial numbers, the Oracle sequence should not be used. In this case, the serial number should be stored in the database table, but proceed with caution.

Sequence disconnection problem: http://zhang41082.itpub.net/post/7167/496340

 

 

4. Synonyms

Synonyms are used to hide the name and owner of a database object, hide the location of remote objects in a distributed environment, simplify SQL statements of database users, and restrict access to a view, for more precise access control.

 

 

5. Index Overview

Oracle provides various types of indexes that complement each other to improve query performance:

Balance Tree Index (B-Tree Index)

B-tree Cluster Index)

Hash Cluster Index)

Reverse key indexes)

Bitmap Index)

Bitmap join Index)

Function-based index)

Domain Index)

Constraints and indexes:Oracle recommends that you use the create unique index statement to explicitly create a unique index. Creating a unique index using a primary key or unique constraint cannot guarantee the creation of a new index, and the indexes created using these methods cannot be unique indexes.

Null and index:Oracle does not add all data rows with null index columns to the index. However, bitmap indexes are an exception. If all the column values of the cluster key are null, they are also an exception.

Function Index storage:Function indexes can calculate the value of a function or expression and store it in the index.

Conditions for using function indexes:After an index is created, the table must be analyzed (analyze). You must ensure that the query condition expression is not a null value because the null value is not stored in the index.

Internal indexing operations:When you create an index, Oracle retrieves and sorts all the data in the indexed columns, then, the sorted index value and the rowid corresponding to this value are loaded to the index in the ascending order.

Index composition-index block:

Branch Block: The minimum key-value prefix (minimum key prefix), used to select a branch between two key values; pointer to the Child block containing the searched key value;

Leaf block: the key value of the data row. The key value corresponds to the rowid of the Data row.

Reverse key index:The reverse order here refers to the reverse order of each byte of the index key value, rather than the reverse order of the index key.

Creation and modification of reverse-order key indexes:Create index I on T (a, B, c) reverse; Alter index I rebuild noreverse;

Advantages of Bitmap indexes in data warehouses:It can reduce the response time of user-defined queries with large data volumes. Compared with other indexing technologies, it can save a lot of storage space. Even if the hardware configuration is low, it can significantly improve the performance. It is conducive to parallel DML and parallel loading.

Use Bitmap indexes:The bitmap index is not suitable for comparing or greater than a column. For example, the where clause usually compares the salary column with a value, which is more suitable for the use of the Balance Tree Index. Bitmap indexes are suitable for equivalent queries, especially when combinations of logical operators such as and, or, and not exist. ,

Null and bitmap Index: Unlike most other indexes, bitmap indexes can contain rows with null key values.

 

Regular table index table

 

Bytes -------------------------------------------------------------------------------------------------------------------------

 

The rowid is used to uniquely identify a row. The primary key (primary key) is optional.

 

The rowid virtual column stores the physical rowid. You can use this column to create an indirect index. The rowid virtual column stores the logical rowid. You can use this column to create an indirect index.

 

Table access is based on the physical rowid. Table access is based on the Logical rowid.

 

Using Full table scan, you can return all rows. Using Full-index scan, you can return all rows.

 

It can be used as a table in a cluster. It cannot be used as a table in a cluster.

 

A column with the Data Type of Lob or long can contain a column with the Data Type of lob, but cannot contain a column with the Data Type of long.

 

Certificate -------------------------------------------------------------------------------------------------------------------------------------------------

Non-key columns in the index table:You can remove infrequently used non-key columns from the index tree so that the index page can accommodate more data and use row overflow segments,

Because the index table stores all the data on the index page block, the tree will be relatively small. We can use the overflow clause to solve this problem.

A Data row is divided into two parts, which are stored in the index and overflow storage area segment.

A row of data can be divided into the following two parts: index entry, which contains all the column values of the primary key column, pointing to the physical rowid of some data overflow in this row, and user-selected non-key column values

Overflow part, which contains the column values of other non-key columns

Using the pctthreshold clause, you can set the percentage value of a data block capacity. Using the including clause, you can set a column name. In the create table statement, the non-key column that appears after the column name is set will be stored in the row overflow segment.

Different performance of indirect indexes in conventional tables and index tables:

Regular table:Scan the indirect index before obtaining the data blocks that contain the required data rows;

Index table:The accuracy of physical guess differs from that of physical speculation:

A. Without physical speculation, data access requires two index scans: first, indirect indexes are scanned, and then primary key indexes (primary key index) are scanned based on the results ).

B. If you use physical speculation and the speculative results are accurate, you need to first scan the indirect index for data access, and then perform I/O operations to obtain data blocks containing the required data rows.

C. if you use physical speculation and the speculative results are inaccurate, You need to first scan the indirect index for data access and execute the I/O operation based on the physical speculation to obtain the wrong data block, then scan the primary key index.

 

6. Clusters

Concept of cluster:A cluster consists of a group of data tables that have the same columns and are frequently used together. This group of tables shares a data block during storage)

Benefits of clusters:

The disk I/o Required for the join cluster Table is reduced.

The time required to connect to the cluster Table is reduced.

In a cluster, the cluster key value is the value of the key columns of each row. The same cluster key value used by each data row in Multiple Cluster tables in a cluster is stored only once in the cluster and Cluster Index. Therefore, compared with non-cluster tables, less storage space is required to store related tables and indexes in clusters.

 

7. Other Scheme object types. In addition, the first section introduces ....

 

 

The following describes the parsing process of the solution Object Name:

The name of the referenced scheme object in the SQL statement can be composed of multiple segments (piece), separated.

The following describes how to resolve an object name in Oracle:

1. Oracle first verifies the first segment of the Object Name referenced in the SQL statement. For example, in HR. Employees, the HR is the first segment. If the referenced name is composed of only one segment, this segment is considered as the first segment. 

A. Oracle first searches for objects whose names match the first segment of the object name in the current schema. If not, go to step B.

B. The Oracle search name matches the public synonym (Public synonym) of the first segment of the Object Name ). If not, go to Step C.

C. Solution for Oracle search to match the first segment of the Object Name. If such a scheme is found, return to step B and use the second segment of the object name to search for matched objects in the scheme. If no matched object exists in the solution, or the referenced object name is composed of only one segment, Oracle returns an error.

If no matching scheme is found in Step C, the referenced object name cannot be verified, and Oracle returns an error.

2. If there is a solution object referenced in the statement in the database, the solution object must also have the content represented by the remaining segment of the Object Name ..