Escuelas de pensamiento del marketing y su tipo de discurso

2.6.1 Column-Based and Row-Based Storage in the SAP HANA Database

The SAP HANA database supports both row-based and column-based storage. However, it is optimized for column storage.

Tables that are organized in columns are read optimized and have better compression rates than tables organized in rows. Furthermore, some features of the SAP HANA database, such as partitioning, are available only for column tables. Column-based storage is typically suitable for big tables with bulk updates. However, update and insert performance is better on row tables. Row-based storage is typically suitable for small tables with frequent single updates.

The following table outlines the criteria that you can use to decide whether to store your data tables as column tables or row tables:

Storage Type When to Use

Column store ● Calculations are typically executed on individual or a small number of columns.

● The table is searched based on the values of a few columns.

● The table has a large number of columns.

● The table has a large number of rows and columnar operations are required (aggregate, scan, and so on)

● High compression rates can be achieved because the majority of the columns contain only a few distinct values (compared to the number of rows).

Storage Type When to Use

Row store ● The application needs to process only one single record at one time (many selects and /or updates of single records).

● The application typically needs to access the complete record.

● The columns contain mainly distinct values so compression rate would be low.

● Neither aggregations nor fast searching are required.

● The table has a small number of rows (for example, configuration tables).

Note

● The SAP HANA database allows row tables to be joined with column tables. However, it is more efficient to join tables of the same storage type.

● It is possible to change an existing table from one storage type to the other (ALTER TABLE ALTER TYPE).

Related Information

ALTER USER

2.6.1.1 Creating Tables

A table is a two dimensional data structure with cells organized in rows and columns. In order to load data into the SAP HANA database, you need to create tables. The tables can be row store or column store depending on the use case.

Prerequisites

To create a table, you must be authorized to create objects in the selected schema.

Procedure

1. In the Systems view, open the catalog schema in which you want to create the new table.

2. In the context menu of the schema in which you want to create the table, choose New Table 3. Enter the following information:

○ Table name

○ Table type (column store or row store) 4. Define the columns of your table as follows:

a) Enter the name and properties of the first column.

b) To add further columns, choose the button.

5. If necessary, add indexes.

a) On the Indexes tab, choose the button.

b) Specify the name and the index type (standard index or full-text index).

A full-text index enables full-text search.

c) In the lower part of the screen, define the index for the required column(s), together with any other necessary parameters.

Note

You can create an index for a table any time either by right-clicking the table in the Systems view and choosing New Index, or opening the table definition for editing.

Indexes are added to the table definition and in the schema's Indexes folder.

6. To create the table, choose (Create Table).

Results

The table appears in the Tables folder of the relevant schema.

Note

For more information creating and defining tables, loading data into tables, creating full-text indexes, and so on, see the SAP HANA Developer Guide and the SAP HANA SQL Reference.

Related Information

SAP HANA SQL and System Views Reference SAP HANA Developer Guide

2.6.1.2 Creating Views

A view is a combination or selection of data from tables modeled to serve a particular purpose. Views appear like readable tables, in other words, database operations that read from tables can also be used to read data from views. For example, you can create a view that simply selects some columns from a table, or a view that selects some columns and some rows according to a filter pattern.

Prerequisites

To create a view, you must be authorized to create objects in the selected schema and to select data from the tables to be included in the view.

Procedure

1. In the Systems view, open the catalog and navigate to the Views folder in the relevant schema.

2. In the context menu, choose New View.

The editor for creating a new view opens.

3. Specify the view name.

4. Select the relevant tables by dragging them from the Systems view into the editor area, or by choosing the (Insert) button in the editor.

5. To create a join, proceed as follows:

a) Drag a column from one table to the column of another table.

b) Choose the join type in the Join Order area.

If you define more than one join, you can define the order in which the joins are executed using drag and drop.

6. Drag and drop the columns to be contained in the result set into the Columns area.

You can specify additional constraints or create synonyms for column names here.

7. To preview the data, choose Data Preview in the context menu of the editor.

8. To show the equivalent SQL statement, choose Export SQL in the context menu of the editor.

9. To create the view, choose the (Execute) button.

Results

The view appears in the Views folder of the relevant schema.

Related Information

SAP HANA SQL and System Views Reference SAP HANA Developer Guide

2.6.2 Memory Management in the Column Store

The column store is the part of the SAP HANA database that manages data organized in columns in memory.

Tables created as column tables are stored here.

The column store is optimized for read operations but also provides good performance for write operations. This is achieved through 2 data structures: main storage and delta storage.

The main storage contains the main part of the data. Here, efficient data compression is applied to save memory and speed up searches and calculations. Write operations on compressed data in the main storage would however be costly. Therefore, write operations do not directly modify compressed data in the main storage.

Instead, all changes are written to a separate data structure called the delta storage. The delta storage uses only basic compression and is optimized for write access. Read operations are performed on both structures, while write operations only affect the delta.

Figure 13: Main Storage and Delta Storage

The purpose of the delta merge operation is to move changes collected in the delta storage to the read-optimized main storage. After the delta merge operation, the content of the main storage is persisted to disk and its compression recalculated and optimized if necessary.

A further result of the delta merge operation is truncation of the delta log. The delta storage structure itself exists only in memory and is not persisted to disk. The column store creates its logical redo log entries for all operations executed on the delta storage. This log is called the delta log. In the event of a system restart, the delta log entries are replayed to rebuild the in-memory delta storages. After the changes in the delta storage have been merged into the main storage, the delta log file is truncated by removing those entries that were written before the merge operation.

Note

As only data in memory is relevant, the load status of tables is significant. A table can have one of the following load statuses:

● Unloaded, that is, none of the data in the table is loaded to main memory

● Partly loaded, that is, some of the data in the table is loaded to main memory, for example, a few columns recently used in a query

● Fully loaded, that is, all the data in the table is loaded into main memory

However, data that is in the delta storage can only be fully loaded or unloaded. Partial loading is not possible.

Therefore, if a delta merge has not been performed and the table's entire data is in the delta storage, the table is either fully loaded or unloaded.