Developing Joined Tables for Data

Developing Joined Tables for Data4. Pre-Joined tables dish out creating tables of pre- matinged data when 2 or three tables should join on a normal format by an industry although the disadvantage of the join is not allowed, the pre-joined tables should live no redundant columns (matching join criteria columns)contain exclusively those columns completely essential for the application to provide its make foring requirementsBe made frequently using SQL to join the normalized tables.When the pre-joined tables are created the disadvantage of the join will put in 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 tablesMost of the term developing an end-user report using SQ is possible. These sorts of reports pauperization special data manipulation or formatting. Consider creating a table that displays the report when specific highly visible or critical rep orts of this kind are needed to be seen in an on-line environment. and then(prenominal) 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 mustConsist mavin column for each column of the reportHave a clustering index on the columns which make the reporting sequenceNot deflower relational tenets (such as, 1NF and atomic data elements)Page15In order to carry the results of outer joins or other complex SQL statements Report tables are suitable. a simple postulate 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, found on the implementation, the explicit outer join can be simpler or more complicated than the UNION it replaces. 25,41.6. Mirror (duplicating) tablesIt 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 good 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 do 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 estab lished to sporadically migrate the foreground data to background tables. One such this kind of mechanism would be a batch job executing LOAD and drop down 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 opening needs of decision support are usually significantly different than the access needs of the production environment, various data definition decisions like list and clustering may be selected for the mirror tables.Page 167. Partitioning tablesFragmentation 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 focusing of very large volumes of data properly. As mentioned before, two ways of achieving fragmentation are existed vertical and horizontal 218. Vertical fragm entation makes the designer able to assort 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 parent to all partitions). During physical data warehouse design Fragmentation techniques should be selected. Oracle 213 provides four kinds of horizontal partitioning methods range, haschisch, 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,