The Effect of Buffer Size on Pages Accessed in Random Files

Prior works, for estimating the number of pages (blocks) accessed from secondary memory to retrieve a certain number of records for a query, have ignored the effect of main memory buffer size. While this may not cause any adverse impact for special cases, in most cases the impact of buffer sizes will be to increase the number of page accesses. This paper describes the reasons for the impact due to a limited buffer size and develops new expressions for the number of pages accessed. The accuracy of the expressions is evaluated by simulation modeling; and the effects of limited buffer size are discussed. Analytical works in database analysis and design should use the new expressions: especially when the effect of the buffer size is significant

Batched Searching in Database Organizations

Savings in the number of page accesses due to batching on sequential, tree-structured, and random files are well known and have been reported in the literature. This paper asserts that substantial savings can also be obtained in database organizations by batching the requests for records (in queries), and also by batching intermediate processing requests while traversing the database. A simple database having two interrelated files is used to demonstrate such savings. For the simple database, three variations on batching are reported and compared with the case of unbatched requests. New mathematical expressions have been developed for the batched cases as well as for the unbatched case, and the savings are demonstrated with some example problems. As an extension, larger databases will enjoy even greater savings due to batching. The paper also discusses several strategies for applying the batching approach to current databases, and the advantages of emerging very large main memories for the batching approach

An Interactive DSS Tool for Physical Database Design

The design of efficient physical databases is a complex activity involving the consideration of a large number of factors. Because of the complexity, mathematical programming approaches seeking to optimize the physical database have to make many simplifying assumptions; therefore, their applicability is limited. Further, the database designer may want to experiment with design preferences and features not considered by the mathematical optimization approaches. In order to effectively design the physical database, this article describes an interactive DSS tool, which aides the database designer in this task. The database design is accomplished in the context of a high-level abstract model which is capable of being implemented in a variety of DBMSs and file systems. Because of this generic nature of the abstract model, the utility of the DSS tool is enhanced. The interactive tool not only lets the designer experiment with his own designs, but also provides several heuristic optimization procedures to enable the generation of many good designs. The heuristic designs may be used for final physical database design as well as for further experimentation. The paper also includes examples of how the physical design selected using the abstract model and the interactive tool may be implemented on several DBMSs and file systems

Heuristic Optimization of Physical Data Bases: Using a Generic and Abstract Design Model

Designing efficient physical data bases is a complex activity, involving the consideration of a large number of factors. Mathematical programming-based optimization models for physical design make many simplifying assumptions; thus, their applicability is limited. In this article, we show that heuristic algorithms can be successfully used in the development of very good, physical data base designs. Two heuristic optimization algorithms are proposed in the contest of a genetic and abstract model for physical design. One algorithm is based on generic principles of heuristic optimization. The other is based on capturing and using problem-specific information in the heuristics. The goodness of the algorithms is demonstrated over a wide range of problems and factor values

Heuristic Optimization of Physical Databases; Using a Generic & Abstract Design Model

Abstract: Designing efficient physical data bases is a complex activity, involving the consideration of a large number of factors. Mathematical programming-based optimization models for physical design make many simplifying assumptions; thus, their applicability is limited. In this article, we show that heuristic algorithms can be successfully used in the development of very good, physical data base designs. Two heuristic optimization algorithms are proposed in the contest of a genetic and abstract model for physical design. One algorithm is based on generic principles of heuristic optimization. The other is based on capturing and using problemspecific information in the heuristics. The goodness of the algorithms is demonstrated over a wide range of problems and factor values. Subject Areas: Heuristics, Management Information Systems, and Simulation. Article: INTRODUCTION Data base design is a challenging activity and involves two phases: logical and physical design. Logical design involves the development of a logical data structure (LDS) for the task domain. Physical design is concerned with developing structures for placing data on secondary storage, given a specific LDS. While both phases require significant effort, the physical data base design phase is the focus of this article. The main concern in physical data base design is the efficiency of the physical design, which can be measured in a number of ways, such as storage requirement, response time and total system cost. Prior works have generally dealt with and developed design/optimization models for specific aspects of physical data base design. These works include index selection While the attention to individual design problems results in elegant solutions, it is quite plausible that those individual solutions will have to be perturbed when the total data base is put together. [20, p. 217] Thus, the need for comprehensive design models (which deal with the entire data base design problem rather than parts of it) cannot be overstated. Comprehensive physical design models fall under two categories: (1) those specific to and (2) those generic and independent of any particular logical data model and/or commercial DBMS. The first category offers the advantage of direct implementation on a particular DBMS, but is difficult to convert to another implementation. The second category allows more flexibility at the expense of added conversion requirements for a particular implementation. Optimization of physical design in the first category is reported i