This Article 
 Bibliographic References 
 Add to: 
An Effective Approach to Vertical Partitioning for Physical Design of Relational Databases
February 1990 (vol. 16 no. 2)
pp. 248-258

Vertical partitioning can be used to enhance the performance of relational database systems by reducing the number of disk accesses. The authors identify the key parameters for capturing the behavior of an access plan and propose a two-step methodology consisting of a query analysis step to estimate the parameters and a binary partitioning step which can be applied recursively. The partitioning uses an integer linear programming technique to minimize the number of disk accesses. Significant performance benefit would be achieved for join if the partitioned (inner) relation could fit into the memory buffer under the inner-outer loop join method, or if the partitioned relation could fit into the sort buffer under the sort-merge join method, but not the original relation. For cases where a segment scan or a cluster index scan is used, vertical partitioning of the relation with the algorithm described is still often found to lead to substantial performance improvement.

[1] M. M. Astrahanet al., "System R: Relational approach to database management,"Trans. Database Syst., vol. 1, no. 1, pp. 97-137, 1976.
[2] M. J. Eisner and D. G. Severance, "Mathematical techniques for efficient record segmentation in large shared databases,"J. ACM, vol. 23, pp. 619-635, Oct. 1976.
[3] J. Hoffer, "An integer programming formulation of computer data-base design problems,"Inform. Sci., vol. 11, pp. 29-48, 1976.
[4] F. S. Hillier and G. J. Lieberman,Operations Research, 3rd ed. San Francisco, CA: Holden-Day, 1980.
[5] J. Joyce and D. Warn, "Command use in a relational database system," inAFIPS Conf. Proc. 52 (1983 Nat. Comput. Conf.), pp. 247- 253.
[6] J. Navathe et al., "Vertical Partitioning Algorithms for Database Design,"ACM Trans. Database Systems, Vol. 9, No. 4, Dec. 1984.
[7] P. Selinger,et al., "Access path selection in a relational data base system," inProc. 1979 ACM-SIGMOD Int. Conf. Management of Data, Boston, MA, June 1979.
[8] M. Stonebraker,et al., "The design and implementation of INGRES,"ACM Trans. Database Syst., vol. 1, no. 3, Sept. 1976.
[9] J. Z. Teng and R. A. Gumaer, "Managing IBM Database 2 buffers to maximize performance,"IBM Syst. J., vol. 23, no. 2, pp. 211- 218, Feb. 1984.
[10] L. G. Waters, "Reduction of integer polynomial problems to zero-one linear programming problems,"Oper. Res., vol. 15, pp. 1171- 1174, 1967.
[11] S. B. Yao, "An attribute based model for database access cost analysis,"ACM Trans. Database Syst., vol. 2, pp. 45-67, Mar. 1977.
[12] P. S. Yu, H. Heiss, and S. Lee, "Workload characterization of relational database environments," IBM, Yorktown Heights, NY, Res. Rep. RC 14675, June 1989.

Index Terms:
vertical partitioning; physical design; relational databases; disk accesses; two-step methodology; query analysis; binary partitioning; integer linear programming; join; sort-merge; segment scan; cluster index scan; linear programming; relational databases; software engineering.
D.W. Cornell, P.S. Yu, "An Effective Approach to Vertical Partitioning for Physical Design of Relational Databases," IEEE Transactions on Software Engineering, vol. 16, no. 2, pp. 248-258, Feb. 1990, doi:10.1109/32.44388
Usage of this product signifies your acceptance of the Terms of Use.