Bookshelf Home | Contents | Index | Search | PDF |
Siebel Marketing Guide > External Data Mapping > Working With Tables >
Understanding Partition and Union Tables
In large data warehouses, tables can grow to an unmanageable size. Typically, this occurs with tables containing transaction data that expands over time. You can use the following table types to manage table size:
- Partition tables. Large tables can be divided into smaller tables (partitioned) to improve performance. Partition tables can be individually accessed.
- Union tables. Tables created to logically (not physically) reconnect the partition tables.
Union tables cannot be used as pass-through tables. You use information in pass-through tables to join one table to another but you cannot use any of the union table's data in your list. For example, you have three tables labeled A-C-D. Table C is a union table. If you need to use data from table C in your list, you cannot use table C as a pass-through table. You must select a different pass-through table, perhaps one of the partitioned tables in your union table.
Consider the following guidelines when creating union tables:
- Have identical common fields. The partition tables must have common fields for you to include those fields in a union table. Therefore, the reference name, expression, data type, and data size for each field must be identical across partitions. The comparison process is case sensitive. For example, the expression AAA would be evaluated differently than the expression Aaa.
CAUTION: Some tasks will fail if Marketing cannot use the Merge method with union tables. For additional information, see Understanding Joins and Designing Customer Hierarchies.
- Use at least two tables. You can create a union table that has only one partition table. However, the purpose of creating a union table is to join two or more partition tables.
The following examples illustrate union table rules.
Example 1. You want to create a union table that uses three partition tables. Because the five fields in each partition table are identical, the union table contains the five fields.
Partition 1 Partition 2 Partition 3 Union Field1 Field1 Field1 Field1 Field2 Field2 Field2 Field2 Field3 Field3 Field3 Field3 Field4 Field4 Field4 Field4 Field5 Field5 Field5 Field5Example 2. You want to create a union table using partition tables that have no common fields. The union table contains no fields.
Partition 1 Partition 2 Union Field1 Field6 Field2 Field7 Field3 Field8 Field4 Field9 Field5 Field10Example 3. You want to create a union table using three partition tables. Examples 1 and 3 have common fields. However, because Example 2 fields are not identical, the union table contains no fields.
Partition 1 Partition 2 Partition 3 Union Field1 Field6 Field1 Field2 Field7 Field2 Field3 Field8 Field3 Field4 Field9 Field4 Field5 Field10 Field5Example 4. You want to create a union table using three partition tables. The tables have some fields which are identical, and others that are not. The fields that are identical across the three tables are included in the union table.
Partition 1 Partition 2 Partition 3 Union Field1 Field1 Field1 Field1 Field2 Field7 Field2 Field3 Field8 Field3 Field4 Field4 Field4 Field4 Field5 Field10 Field5Example 5. You are editing partition tables and plan to create a new union table. You add a new field to each partition table. However, the fields are not identical. The new union table reflects no changes from the old union table.
Old Union Partition 1 Partition 2 New Union Field1 Field1 Field1 Field1 Field2 Field2 Field2 Field2 Field3 Field3 Field3 Field3 Field4 Field4 Field4 Field4 Field5 Field5 Field5 Field5 Field6 Field7Example 6. You are editing partition tables and plan to create a new union table. You add a new field to each partition table, and the fields are identical. The new union table reflects the addition of the new field.
Bookshelf Home | Contents | Index | Search | PDF |
Siebel Marketing Guide Published: 23 June 2003 |