Pool table & Cluster table

[Former Member]

Dear all,

could you please help me out from the below.

How to create pooled tables & cluster tables.

When i am trying to create a table, by default it is showing table category as Transparent table.

Regards

Venkat

[Former Member]

hi,

A pool table has many to one relation with the table in the database. For one table in the database there are many tables in the dictionary. Tha table in the database has a diff name than in the table in the data dict, it has diff no of fields and field names are different. A pooled table is stored in the pool at the database level. A table pool is a databse table with a special struct that enables the data of many R3 tables to be stored in it. It can hold only pooled tables

Cluster table are logical tables that must be assigned to a table cluster when they are defined.

Cluster table can be used to store control data they can also used to store temporary data or text such as documentation

Pool table

A database table defined in the ABAP Dictionary whose database instance is assigned to more than one table defined in the ABAP Dictionary. Multiple pool tables are assigned to a table pool in the database. The key fields of a pool table have to be character-type fields. The table pool's primary key consists of two fields: TABNAME for the name of a pool table, and VARKEY for the interdependent contents of the key fields in the corresponding pool table. The non-key fields of the pool table are stored in compressed format in their own column, called VARDATA, of the table pool. The only way to access pool tables is by using Open SQL. Joins are not allowed.

Table Pool

Database table in the database that contains the data of several pool tables.

Cluster Table

Database table defined in the ABAP Dictionary, whose version on the database is not only assigned to one table defined in the ABAP Dictionary. Several cluster tables are assigned to a table cluster in the database. The intersection of the key fields of the cluster tables forms the primary key of the table cluster. The other columns of the cluster tables are stored in compressed form in a single column VARDATA of the table cluster. You can access cluster tables only via Open SQL, and only without using joins.

Table Cluster

Database table in the database that contains the data of several cluster tables.

Note: Never mix up with a database table that has the necessary structure for storing data clusters in database tables and in the shared memory. Those are called INDX-type, with reference to the database table INDX supplied by SAP. Data clusters are groupings of data objects for transient and persistent storage in a selectable storage medium. A data cluster can be processed using the statements IMPORT, EXPORT, and DELETE FROM

Some pooled tables:

T000 Clients

T000C Table for Installing FI-SL Customizing

T000CM Client-specific FI-AR-CR settings

T000F Cross-Client FI Settings

T000G Cross-Client FI-SL Postings

T000GL Flexible general ledger: Customizing check

T000K Group

T000MD MRP at MRP Area Level

T001 Company Codes

T001_ARCH Archive contents short description

T001_CONV Company codes affected by currency convers

T001A Additional Local Currencies Control for Co

T001B Permitted Posting Periods

T001C Valid Posting Periods for Global Companies

T001CM Permitted Credit Control Areas per Company

T001D Validation of Accounting Documents

T001E Company Code-Dependent Address Data

T001F Company code-dependent form selection

T001G Company Code-Dependent Standard Texts

T001I Company Code - Parameter Types

T001J Company Code - Parameter Type Names

T001K Valuation area

T001L Storage Locations

T001M Data on Z5A Foreign Trade Regulations Repo

T001N Company Code - EC Tax Numbers / Notificati

A physical table definition is created in the database for the table definition stored

in the ABAP Dictionary for transparent tables when the table is activated.

The table definition is translated from the ABAP Dictionary to a definition of the particular database.

On the other hand, <b>pooled tables and cluster tables are not created in the database.</b>The data of these tables is stored in the corresponding table pool or table cluster.

It is not necessary to create indexes and technical settings for pooled and cluster tables.

regards,

pritha

[Former Member]

Hi Venkat,

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb13446011d189700000e8322d00/frameset.htm

Creating Pooled Tables/Cluster Tables

Procedure

In the initial screen of the ABAP Dictionary select object type Table, enter a table name and choose Create.

The field maintenance screen for the table is displayed. Table type Transparent table is set as default.

Make the necessary entries in the Short description and Delivery class fields on the Attributes tab page. Then define the fields of the table.

Proceed as when creating a transparent table. Save your entries.

Choose Extras ® Change table category.

A dialog box appears in which you have to select the table type Pooled table or Cluster table.

Choose Select.

You return to the field maintenance screen for the table. Field Pool/cluster name is displayed on the Attributes tab page in addition to the standard fields.

Enter the name of the table pool or table cluster to which you want to assign the pooled table or cluster table in field Pool/cluster name

Note that the total key length of a pooled table may not exceed the key length of the associated table pool. The key of a cluster table must correspond to the key of the associated table cluster.

Proceed as when creating a transparent table (see Creating Tables). Remember that you cannot create indexes for pooled or cluster tables.

All the attributes of the technical settings can be maintained for pooled tables and cluster tables. Before you can access these attributes, however, you must convert the table to a transparent table.

Regards, ABY

[Former Member]

Hi,

For creating cluster tables first u have to create table pool ...

create a table and specify the fields and other tecnical settings and

then Goto EXTRAS --> Change Table Category and selct the Pooled table and activate it...

Then Create another table And specify the required fileds and also the settings and then

Goto EXTRAS --> Change Table Category and selct the Cluaster table and in Delivery and

Maintainence Properties mention the Pooled table that u created and activate it...

reward if it helps..

Regards,

Omkar.

[Former Member]

Hi,

Please see posting below

Check this Link to know more

+++

i)Transparent tables - Exists with the same structure both in dictionary as well as in database exactly with the same data and fields. Both Opensql and Nativesql can be used.

ii)Pool tables & Cluster tables - These are logical tables that are arranged as records of transparent tables. one cannot use native sql on these

tables (only open sql).They are not managable directly using database system tools.

Pooled table:

Pooled tables can be used to store control data (e.g. screen sequences, program parameters or temporary data). Several pooled tables can be combined to form a table pool. The table pool corresponds to a physical table on the database in which all the records of the allocated-pooled tables are stored.

Cluster table

Cluster tables contain continuous text, for example documentation. Several cluster tables can be combined to form a table cluster. Several logical lines of different tables are combined to form a physical record in this table type. This permits object-by-object storage or object-by-object access. In order to combine tables in clusters, at least part of the keys must agree. Several cluster tables are stored in one corresponding table on the database.

Regards, ABY

[Former Member]

Hi,

Pool table

A database table defined in the ABAP Dictionary whose database instance is assigned to more than one table defined in the ABAP Dictionary. Multiple pool tables are assigned to a table pool in the database. The key fields of a pool table have to be character-type fields. The table pool's primary key consists of two fields: TABNAME for the name of a pool table, and VARKEY for the interdependent contents of the key fields in the corresponding pool table. The non-key fields of the pool table are stored in compressed format in their own column, called VARDATA, of the table pool. The only way to access pool tables is by using Open SQL. Joins are not allowed.

Table Pool

Database table in the database that contains the data of several pool tables.

Cluster Table

Database table defined in the ABAP Dictionary, whose version on the database is not only assigned to one table defined in the ABAP Dictionary. Several cluster tables are assigned to a table cluster in the database. The intersection of the key fields of the cluster tables forms the primary key of the table cluster. The other columns of the cluster tables are stored in compressed form in a single column VARDATA of the table cluster. You can access cluster tables only via Open SQL, and only without using joins.

Table Cluster

Database table in the database that contains the data of several cluster tables.

Note: Never mix up with a database table that has the necessary structure for storing data clusters in database tables and in the shared memory. Those are called INDX-type, with reference to the database table INDX supplied by SAP. Data clusters are groupings of data objects for transient and persistent storage in a selectable storage medium. A data cluster can be processed using the statements IMPORT, EXPORT, and DELETE FROM

This link also helps you...

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21f083446011d189700000e8322d00/content.htm

Check out these threads

EXAMPLES

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21f083446011d189700000e8322d00/content.htm

Regards,

Priyanka.

[Former Member]

I. Transparent tables (BKPF, VBAK, VBAP, KNA1, COEP)

· Allows secondary indexes (SE11->Display Table->Indexes)

· Can be buffered (SE11->Display Table->technical settings) Heavily updated tables should not be buffered.

II. Pool Tables (match codes, look up tables)

· Should be accessed via primary key or

· Should be buffered (SE11->Display Table->technical settings)

· No secondary indexes

· Select * is Ok because all columns retrieved anyway

III. Cluster Tables (BSEG,BSEC)

· Should be accessed via primary key - very fast retrieval otherwise very slow

· No secondary indexes

· Select * is Ok because all columns retrieved anyway. Performing an operation on multiple rows is more efficient than single row operations. Therefore you still want to select into an internal table. If many rows are being selected into the internal table, you might still like to retrieve specific columns to cut down on the memory required.

· Statistical SQL functions (SUM, AVG, MIN, MAX, etc) not supported

· Can not be buffered

IV. Buffered Tables (includes both Transparent & Pool Tables)

While buffering database tables in program memory (SELECT into internal table) is generally a good idea for performance, it is not always necessary. Some tables are already buffered in memory. These are mostly configuration tables. If a table is already buffered, then a select statement against it is very fast. To determine if a table is buffered, choose the 'technical settings' soft button from the data dictionary display of a table (SE12). Pool tables should all be buffered.

"Major difference betwen Standard tables,Pooled tables and Cluster Tables?

1.A transparent table is a table that stores data directly. You can read these tables directly on the database from outside SAP with for instance an SQL statement.

2.Transparent table is a one to one relation table i.e. when you create one transparent table then exactly same table will create in data base and if is basically used to store transaction data.

3.A clustered and a pooled table cannot be read from outside SAP because certain data are clustered and pooled in one field.

4.One of the possible reasons is for instance that their content can be variable in length and build up. Database manipulations in Abap are limited as well.

5.But pool and cluster table is a many to one relationship table. This means many pool table store in a database table which is know as table pool.

6.All the pool table stored table in table pool does not need to have any foreign key relationship but in the case of cluster table it is must. And pool and cluster table is basically use to store application data.

7.Table pool can contain 10 to 1000 small pool table which has 10 to 100 records. But cluster table can contain very big but few (1 to 10) cluster table.

8.For pool and cluster table you can create secondary index and you can use select distinct, group for pool and cluster table. You can use native SQL statement for pool and cluster table.

9.A structure is a table without data. It is only filled by program logic at the moment it is needed starting from tables.

10.A View is a way of looking at the contents of tables. It only contains the combination of the tables at the basis and the way the data needs to be represented. You actually call directly upon the underlying tables.

'The table which store information about Structures and Tables are as follows:

DD02L - table properties

DD02T - table texts

DD03L - field properties

DD03T - field texts

regards,

srinivas

*reward for useful answers*</b>

[Former Member]

<b>Creating Table Pools/Table Clusters</b>

<b>Procedure</b>

In the initial screen of the ABAP Dictionary, choose Utilities-> Further Dictionary Objects.

A dialog box appears.

Select the object type Table pool/cluster and enter the object name. Choose .

A dialog box appears in which you must specify if it is a table pool or a table cluster.

Select the required object type and choose .

The maintenance screen for table pools/clusters appears.

The necessary entries will have been made automatically for the fields for table pools since a table pool has a fixed structure. You should not change these standard settings if you can avoid it.

The structure of a table cluster is also mostly fixed. Certain fields are therefore proposed when the table cluster is created. You can adjust this proposal to your requirements, for example by inserting further key fields. However, make sure you conform to the structure necessary for a table cluster.

Enter an explanatory text in the field Short text.

If necessary, select the activation type of the table pool/cluster with Utilities -> Activation type.

Create documentation about the table pool/cluster with Goto -> Documentation.

This documentation should describe what the table pool/cluster is used for. The documentation is also output when the table pool/cluster is printed.

Go to the maintenance screen for the technical settings by choosing Goto-> Technical settings.

In contrast to the table maintenance screen, you can only define the size category here. All other attributes of the technical settings are preset.

Activate the table pool/cluster with .

<b>Result</b>

The table pool/cluster is activated. You can look at the log of the activation with Utilities -> Activation log. If errors occurred during activation, the activation log is automatically displayed.

After the table pool/cluster has been activated, you need to create it in the database. To do this, use the database utility (Utilities -> Database utility).

<b>Reward if helpful</b>