Query optimizer
The optimizer objective is to find alternative ways to execute a query_to evaluate the “cost” of each alternative then to choose the one with the lowest cost. To understand the function of the query optimizer, let’s use a simple example. Assume that you want to list all products provided by a vendor based in Florida. To acquire that information. you could write the following query:
SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME, V_STATE
FROM PRODUCT, VENDOR
WHERE PRODUCT.P_CODE = VENDOR.V_CODE
AND VENDOR.V_STATE=’FL’
Furthermore, let’s assume that the database statistics indicate that:
• The PRODUCT table has 7,000 rows.
• The VENDOR table has 300 rows.
• Ten vendors are located in Florida.
• One thousand products come from vendors in Florida.
It’s important to point out that only the first two items are available to the optimizer. The second two items are to illustrate the choices that the optimizer must make. Armed with the information in the first two items, the optimizer would try to find the most efficient way to access the data. The primary factor in determining the most efficient access plan is the I/O cost. (Remember, the DBMS always tries to minimize I/O operations.) Table bellow shows two sample access plans for the previous query and their respective I/O costs.
المادة المعروضة اعلاه هي مدخل الى المحاضرة المرفوعة بواسطة استاذ(ة) المادة . وقد تبدو لك غير متكاملة . حيث يضع استاذ المادة في بعض الاحيان فقط الجزء الاول من المحاضرة من اجل الاطلاع على ما ستقوم بتحميله لاحقا . في نظام التعليم الالكتروني نوفر هذه الخدمة لكي نبقيك على اطلاع حول محتوى الملف الذي ستقوم بتحميله .
|