Envisage a situation in which you are standing facing a delicate buffet overflowing with several delicacies. The objective is to try all of them out, however you must make a decision on what sequence and what substitute of flavors will make the most of the general delight of your appetite. Even though much less satisfying and prejudiced, these are the forms of difficulties that query optimizers are required to resolve. Provided a query, there are numerous schemes that a DBMS can pursue to process it and create its response. All plans are equal in the basis of their ultimate output however they differ in their cost that is the quantity of time that they require to run. Therefore, one of the most difficult predicaments in query optimization is to precisely approximate the costs of substitute query plans.
Solution
Most qualified optimizers estimate or charge the cost of query plans via an algebraic form of query implementation costs. This hinges a great deal on the approximations of the cardinality and the figures of tuples moving via each perimeter in a query plan. The Cardinality approximation sequentially relies on selection factor approximations of predicates in the query plan. Conventionally, database systems approximate selectivities via moderately comprehensive statistics on the allocation of the worth in each discourse, for example histograms. This method operates fine for approximation of selectivities of each predicates. On the other hand numerous queries have predicates conjunctions for example select count (*) from B where B. make='Toyota' and B. model='Primo'. These predicates are frequently much correlated, for instance, model='Primo' means make='Toyota', thus it is extremely difficult to approximate the conjunct selectivity generally. The Poor cardinality approximations and untamed correlation are the key reasons why optimizers choose deprived query plans. Therefore from this reason a database administrator is supposed to frequently modernize the database statistics, particularly following key data loads and unloads.
References
Chen, P. s., & Akoka, J. (2006). Optimal design of distributed information systems. IEEE trans. compute. c-29, 12, 1068-1080.
Chesnais, A., Gelenbe, E., and Mitrani, I. (2006). On the modeling of parallel access to
shared data. Communication. ACM 26, 3 (Mar.), 196-202.
Cheung, T.-Y. (2007). A method for equijoin queries in distributed relational databases.
IEEE Trans. Comput. C-31, 8, 746-751.
Chiu, D. M., and Ho, Y. C. (2004). A methodology for interpreting tree queries into optimal semijoin expressions. In Proceedings of the ACM SIGMOD International Conference on Management of Data (Santa Monica, Calif., May 14-16).ACM, New York, pp. 169-178.
Davis, L. S., AND Kunii, T. L. (2005). Pattern databases. In Data Base Design Techniques
II, S. B. Yao and T. L. Kunii, Eds. Springer, New York, pp. 357-399.
Dayal, U. (2003). Evaluating queries with quantities: A horticultural approach. In
Proceedings of the ACM Symposium on Principles of Database Systems {Atlanta, Ga.).
ACM, New York, pp. 125-136.
Epstein, A., Stonebraker, M., and Wong, E. (2008). Distributed query processing in a relational data base system. In Proceedings of the ACM-SIGMOD International
Conference on Management of Data. ACM, New York, pp. 169-180.
Garcia-Molina, H., Ullhman, J., and, Widom, J., (2004) Database Systems-The Complete
Book, Pearson Education,
Raghu R., and Johannes G., (2003) Database Management Systems, 3rdEdition, McGraw Hill,
Rothnie, J. B. (2004). An approach to implementing a relational data management system. In Proceedings of the ACM-SIGMOD Workshop on Data Description, Access, and Control (Ann Arbor, Mich., May 1-3). ACM, New York, pp. 277-294.
Selinger, et.al. (2009). Access path selection in a relational database management system. In Proceedings of the ACM-SIGMOD International Conference on Management of Data (Boston, Mass., May 30-June 1). ACM, New York, pp. 23-34.
Severance, D. G., and Carlis, J. V. (2007). A practical approach to selecting record access paths. ACM Comput. Surv. 9, 4 (Dec.) 259-272.
Shneiderman, B., and Goodman, V. (2006) Batched searching of sequential and tree structured files. ACM Trans. Database Syst. I, 3 (Sept.), 268-275.
Walker, A. (2007). On retrieval from a small version of a large data base. In Proceedings of the 6th International Conference on Very Large Data Bases (Montreal, Oct. 1-3). IEEE, New York, pp. 47-54.
Warren, D. H. D. (2005). Efficient processing of interactive relational database queries expressed in logic. In Proceedings of the 9th International Conference on Very Large
Data Bases (Cannes, Sept. 9-11). IEEE, New York, pp. 272-281.
