the differences between transparent, pooled, and cluster tables

In R/3 there are three table types: transparent tables, pooled tables, and cluster tables. They are shown in Figure 3.1.

Figure 3.1 : The three table types in the R/3 Data Dictionary.

Transparent Tables

A transparent table in the dictionary has a one-to-one relationship with a table in the database. Its structure in R/3 Data Dictionary corresponds to a single database table. For each transparent table definition in the dictionary, there is one associated table in the database. The database table has the same name, the same number of fields, and the fields have the same names as the R/3 table definition. When looking at the definition of an R/3 transparent table, it might seem like you are looking at the database table itself.

Transparent tables are much more common than pooled or cluster tables. They are used to hold application data. Application data is the master data or transaction data used by an application. An example of master data is the table of vendors (called vendor master data), or the table of customers (called customer master data). An example of transaction data is the orders placed by the customers, or the orders sent to the vendors.

Transparent tables are probably the only type of table you will ever create. Pooled and cluster tables are not usually used to hold application data but instead hold system data, such as system configuration information, or historical and statistical data.

Both pooled and cluster tables have many-to-one relationships with database tables. Both can appear as many tables in R/3, but they are stored as a single table in the database. The database table has a different name, different number of fields, and different field names than the R/3 table. The difference between the two types lies in the characteristics of the data they hold, and will be explained in the following sections.

Table Pools and Pooled Tables

A pooled table in R/3 has a many-to-one relationship with a table in the database (see Figures 3.1 and 3.2). For one table in the database, there are many tables in the R/3 Data Dictionary. The table in the database has a different name than the tables in the DDIC, it has a different number of fields, and the fields have different names as well. Pooled tables are an SAP proprietary construct.

When you look at a pooled table in R/3, you see a description of a table. However, in the database, it is stored along with other pooled tables in a single table called a table pool. A table pool is a database table with a special structure that enables the data of many R/3 tables to be stored within it. It can only hold pooled tables.

R/3 uses table pools to hold a large number (tens to thousands) of very small tables (about 10 to 100 rows each). Table pools reduce the amount of database resources needed when many small tables have to be open at the same time. SAP uses them for system data. You might create a table pool if you need to create hundreds of small tables that each hold only a few rows of data. To implement these small tables as pooled tables, you first create the definition of a table pool in R/3 to hold them all. When activated, an associated single table (the table pool) will be created in the database. You can then define pooled tables within R/3 and assign them all to your table pool (see Figure 3.2).

Figure 3.2 : Pooled tables have a many-to-one relationship with table pools.

Pooled tables are primarily used by SAP to hold customizing data.

When a corporation installs any large system, the system is usually customized in some way to meet the unique needs of the corporation. In R/3, such customization is done via customizing tables. Customizing tables contain codes, field validations, number ranges, and parameters that change the way the R/3 applications behave.

Some examples of data contained in customizing tables are country codes, region (state or province) codes, reconciliation account numbers, exchange rates, depreciation methods, and pricing conditions. Even screen flows, field validations, and individual field attributes are sometimes table-driven via settings in customizing tables.

During the initial implementation of the system the data in the customizing tables is set up by a functional analyst. He or she will usually have experience relating to the business area being implemented and extensive training in the configuration of an R/3 system.

Table Clusters and Cluster Tables

A cluster table is similar to a pooled table. It has a many-to-one relationship with a table in the database. Many cluster tables are stored in a single table in the database called a table cluster.

A table cluster is similar to a table pool. It holds many tables within it. The tables it holds are all cluster tables.

Like pooled tables, cluster tables are another proprietary SAP construct. They are used to hold data from a few (approximately 2 to 10) very large tables. They would be used when these tables have a part of their primary keys in common, and if the data in these tables are all accessed simultaneously. The data is stored logically as shown in Figure 3.3.

Figure 3.3 : Table clusters store data from several tables based on the primary key fields that they have in common.

Table clusters contain fewer tables than table pools and, unlike table pools, the primary key of each table within the table cluster begins with the same field or fields. Rows from the cluster tables are combined into a single row in the table cluster. The rows are combined based on the part of the primary key they have in common. Thus, when a row is read from any one of the tables in the cluster, all related rows in all cluster tables are also retrieved, but only a single I/O is needed.

A cluster is advantageous in the case where data is accessed from multiple tables simultaneously and those tables have at least one of their primary key fields in common. Cluster tables reduce the number of database reads and thereby improve performance.

For example, as shown in Figure 3.4, the first four primary key fields in cdhdr and cdpos are identical. They become the primary key for the table cluster with the addition of a standard system field pageno to ensure that each row is unique.

Figure 3.4 : The cdhdr and cdpos tables have the first four primary key fields in common and are always accessed together and so are stored in the table cluster cdcls.

As another example, assume the data from order header and order item tables is always needed at the same time and both have a primary key that begins with the order number. Both the header and items could be stored in a single cluster table because the first field of their primary keys is the same. When implemented as a cluster, if a header row is read, all item rows for it are also read because they are all stored in a single row in the table cluster. If a single item is read, the header and all items will also be read because they are stored in a single row.

Restrictions on Pooled and Cluster Tables

Pooled and cluster tables are usually used only by SAP and not used by customers, probably because of the proprietary format of these tables within the database and because of technical restrictions placed upon their use within ABAP/4 programs. On a pooled or cluster table:

• Secondary indexes cannot be created.
• You cannot use the ABAP/4 constructs select distinct or group by.
• You cannot use native SQL.
• You cannot specify field names after the order by clause. order by primary key is the only permitted variation.

 

 

转自: < Teach Yourself Abap4 In 21 Days >