What are the oracle objects?

Oracle objects include: 1. Table; 2. Index; 3. Cluster; 4. View; 5. Synonym; 6. Sequence; 7. Process ( Procedure) and function; 8. Triggers; 9. Constraints, etc.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

What are Oracle’s database objects:

The function of the database is to organize, manage and store various database objects. The objects in the database are the basis for our data management. This article will review some basic knowledge of database objects to give us a clearer understanding of these Oracle database objects.

1. Table:

When we operate the database, most of it is done by operating tables. Table is a logical concept and the basic unit for database organization and management of data.

Tables can be divided into relational tables and object tables. Relational tables include heap tables, index-organized tables, and external tables. What we commonly use is a heap table.

The segments corresponding to the heap table are stored in the form of a heap structure, and the stored data is logically out of order.

The corresponding relationship between tables and segments is as follows

For non-partitioned tables, one table corresponds to one segment;

For partitioned tables, one partition corresponds to one segment;

For tables with sub-partitions, one sub-partition corresponds to one segment;

Other database objects are similar to this

1.1 Table partition
Table partition (Partition), According to the user's data situation and business needs, the data in the table is stored in multiple segments from one segment to facilitate the management and maintenance of user data and improve the performance of query operations. Of course, there are also some shortcomings. If used improperly, some performance problems will occur; applying partition tables requires more experience and more comprehensive considerations, which also puts forward higher requirements for developers.

When to apply partitioning:

The first thing to consider is the amount of data. Partitioning is necessary only if the amount of data is large enough. Partitioning is not necessary for a small amount of data. Oracle officially recommends that when the storage space occupied by the table exceeds 2GB, you can consider partitioning the table. Generally speaking, try to avoid partitioning, and there must be a good reason for partitioning.
If the current non-partitioned table has affected the user's management and maintenance of data, partitioning can be considered.
Whether the user's query and operation performance can be improved after partitioning.
Regarding how to partition, we will continue to analyze it in the following articles.

2. Index:

The index is a logical object built on the table. Indexes can improve the efficiency of table data access and query, and play a great role in performance optimization. The index is also associated with one or more segments, and the final storage location of the index is also a segment. Different types of indexes have different storage logical structures, such as B-tree, Bitmap, etc.

Indexes are optional for tables, and creating appropriate indexes is the top priority of database optimization. However, indexes can improve query efficiency, but also reduce the efficiency of DML operations. Only when the two are taken into consideration will better performance be achieved.

Regarding index database optimization solutions, most of them are to avoid index failure, optimize the order of index use, etc. Indexes such as B* tree index and bitmap index will be discussed in detail in subsequent articles.

3. Cluster:

A cluster is a database object that contains data from one or more tables. The included tables All have one or more columns in common, these columns are called cluster key columns.

Creating a cluster requires corresponding permissions and is rarely used in development. Clusters can be divided into index clusters and hash clusters. The difference is that when searching for data, the former uses the index on the cluster key column, while the latter uses the hash value of the cluster key column. Which type to use depends on the usage scenario.

4. View (View) & Materialized View (Materialized View):

View is a virtual defined logical object, mainly used To simplify business logic and facilitate development and maintenance, the view does not contain any data, and its data is based on other objects corresponding to the view.

The view can provide some operations of adding, deleting, modifying and checking, and at the same time it has a certain degree of security. It can block some columns and is more flexible to use. But there will be some impact on performance.

Materialized views, compared to views, materialized views can actually store data and correspond to related segments like tables.

Materialized views can be used for summary, calculation and other services. At the same time, additions, deletions, modifications, and searches can also be performed under certain conditions, and indexes can also be established.

5. Synonym:

The synonym is also a virtual logical object and does not store any data. Essentially it is an alias for other data objects. At the same time, the permissions of synonyms can be assigned to different users as a way of security management.

6. Sequence:

The sequence does not store any data, and the user can obtain a series of ordered values ​​through the sequence.

When defining a sequence, you can define the sequence name, ascending and descending order, step size, etc. If the load concurrency is high, the growth of the sequence will affect the overall performance.

7. Procedure & Function:

Procedures and functions are virtual logical objects and do not store data. The main function is to use database-encoded calls to perform a series of tasks.

Processes and functions are objects in the database, consisting of a set of SQL or some other PL statements. A unit written to solve a specific problem.

The difference is that the function has a return value. Except for this, the procedure and the function are the same in other aspects.

8. Trigger:

The trigger is also a logical object of the database and does not store data. Mainly through database coding, a set of commands are executed when the event is automatically triggered.

The execution process is automatic. When an event triggers relevant conditions, it will be executed.

9. Constraint:

Constraints are logical objects in the database, and their functions are determined through some internal or automatic logic. Implement inspection and restriction of data to make it comply with certain rules or standards. This enables the regularization and standardization of data.

Common constraints include

• Primary key constraint

• Unique constraint

• ## Non-null constraints

• Foreign key constraints

• Custom constraints (check constraints)

Recommended Tutorial: "

Oracle Tutorial"

The above is the detailed content of What are the oracle objects?. For more information, please follow other related articles on the PHP Chinese website!