Creating tables of pre-joined data

4. Pre-Joined tables

Consider creating tables of pre-joined data when two or three tables should join on a normal format by an application although the disadvantage of the join is not allowed, the pre-joined tables should:

• Consist no redundant columns (matching join criteria columns)
• contain only those columns completely essential for the application to provide its processing requirements
• Be made frequently using SQL to join the normalized tables.

When the pre-joined tables are created the disadvantage of the join will raise a problem only once [Dick] [25, 41].

Since every new query does not cause a problem for the overhead of the table join process a pre-joined table is queried very effectively.

5. Report tables

Most of the time developing an end-user report using SQ is possible. These sorts of reports need special data manipulation or formatting. Consider creating a table that displays the report when specific highly visible or critical reports of this kind are needed to be seen in an on-line environment. Then by using SQL and/or another report facility this table can be queried. In a batch environment The report should be made by using the suitable mechanism (application program, 4GL, SQL, etc.). then in sequence It can loaded into the report table. The report table must:

• Consist one column for each column of the report
• Have a clustering index on the columns which make the reporting sequence
• Not destroy relational tenets (such as, 1NF and atomic data elements)

Page15

In order to carry the results of outer joins or other complex SQL statements Report tables are suitable. a simple SELECT statement can be apply to retrieve the results of the outer join rather than the complex UNION technique If an outer join is executed and then loaded into a table. Some RDBMS products support an explicit outer join function that can be a substitution for the UNION depicted. After all, based on the implementation, the explicit outer join can be simpler or more complicated than the UNION it replaces. [25,41].

6. Mirror (duplicating) tables

  It is necessary to divide processing into two (or more) different components in case of very active application system which needs creating duplicate, or mirror tables. Consider an application system that has very heavy on-line traffic during the morning and early afternoon hours. Both querying and updating of data are involved in such traffic. On the same application tables during the afternoon decision support processing is also performed. It always seems that the production work in the afternoon to destroy the decision support processing causing dead locks and time outs frequently. Creating mirror tables is a solution to remove the problem in this condition. Background set of tables is available for the decision support reporting and a foreground set of tables for the production traffic. In order to keep the application data synchronized a mechanism must be established to periodically migrate the foreground data to background tables. One such this kind of mechanism would be a batch job executing LOAD and UNLOAD utilities. in order to maintain the effectiveness of the decision support processing This must be done as often as necessary. It is worth noting that since the access needs of decision support are usually significantly different than the access needs of the production environment, various data definition decisions like indexing and clustering may be selected for the mirror tables.

Page 16

7. Partitioning tables

Fragmentation or partitioning is a mechanism normally used in relational databases to decrease the execution time of queries. The terms Fragmentation, Partitioning, and Clustering in databases domain is inferred to a table split into smaller data sets to support the management of very large volumes of data properly. As mentioned before, two ways of achieving fragmentation are existed: vertical and horizontal [218]. Vertical fragmentation makes the designer able to group attributes of a relation into smaller records. For instance, a dimension may be split in order to have the name and city attributes in one partition and the remaining attributes in another partition. As a sequence, more records can be retrieved into main memory when a query asks name, because they consist fewer attributes and therefore their size are smaller. Contrarily, horizontal fragmentation split a table into smaller tables with the same structure but with fewer records. For instance, if some queries need the latest data while others access older data, a fact table can be horizontally partitioned based on some time frame such as years [234]. Therefore, since smaller data sets are physically assigned to different partitions, these smaller data sets significantly facilitate administrative tasks, enhance query performance when parallel processing is used, and provide access to a smaller subset of the data (if the user?s selection does not refer to all partitions). During physical data warehouse design Fragmentation techniques should be selected. Oracle [213] provides four kinds of horizontal partitioning methods: range, hash, list, and composite. Each of them has different merits and design considerations. In range partitioning, the rows of a table are partitioned based on a range of values. In hashing partitioning, the rows of a table are partitioned according to a hash function used for an attribute of the table. Lastly,