Partitions, views, and Other SchemaObjects in Oracle

Welcome to the Oracle community forum and interact with 2 million technical staff. Although tables and indexes are the most important and common schemaObjects, there are still many other schemaobjects databases. The most common ones are described in this chapter. This chapter includes the following sections :? OverviewofPartitions (partition overview )? OverviewofVie

Welcome to the Oracle community forum and interact with 2 million technical staff> although tables and indexes are the most important and commonly used schema Objects, there are still many other schema objects in the database. The most common ones are described in this chapter. This chapter includes the following sections :? Overview of Partitions (partition Overview )? Overview of Vie

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Although tables and indexes are the most important and commonly used schema Objects, there are still many other schema objects in the database. The most common ones are described in this chapter.

This chapter includes the following sections:

? Overview of Partitions (partition Overview)

? Overview of Views (view Overview)

? Overview of Materialized Views (Materialized view Overview)

? Overview of Sequences)

? Overview of Dimensions (latitude Table Overview, what is in data warehouse)

? Overview of Synonyms (Synonym Overview)

　　Overview of Partitions (partition Overview)

Partitioning allows you to split large tables and indexes into many small fragments with higher manageability. These fragments are called partitions.

Each partition is an independent object with its own name and storage attributes.

Give a life case to explain the partition

Assume that the human resources manager has an oversized box containing employee folders, each of which corresponds to the employee's employment date.

It is often necessary to query who is hired in the specified month. One way is to create an index on the employee's employment date, and record the locations of folders scattered in the box in order.

In comparison, the partitioning policy uses many small boxes, each of which stores records of people hired in a specified month.

There are several advantages to using a small box. When someone asks the folder of the hired person in February, the HR manager can find it directly in the box in February. Furthermore, if any small box is damaged temporarily, other small boxes can still be used normally at this time. It is easy to move the office, from moving a super-large box to moving many small boxes.

From the application perspective, there is only one large table. After the table is partitioned, the related statements do not need to be modified.

Partitions are suitable for multiple database application types, especially those with a large amount of data from managers.

Partition advantages include:

? Increase availability (increased availability)

The unavailability of a partition does not result in unavailability of the entire object. The query optimizer automatically removes related partitions from the execution plan. Therefore, some partitions are damaged and the query will not be affected.

? Easier administration of schema objects (simplified object management)

Objects after a partition can be managed as a whole or for a partition.

DDL statements can only operate on a partition. Therefore, you can split and execute resource-intensive tasks. For example, re-indexing or recreating a table.

For example, you can directly move the table partition. If something goes wrong, only the partition needs to be moved again instead of the entire table. Similarly, deleting a partition can avoid executing a large number of DELETE statements.

? Protected CED contention for shared resources inOLTP systems (reduce contention for shared resources in the OLTP system)

In some OLTP systems, partitions can reduce competition for shared resources.

For example, DML operations are dispersed in multiple segments, rather than in one segment.

? Enhanced query performance in datawarehouses (improves the query performance of data warehouses)

In a data warehouse, partitions can accelerate the processing of some special queries.

For example, a sales record table with N million records can be divided into four partitions.

　　Partition characteristics (features of partitions)

Each partition of a table and an index must have the same logical attributes (attributes), such as column names, data types, and constraints.

For example, all partitions in a table share the same column and constraint definition, while all partitions in the index share the same index column. However, each partition has its own physical attributes, such as the tablespace to which it belongs.

　　Partitioning Key (partition key)

The partition key is one or more columns. Used to determine the partition to which each row in the Partition Table should go.

Each row can belong to only one partition.

In the sales table, you can specify that the time_id column is the key of range partition. The partition to which the row in the database allocation table belongs. This is based entirely on whether the time_id value of this row belongs to this partition range.

The Oracle database automatically performs DML on the appropriate partition based on the partition key in the condition.

　　Partitions Strategies)

Oracle provides some partitioning solutions to determine how data is partitioned.

The basic solution is range, list, and hash partition.

Single-level (Single-level) Partitioning policy only uses one of these data distribution methods.

For example, you can use only range partitions or list partitions.

In Composite partitioning, each partition is partitioned according to one method, and then each partition is further divided into subpartitions.

Example: You can use the channel_id column to list partitions. Then use time_id for range subpartitioning.

　　Range Partitioning)

In a range partition, the database determines the row ownership based on the range specified by the partition key.

Range partitions are most commonly used in the date type.

[1] [2] [3] [4]