Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about Oracle 12.2 how to enable In-Memory conversion filling of database objects, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

The so-called column transformation filling of the database is the process that the database reads the existing row format data from disk, converts it to column format, and then stores it in IM column storage. Populating database objects to column storage greatly improves access efficiency. Only objects with the In-Memory property can be populated with transformations.

The purpose of enabling column filling of objects

The IM column store does not automatically load all objects in the database into the IM column store. If you do not use DDL to specify any objects as INMEMORY, the IM column store remains empty. It is necessary to convert the rows of user-specified In-Memory objects to column format so that they can be used to parse queries.

The padding that converts existing data on disk to column format is different from what is commonly known as column padding, which simply loads new data into the IM column store. Because IMCU is a read-only structure, the Oracle database does not automatically populate rows when they change. In the former, the database records the row changes in the transaction log, and then creates a new IMCU as part of the IM.

The principle of column conversion filling

You can specify that the database populates the INMEMORY column store with objects when the instance starts or when the IM object is accessed. Column transformation filling algorithm is different in single instance database and RAC.

The DDL statement includes an INMEMORY PRIORITY clause that provides more control over the clause queue.

Priority settings apply to entire tables, partitions, or subpartitions, not to different subsets of columns. Setting the INMEMORY property on an object means that the object is a candidate for padding in the IM column store. This does not mean that the database populates the object immediately. Oracle database administration priorities are as follows:

1. Fill on demand

By default, the INMEMORY PRIORITY parameter is set to NONE. In this case, the database populates the object only when it is accessed through a full table scan. If the object is never accessed, or only through an index scan or through rowid, the conversion does not occur.

2. Priority-based filling

When PRIORITY is set to a non-none value, the Oracle database automatically populates objects with internally managed priority queues. In this case, full scan is not a necessary condition for filling.

The priority-based filling process is as follows:

A. Automatically populate the column data in the IM column after the database instance is restarted

B. Queue population based on the specified priority INMEMORY object

For example, a table changed with INMEMORY PRIORITY CRITICAL takes precedence over a table changed with INMEMORY PRIORITY HIGH, which in turn takes precedence over a table changed with INMEMORY PRIORITY LOW. If the IM column does not have enough storage space, the Oracle database will not populate other objects until space is available.

C. Wait for the return from the ALTER TABLE or ALTER MATERIALIZED VIEW statement until changes to the object are recorded in the IM column store

After a segment is populated in the IM column store, the database pushes the segment away only when it is deleted or moved, or updates the segment using the NO INMEMORY property. Subdivisions can be expelled either manually or through ADO policies.

Before completing this example, you must enable IM column storage for the database.

1. Log in to the database as an administrator, and then query the customer table as follows:

2. Display the execution plan of the query:

3. Enable the population of the sh.customers table in the IM column store:

4. To determine whether the data in the sh.customers table has been populated in the IM column store, execute the following query (including sample output):

In this case, because the sh.customers table has not been scanned, no fragments are populated in the IM column store.

5. Query sh.customers using the same statement as in step 1:

6. Query the cursor to display the database to perform a full scan and access the IM column storage:

7. Query V$IM_SEGMENTS again (including sample output):

8. The DBA_FEATURE_USAGE_STATISTICS view confirms that the database uses IM column storage to retrieve the results:

How background processes populate IMCU

During population, the database reads data from disk in its row format, pivots rows to create columns, and then compresses the data into an in-memory compression unit (IMCU).

The worker process (Wnnn) populates the data in the IM column store. Each worker process runs on a subset of the object's database block. Population is a streaming mechanism that compresses data and converts it to a columnar format.

The INMEMORY_MAX_POPULATE_SERVERS initialization parameter specifies the number of worker processes to be used for the IM column storage community. By default, this setting is half that of CPU_COUNT. Set this parameter to a value appropriate to your environment. More work processes lead to population growth, but consume more CPU resources. Fewer work processes lead to a smaller population, which reduces CPU overhead.

Column filling control

Use the INMEMORY clause in the data definition language (DDL) statement to specify which objects are eligible to enter the IM column store. Table spaces, tables, partitions, and materialized views can be enabled.

INMEMORY clause

INMEMORY is a segment-level attribute, not a column-level attribute. However, you can apply the INMEMORY attribute to a subset of columns in a specific object.

To enable or disable objects stored in the IM column, specify the INMEMORY clause in any of the following statements:

1 、 CREATE TABLESPACE or ALTER TABLESPACE

By default, all tables and instantiated views in the tablespace are enabled for IM column storage. Individual tables and instantiated views in a tablespace may have different INMEMORY properties. The properties of a single database object override the properties of the tablespace.

2 、 CREATE TABLE or ALTER TABLE

By default, the IM column stores all non-virtual columns in the populated table. You can specify all or part of the columns of the table. For example, you can exclude the weight_class and catalog_url columns in oe.product_information from eligibility. For partitioned tables, you can populate all or part of the partitions in the IM column store. By default, for partitioned tables, all table partitions inherit the INMEMORY property.

3 、 CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW

For partitioned materialized views, you can populate all or part of the partitions in the IM column store.

The INMEMORY column in the DBA_TABLES view indicates which tables have INMEMORY property settings (ENABLED) or are not set (DISABLED).

The following objects are not eligible for filling in the IM column store:

Indexes; index organization tables; hash clusters; objects owned by SYS users and stored in SYSTEM or SYSAUX tablespaces.

If a table is enabled for the IM column store and it contains any of the following types of columns, these columns will not be populated in the IM column store:

Rows and columns (varrays, nested table columns and out-of-row LOB)

Columns that use LONG or LONG RAW data types

Extended data type column

Column fill the table

Suppose you connect to the database as user sh. You can use the default compression level FOR QUERY LOW (see "Compression in memory") to enable the customer table for the user table in the IM column store:

Priority options for column padding

When you enable database objects for the IM column store, you can enable the Oracle database to control the filling of objects in the IM column store (the default), or you can specify the priority of the determined objects.

Oracle SQL includes an INMEMORY PRIORITY clause that gives the group more control over the queue. For example, data that populates database objects may be more or less important before populating data from other database objects.

The following table describes the supported priorities.

After reading the above, do you have any further understanding of how Oracle 12.2 enables In-Memory transformation of database objects? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.