Learning About Computer & Internet

زبان SQL تنها زبان استاندارد و جامع پياده‌سازي، مديريت، نگهداري و كار با بانكهاي اطلاعاتي مي‌باشد كه تقريباً توسط تمام بانكهاي اطلاعاتي كوچك و بزرگ مانند Access، SQL Server، Oracle و DB2 پشتيباني مي‌شود. طراحان و افرادي كه بنوعي با بانكهاي اطلاعاتي سروكار دارند و همچنين برنامه نويساني كه از اين بانكها استفاده مي‌كنند هركدام بايد تا اندازه‌‌اي با اين زبان آشنايي داشته باشند. اين مقاله كه در سه قسمت تهيه شده است مي‌كوشد تا مفاهيم زبان SQL را در قالب يك مثال كاربردي بيان كند. هرچند كه مفاهيم بكار رفته در اين مقاله در تمامي بانكهاي اطلاعاتي قابل پياده‌سازي مي‌باشند ولي مثالهاي ارائه شده در 2000 SQL Server مورد تست قرار گرفته‌اند.

بانك اطلاعاتي كه در اين مقاله بعنوان مثال مورد استفاده قرار گرفته است بانك اطلاعاتي يك آموزشگاه مي‌باشد كه شامل دو جدول بنامهاي teachers و students مي‌باشد. جدول اول اطلاعات اساتيد و جدول دوم اطلاعات دانشجويان را در خود نگه مي‌دارد. جدول اول داراي چهار فيلد زير مي‌باشد: name يا نام از نوع text، family يا فاميل از نوع text، age يا سن از نوع عدد، salary يا حقوق از نوع عدد.

جدول دانشجويان نيز شامل چهار فيلد مي‌باشد، سه فيلد اول آن مشابه سه فيلد اول جدول اساتيد مي‌باشد و فيلد چهارم آن عبارتست از GPA يا معدل كه يك فيلد عددي است.

زبان SQL داراي دستورات متنوع و نسبتاً زيادي مي‌باشد. به اين دستورات Clause نيز گفته مي‌شود. در اين مقاله و قسمتهاي بعدي آن تعدادي از مهمترين Clauseهاي زبان SQL مورد بررسي قرار مي‌گيرند.

1 – دستورهاي SELECT و FROM :

هدف نهايي از دادن انبوه اطلاعات به كامپيوتر، جستجو و يافتن اطلاعات مفيد مي‌باشد. به اين عمل يعني جستجوي اطلاعات در بانك اطلاعاتي Query نيز گفته مي‌شود. اكثر دستورات زبان SQL نيز در همين راستا مورد استفاده قرار مي‌گيرند. در اين بين مهمترين و پركاربردترين دستور را مي‌توان دستور SELECT قلمداد كرد.

اين دستور جهت انتخاب يك يا چند فيلد از يك يا چند جدول مختلف مورد استفاده قرار مي‌گيرد، فيلدهاي انتخاب شده پس از اجراي query روي صفحه نمايش داده خواهند شد.

بعنوان مثال اگر در بانك اطلاعاتي فرضي خودمان بخواهيم اسم و فاميل تمام دانشجويان را مشاهده كنيم بايد برنامه‌اي به شكل زير بنويسيم:

SELECT name, family
FROM students

در اين برنامه كه به زبان SQL استاندارد نوشته شده است از دو دستور SELECT و FROM استفاده شده است. دستور SELECT مشخص مي‌كند كه چه فيلدهايي از جدول بايد نمايش داده شوند و دستور FROM نيز مشخص كننده جدولي است كه قرار است اطلاعات از داخل آن استخراج شوند. حال اگر بخواهيم نام و فاميل تمام اساتيد را ببينيم برنامه بالا را بايد بصورت زير تغيير دهيم:

SELECT name, family
FROM teachers

همان طوري كه از مثال‌هاي بالا نيز مشخص است جلوي دستور SELECT نام فيلدهايي نوشته مي‌شوند كه قرار است نمايش داده شوند و بعنوان جداكننده نيز بايد از كاراكتر كاما استفاده كرد. در صورتي كه بخواهيم تمام فيلدهاي يك جدول را ببينيم مي‌توانيم بجاي نوشتن اسم تمام فيلدها فقط از يك كاراكتر ستاره استفاده كنيم. كاراكتر ستاره بمعني تمام فيلدهاي يك جدول مي‌باشد. مثلاً دو دستور زير با هم معادلند:

SELECT *
SELECT name, family, age, gpa

2 – دستور WHERE :

دستور SELECT همان طوري كه گفته شد جهت انتخاب و نمايش تعدادي از فيلدهاي جداول مورد استفاده قرار مي‌گيرد. در اين حالت تمام ركوردها نمايش داده خواهند شد، ولي در بيشتر موارد هدف از نوشتن Query نمايش ركوردهايي است كه داراي شرايط ويژه‌اي مي‌‌باشند مثلاً نمايش مشخصات اساتيدي كه بيش از پنجاه سال سن دارند. دستور WHERE براي گذاشتن يك يا چند شرط به دستور SELECT مورد استفاده قرار مي‌گيرد. با اضافه كردن شرط به دستور SELECT تعداد ركوردهاي خروجي (پاسخ) معمولاً محدودتر مي‌شود. بعنوان مثال اگر بخواهيم مشخصات اساتيدي كه بيش از چهل سال سن دارند را ببينيم بايد برنامه‌اي بصورت زير بنويسيم:

SELECT *
FROM teachers
WHERE age > 40

در اين برنامه شرط age > 40 با استفاده از دستور WHERE به Query اضافه شده و باعث شده است تا فقط مشخصات اساتيدي نمايش داده شوند كه در اين شرط صدق مي‌كنند يعني بيش از چهل سال سن دارند.

حال اگر بخواهيم مشخصات دانشجوياني را كه نام آنها علي مي‌باشد و سن آنها نيز كمتر از پانزده سال است را ببينيم بايد Query زير را اجرا كنيم:

SELECT *
FROM students
WHERE age < 15 AND name = 'ali'

در مثال بالا دو مطلب جديد وجود دارد نخست آنكه در زبان SQL رشته متني را بايد داخل كوتيشن (‘) قرارداد. بنابراين براي معرفي كلمه‌اي بنام علي بايد آنرا بصورت ‘ali’ نوشت، زيرا در اين حالت اين كلمه يك ثابت رشته‌اي (متني) بحساب مي‌آيد. نكته ديگر آنكه مي‌توان شرطهاي مختلف را توسط AND، OR و NOT با همديگر ادغام كرده و شرطهاي پيچيده‌تري را بدست آورد. AND، OR و NOT هر سه از كلمات كليدي زبان SQL مي‌باشند. بعنوان يك مثال ديگر فرض كنيد مي‌خواهيم مشخصات تمام دانشجوياني را كه نام آنها، علي يا رضا نباشند را پيدا كنيم، برنامه‌اي كه اين Query را انجام مي‌دهد بصورت زير مي‌باشد:

SELECT *
FROM students
WHERE name NOT IN ('ali', 'reza')

در صورتي كه NOT را از برنامه بالا حذف كنيم مشخصات تمام دانشجوياني كه نام آنها علي يا رضا مي‌باشند نمايش داده خواهد شد.

در اين برنامه نيز يك كلمه كليدي جديد وجود دارد: IN كه براي تست عضويت در يك مجموعه بكار مي‌رود. مجموعه‌اي كه بادستور IN بكار مي‌رود مي‌تواند دهها عضو داشته باشد و نوع اعضا نيز مي‌تواند رشته‌اي، عددي يا ... باشد. البته واضح است كه تمام اعضا بايد هم نوع باشند.

حال بعنوان يك مثال پيشرفته‌تر مي‌خواهيم مشخصات دانشجوياني را ببينيم كه اسم فاميل آنها به ‘ زاده’ ختم مي‌شود مانند عليزاده، محمد زاده و ... براي اين منظور بايد Query زير را نوشته و اجرا كنيم:

SELECT *
FROM family LIKE '% zadeh'

كاربراني كه با Access كار مي‌كنند بايد سطر آخر را به صورت زير تغيير دهند:

WHERE family LIKE '*zadeh'

در اين مثال از Wildcardها استفاده شده است كه قبلاً با مفهوم آن در DOS و Windows يا حتي Unix آشنا شده‌ايم (ls a*) . در Wildcard ،SQL هايي به شرح زير وجود دارند:

% (در Access از * استفاده كنيد): اين Wild card نشانگر هر تعدادي از كاراكترها (هر كاراكتري) مي‌باشد.

- (در Access از ? استفاده كنيد): اين Wildcard نشانگر يك كاراكتر مي‌باشد كه اين كاراكتر مي‌تواند هر كدام از كاراكترهاي مجاز كامپيوتر باشد.

جهت جستجو كردن فيلدهايي كه داراي يك الگوي (Pattern) خاصي هستند بايد از دستور LIKE و Wildcardها استفاده كرد. در مثال زير نام و سن تمام اساتيدي كه اسم آنها با Pe شروع مي‌شود، نمايش داده خواهد شد.

SELECT name, age
FROM teachers
WHERE name LIKE 'Pe%'

بنابراين مشخصات افرادي كه نام آنها مثلاً پدرام يا پيمان باشد در خروجي ليست خواهد شد.

3 – دستور ORDERBY :

دستور ORDERBY جهت Sort كردن ركوردهاي نمايش داده شده مورد استفاده قرار مي‌گيرد. با اين دستور مي‌توان مشخص كرد كه ركوردهايي كه قرار است نمايش داده شوند برحسب كدام فيلد بايد مرتب شوند. بعنوان مثال براي مشاهده كردن مشخصات اساتيدي كه سن آنها بيشتر از 29 سال مي‌باشد و در ضمن ليست خروجي بترتيب اسم فاميل نيز مرتب شده باشد بايد Query زير را اجرا كرد.

SELECT *
FROM teachers
ORDERBY family

در اين حالت افراد بترتيب اسم فاميل خود ليست خواهند شد. (از A تا Z) در صورتي كه بخواهيم ترتيب Sort شدن برعكس شود (از Z تا A) مي‌توان پس از دستور ORDERBY از كلمه كليدي DESC استفاده كرد، مانند مثال زير

SELECT *
FROM teachers
ORDERBY DESC name

در اين مثال Sort شدن بترتيب اسم و نه بترتيب فاميل انجام مي‌گيرد.

براي دستور ORDERBY مي‌توان چند فيلد تعريف كرد، در اين صورت اين دستور عمل Sort كردن را با در نظرگرفتن اولين فيلد انجام خواهد داد در صورتي كه چند ركورد داراي مقدار مشابهي در اين فيلد باشند ملاك مرتب سازي آنها فيلد دومي خواهد بود كه در دستور ORDERBY ذكر شده است، در صورتي كه اين فيلد نيز داراي مقادير مشابهي باشد ملاك تصميم‌ گيري فيلد سوم خواهد بود والي آخر.

بعنوان مثال در Query زير نام اساتيدي كه بيش از 30 سال دارند برحسب فاميل آنها مرتب مي‌شود در صورتي كه چند استاد داراي اسم فاميل يكساني باشند ملاك مرتب شدن، اسم كوچك آنها خواهد بود.

SELECT *
FROM teachers
WHERE age > 30
ORDERBY family, name

4 – استفاده از توابع:

در دستور SELECT علاوه بر تعريف فيلدها مي‌توان از عبارتهاي رياضي و يا توابع استاندارد SQL نيز استفاده كرد.

بعنوان مثال اگر ماليات بردرآمد پنج درصد باشد Query زير نام اساتيد و مالياتي را كه هر كدام مي‌پردازند را مشخص مي‌كند.

SELECT family, name, salary*5/100
FROM teachers

علاوه بر عبارتهاي رياضي مي‌توان از توابع استاندارد SQL نيز استفاده كرد، تعدادي از اين توابع عبارتند از:

تابع COUNT : تعداد فيلدها را برمي‌گرداند.

تابع SUM : مجموع يك فيلد عددي را برمي‌گرداند.

تابع AVG : ميانگين يك فيلد عددي را برمي‌گرداند.

تابع MIN : مينيمم يك فيلد عددي را برمي‌گرداند.

تابع MAX : ماكزيمم يك فيلد عددي را برمي‌گرداند.

بعنوان مثال اگر بخواهيم تعداد اساتيد و مجموع حقوقهايي را كه به آنها پرداخت شده است را ببينيم مي‌توانيم از Query زير استفاده بكنيم.

SELECT COUNT (name), SUM (Salary)
FROM teachers

5 – Queryهاي چند جدولي:

تمام Queryهايي كه تاكنون مشاهده كرديد، Queryهاي تك جدولي بودند بدين معني كه در هر Query فقط اطلاعات يك جدول مورد جستجو قرار مي‌گرفت. در SQL امكان نوشتن Queryهاي چند جدولي نيز وجود دارد. در اين حالت اطلاعات چند جدول بطور همزمان مورد جستجو قرار مي‌گيرد و حتي امكان مقايسه فيلدهايي از يك جدول با فيلدهايي از جدول ديگر نيز وجود دارد. اگر در بين جداولي كه در Query شركت داده مي‌شوند فيلدهاي هم نام وجود داشته باشد بايد نام آن فيلدها را Fully qualified كرد بدين معني كه ابتدا اسم جدول و سپس اسم فيلد را ذكر كرد. بين اسم جدول و اسم فيلد نيز بايد از يك كاراكتر نقطه (‘.’) استفاده كرد.

بعنوان مثال اگر بخواهيم ليست اساتيد و دانشجوياني كه داراي ارتباط فاميلي هستند را ببينيم مي‌توانيم از Query زير استفاده بكنيم:

SELECT *
FROM students, teachers
WHERE students.family = teachers. Family

و يا اگر بخواهيم ليست اساتيدي را مشاهده كنيم كه سن آنها از سن برخي از دانشجويان كمتر است، مي‌توانيم Query زير را اجرا كنيم:

SELECT teachers.family, teachers.name
FROM students, teachers
WHERE teachers.age < students.age

6 – كلام آخر:

كلام آخر اينكه زبان SQL برخلاف زبانهايي مانند C يا C++ يك زبان Case Sensitive نيست بدين معني كه به بزرگ يا كوچك بودن حروف حساس نمي‌باشد. بنابراين به راحتي مي‌‌توان دستورات و كلمات كليدي آنرا در هر برنامه‌اي با حروف كوچك يا بزرگ تايپ كرد.

شاخصهاي يك جدول مي‌توانند به شما در دسترسي به يك يا چند سطر از داده‌ها كمك كنند. داشتن يك شاخص خوب براي پرس و جوي (query) شما يكي از بهترين راههاي بهبود بخشيدن به عملكرد است. هنگامي كه پرس و جوي شما سعي دريافتن تنها تعداد محدودي سطر از يك جدول بزرگ دارد،‌وجود يا عدم وجود يك شاخص خوب و مفيد مي‌تواند تاثير چشمگيري در تفاوت عملكرد داشته باشد. هنگام تنظيم پرس و جوهايي كه جداول گوناگوني دارند (مانند پيوند)، شاخصها مي‌توانند بطور موثري به سرويس‌دهنده SQL دريافتن سطرهاي موردنظر از بين جدولها كمك كنند.

بهينه ساز پرس و جوي سرويس دهنده SQL مي‌تواند براي اجراي پيوندها از ميان 3 استراتژي يكي را انتخاب كند: پيوند حلقه‌اي تودرتو (nested-loop)، پيوند ادغامي (merge) و پيوند hash، در اين مقاله به شرح دو استراتژي اول يعني حلقه‌اي تودرتو و ادغامي مي‌پردازيم. در هر روش، جدولهايي را كه مي‌خواهيد پيوند دهيد (يا زير مجموعه‌هايي از آنها كه با شرط WHERE محدود كرده‌ايد) ورودي‌هاي پيوند هستند. اگر پرس و جوي شما علاوه بر پيوند، شرط WHERE را نيز شامل شود، سرويس دهنده SQL ممكن است قبل از يافتن سطرهاي موردنظر در دومين جدول شرط WHERE را بكار ببرد. بعنوان مثال پرس و جوي 1 از بانك اطلاعاتي Northwind را در نظر بگيريد.

-- Query 1:
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
ON e.EmployeeID = o.EmployeeID

اين پيوند تمام سفارشات را از جدول سفارشها باز مي‌گرداند و همچنين براي هر يك از آنها، FirstName، LastName كارمندي را كه به آن OrderID و OrderDate مربوط مي‌شود را نيز باز مي‌گرداند.

در پرس و جوي 1 سرويس دهنده SQL تمام سطرها را در جدول كارمندان و سفارشات امتحان مي‌كند و ورودي‌هاي پيوند تمام سطرهاي جدولها هستند. بهرحال، طبق آنچه كه پرس و جوي 2 نشان مي‌دهد، اگر شما عبارت SELECT را با دو شرط WHERE تعريف كنيد، وروديهاي پيوند ديگر تمام سطرهاي جدولها نيستند.

-- Query 2:
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
ON e.EmployeeID = o.EmployeeID
WHERE OrderDate < '1996-12-01' AND LastName < 'D'

وروديهاي پيوند در پرس و جوي 2، بسيار كوچكتر از پرس و جوي 1 هستند. به جاي پيوند 9 سطر از جدول كارمندان با 830 سطر از جدول سفارشات، سرويس دهنده SQL بايد تنها 2 سطر از جدول كارمندان را با 121 سطر از جدول سفارشات پيوند دهد. بهينه ساز پرس و جو با وجود تعداد محدودي از سطرهاي ورودي غالباً استراتژي پيوند متفاوتي را در مقايسه با زماني كه وروديهاي پيوند بيشتر هستند برمي‌گزيند و همچنين ممكن است جدولها را به سبك متفاوتي پيوند دهد. به همان اندازه كه تصميمات استراتژي بهينه ساز مهم هستند اندازه جدولها نيز حائز اهميت است.

حلقه‌هاي تودرتو

حتي اگر پرس وجوي شما بيش از 2 جدول را پيوند دهد، سرويس دهنده SQL عمل پيوند را از طريق پيوند تنها دو ورودي در يك زمان اجرا مي‌كند و هر پيوند در يك پرس و جو ممكن است از استراتژي پيوند متفاوتي استفاده كند. آسانترين نوع پيوند – و نوعي از پيوند كه اكثر افراد هنگام عمليات پيوند بدان فكر مي‌كنند – پيوند حلقه تودرتوست مي‌توانيد تصور كنيد كه سرويس دهنده SQL روي دو ورودي عمل مي‌كند حتي اگر آنها آرايه‌هايي در يك زبان پيشرفته مانند C يا Basic باشند. سرويس دهنده SQL هر سطر يك ورودي را با تمام سطرهاي ورودي ديگر مقايسه مي‌كند تا تطابق بين سطرها را بيابد.

پرس و جوي 1 سعي دريافتن سطرهايي دارد كه با ستون EmployeeID تطبيق داشته باشد. بنابراين با استراتژي پيوند حلقه تودرتو، سرويس دهنده SQL بايد تمام مقادير EmployeeID در يك جدول را با تمام مقادير EmployeeID در جدول ديگر مقايسه كند.

بدترين قسمت سناريو براي يك پيوند حلقه تودرتو زماني است كه هيچ شاخصي نتواند به سرويس دهنده SQL دريافتن سطرهاي منطبق در بين وروديها و همچنين يافتن سطرهايي كه در هر شرط WHERE صدق مي‌كند، كمك نمايد. در ين حالت، وروديها كل سطرهاي جدولها هستند. بهينه ساز پرس و جو جدولي را بعنوان جدول خارجي انتخاب مي‌كند و در ابتدا به سطرهاي آن دستيابي مي‌يابد. بياييد فرض كنيم كه جدول خارجي داراي P1 صفحه و R1 سطر باشد. دومين جدول كه جدول داخلي است P2 صفحه دارد. سرويس دهنده SQL بايد تمام صفحات را از جدول خارجي بخواند؛ و براي هر سطر تعريف شده در هر صفحه بايد تمام صفحات را از جدول داخلي بخواند. براي يافتن تعداد صفحاتي كه سرويس دهنده SQL براي خواندن و ارائه نتيجه نياز دارد، مي‌توانيد از فرمول زير استفاده كنيد:

P1 + R1 * P2

حتي اگر جدولها نسبتاً كوچك باشند، عدد حاصله از صفحات خوانده شده به سرعت بزرگ مي‌شود. در مورد يك جدول خارجي با تنها 200 صفحه و 4000 سطر (براي مثال 20 سطر براي هر صفحه) و يك جدول داخلي با 100 صفحه، نتيجه رقمي كاملاً بزرگ است. جدولهايي با 100 يا 200 صفحه جدولهايي نيستند كه بطور غيرمعمول بزرگ باشند، اما براي پردازش پيوند در صورتي كه جدولها شاخصهاي مفيدي نداشته باشند، سرويس دهنده SQL نياز به دستيابي به بيش از 400,000 صفحه خواهد داشت.

شاخصها مي‌توانند در بهبود عملكرد يك پيوند حلقه تودرتو به طرق مختلف نقش داشته باشند. بزرگترين حسن اين شاخصها اغلب زماني است كه شما يك شاخص كلاستري روي ستون پيوند يكي از جدولها داشته باشيد. وجود يك شاخص كلاستري روي ستون پيوند غالباً مشخص مي‌كند كه سرويس دهنده SQL چه جدولي را بعنوان جدول داخلي انتخاب مي‌كند. اگر جدول داخلي داراي شاخص كلاستري باشد، سرويس دهنده SQL نياز به جستجو در ميان كل سطرهاي آن جدول را ندارد. شاخص كلاستري، سرويس دهنده SQL را مستقيماً به سوي سطرهايي در جدول داخلي هدايت مي‌كند كه داراي مقدار ستون پيوند بوده كه سطرهاي جاري در جدول خارجي را تطبيق مي‌دهد. بنابراين در آن فرمول، به جاي عبارت R1 ´ P2 كه نشان مي‌دهد سرويس دهنده SQL به تمام P2 صفحه دستيابي پيدا مي‌كند، مي‌توانيد P2 را با دستيابي 2 يا 3 صفحه‌اي جايگزين كنيد بسته به اينكه شاخص كلاستري چند Level دارد. بنابراين در مورد مثالي با 200 صفحه و 400 سطر در جدول خارجي و 100 صفحه در جدول داخلي نتيجه 3*4000+200 يا 200،12 صفحه خوانده شده است – يك پيشرفت بزرگ بالاي 400,000 صفحه مي‌باشد.

هنوز هم آن 4000 سطر در محاسبه نتيجه را بزرگتر از حد انتظار خواهد كرد. در اين حالت، تمامي 4000 سطر در جدول خارجي بخشي از نتيجه هستند كه موجب 4000 بار رجوع به جدول داخلي مي‌شود. يك راه ديگر براي كاهش تعداد صفحات بدست آمده كاهش اندازه وروديهاي خارجي است. علاوه بر كنترل شاخص كلاستري روي ستون پيوند، ابتدا بهينه ساز سعي مي‌كند جدولها را با وروديهاي كوچكتر پيوند دهد. در پرس و جوي 1، جدول كارمندان داراي يك شاخص كلاستري روي ستون پيوند يعني EmployeeID هستند اما اين جدول نيز بطور نمايشي كوچكتر از جدول سفارشات است. جدول كارمندان تنها 9 سطر دارد و جدول سفارشات 830 سطر. در پرس و جوي 1، اگر بهينه ساز يك پيوند حلقه تودرتو را انتخاب كند، از جدول كوچكتر كارمندان به عنوان ورودي خارجي استفاده مي‌كند بگونه‌اي كه تنها 9 بار به جدول سفارشات رجوع خواهد داشت.

اگر شما داراي شرط WHERE باشيد كه جدول خارجي را شامل مي‌شود، تعداد سطرهاي تعريف شده پايين مي‌آيد و سرويس دهنده SQL كمتر نياز به مراجعه به جدول داخلي را خواهد داشت. اگر پرس و جوي 1 را طوري تغيير دهيد كه شامل يك شرط WHERE در جدول سفارشات باشد، همانگونه كه در پرس و جوي 3 نشان داده شده، طرح پرس و جو تغيير مي‌كند.

-- Query 3:
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
ON e.EmployeeID = o.EmployeeID
WHERE OrderDate < '1996-12-01'

حالا، تنها 121 سطر در جدول سفارشات بخشي از نتيجه هستند، آن جدول كوچكتر كه با شاخص كلاستري روي ستون پيوند جدول كارمندان تركيب شده اين مفهوم را مي‌رساند كه بهينه ساز حالا جدول كارمندان را به عنوان جدول داخلي انتخاب مي‌كند. سرويس دهنده SQL از پيوند حلقه تودرتو استفاده خواهد كرد چرا كه شاخص كلاستري باعث مي‌شود سرويس دهنده SQL سريعاً سطرهاي منطبق شده را در جدول داخلي بيابد.

FIGURE 1: Query plan for Query 3
|..Nested Loops(Inner Join, OUTER REFERENCES:([o].[EmployeeID]))
    |..Clustered Index Scan(OBJECT:([northwind].[dbo].[Orders].[PK_Orders]
     AS [o]),
         WHERE:([o].[OrderDate] < 'Dec 1 1996 12:00AM'))
    |..Clusterd Index
Seek(OBJECT:([northwind].[dbo].[Employees].[PK_Employees] AS [e]),
         SEEK:([e].[EmployeeID]=[o].[EmployeeID]) ORDERED FORWARD)

شكل 1 طرح پرس و جو را در مورد پرس و جوي 3 نشان مي‌دهد. اولين خط اين طرح نوع پيوند (حلقه تودرتو) را نشان مي‌دهد و مشخص مي‌كند كه جدول خارجي ستون EmployeeID را ارجاع خواهد داد.

اسكن شاخص كلاستري در جدول خارجي شبيه اسكن يك جدول است زيرا هيچيك از شاخصهاي موجود نمي‌تواند دستيابي به جدول خارجي را سرعت ببخشد. شرط WHERE در ستون OrdrerDate تعداد سطرهاي برگردانده شده و تعداد دفعاتي كه سرويس دهنده SQL بايد به جدول داخلي دستيابي داشته باشد تا تعيين كند كداميك مقدار
OrderDate قابل قبولي دارند. در نهايت، طرح پرس و جو نشان مي‌دهد كه سرويس دهنده SQL از يك شاخص كلاستري براي جستجوي جدول داخلي استفاده مي‌كند زيرا اين شاخص روي ستوني است كه سرويس دهنده SQL براي يافتن سطرهاي منطبق استفاده مي‌كند.

چنانچه قبلاً ذكر شد شاخص در ستون OrderDate چيز خوبي است اما عملكرد پرس و جو را تقريباً به يك شاخص كلاستري روي ستون پيوند بهبود نخواهد بخشيد. يك شاخص مفيد در پارامتر جستجو در جدول خارجي بدين معناست كه سرويس دهنده SQL نبايد به تمام صفحات جدول خارجي رجوع نمايد، بنابراين،‌ مقدار P1 كاهش مي‌يابد. باتوجه به اينكه مقدار P1 نسبت به مقدار دومين عبارت،‌ P2 R1، كوچكتر است، بنابراين كاهش مقدار P1 فقط موجب بهبودي كمتر عملكرد مي‌گردد. شاخص جدول خارجي تعداد دفعاتي كه سرويس دهنده SQL بايد به جدول داخلي رجوع كند را كاهش نمي‌دهد زيرا سرويس دهنده SQL هنوز بايد بازاي هر سطر تعريف شده در جدول خارجي به جدول داخلي رجوع كند. شما مي‌توانيد انتخاب بهينه‌ساز از پيوند حلقه تودرتو را اينگونه تعميم دهيد: در صورتي كه يكي از وروديهاي پيوند بسيار كوچكتر از ديگري و ورودي بزرگتر داراي يك شاخص كلاستري روي ستون پيوند باشد، بهينه ساز اغلب پيوند حلقه تودرتو را برمي‌گزيند.

ادغام

در پيوند حلقه تودرتو، شاخص ستون پيوند در مورد جدول خارجي بي‌فايده است. بهرحال، زماني كه شما پرس و جوها و جداول را تنظيم مي‌كنيد، ممكن است هميشه ندانيد كه كدام جدول داخلي و كدام خارجي است، بنابراين بايد در هر دو جدول ورودي شاخصهاي كلاستري را روي ستونهاي پيوند ايجاد كرد. زماني كه هر دو وروديهاي پيوند روي ستون پيوند مرتب سازي مي‌شوند، سرويس دهنده SQL مي‌تواند از پيوند ادغامي استفاده كند، درست مانند موردي كه هر دو جدول داراي شاخصهاي كلاستري روي ستون پيوند باشند.پيوند ادغامي را مي‌توان همچون تركيب دويست مرتب سازي شده از مقادير تصور كرد. فرض كنيد داراي دو سري از اطلاعات پيمانكاري هستيد. يك سري شامل قراردادهاي مهم مي‌باشد كه هر پيمانكاري آن را امضا كرده است و دومين سري توصيف هريك از پروژه‌هايي است كه پيمانكار بر روي آن كار مي‌كند بنابراين، شما اساساً نياز به يك پل ارتباطي ميان اين دو سري اطلاعات داريد.پرس و جوي 1 را در نظر بگيريد: اگر جدول كارمندان و جدول سفارشات در ستون EmployeeID شاخصهاي كلاستري داشته باشند، سرويس دهنده SQL مي‌تواند پيوند ادغامي را اجرا كند. شبه كد مربوطه در مورد اجراي ادغامي سرويس دهنده SQL چيزي شبيه اين عبارات خواهد بود:

GET one Orders row and one Employees row
DO (until one input is empty);
      IF EmployeeID values are equal
           Return values from both rows
           GET next Orders row
      ELSE IF Orders.EmployeeID <> Employees.EmployeeID
           GET next Employees row
      ELSE GET next Orders row

بهينه ساز پرس و جو معمولاً استراتژي پيوند ادغامي را زماني انتخاب مي كند كه هر دو وروديها قبلاً در ستون پيوند مرتب شده باشند. اگر هر دو ورودي قبلاً مرتب شده باشند، در صورتي كه پيوند يك به چند باشد استفاده از I/O كمتري براي پردازش پيوند ادغامي ضروريست. پيوند ادغامي چند به چند (M:N) به جاي كنار گذاشتن سطرها كه معمولاً انجام مي‌دهد، آنها را در يك جدول موقتي ذخيره مي‌كند. اگر داده‌ها شامل مقادير تكراري از هر دو ورودي باشند، هنگامي كه سرويس دهنده SQL هر مقدار تكراري را از اولين ورودي پردازش مي‌كند، دومين ورودي بايد به ابتداي مقادير تكراري در جدول موقت بازگردد. بهرحال، در اكثر موارد، سرويس دهنده SQL از پيوند ادغامي استفاده نخواهد كرد مگر اينكه حداقل يكي از ستونهاي پيوند Unique باشد.در اينجا مثالي از پيوند دو جدول يكي با شاخص و ديگري بدون شاخص Unique آورده شده است.

LISTING 1: Joins Two Tables With and Without Unique Index

-- Copy the two tables
SELECT * INTO o1 FROM orders
SELECT * INTO od1 FROM [order details]

-- Create the indexes.
CREATE CLUSTERED INDEX orders_index
ON o1(orderID)
CREATE CLUSTERED INDEX OD_index
ON od1(orderID)

-- Look at the query plan for the query.
SELECT * FROM o1 JOIN od1
ON o1.orderID = od1.orderID

-- Now recreate the clustered index on o1 as unique.
CREATE UNIQUE CLUSTERED INDEX orders_index ON o1(orderID)
WITH DROP_EXISTING

-- Now Look at the query plan for the query.
SELECT * FROM o1 JOIN od1 ON o1.orderID = od1.orderID

LISTING 2: Modified Listing 1

SELECT * INTO o2 FROM orders

SELECT * INTO od2 FROM [order details]

CREATE UNIQUE CLUSTERED INDEX orders_index ON o2(orderID)
CREATE INDEX OD_index ON od2(orderID)

-- Check the query plan to see the sort operation before
-- the merge join:
SELECT * FROM o2 JOIN od2
ON o2.orderID = od2.orderID

ليست 1 كپي‌هايي از جدول سفارشات و جدول جزئيات سفارشات در بانك اطلاعاتي Northwind ايجاد مي‌كند و يك شاخص كلاستري در OrderID هر دو جدول مي‌سازد. زماني كه شما ابتداً اين جدولها را پيوند مي‌دهيد و طرح پرس و جو را به نمايش مي‌گذاريد، خواهيد ديد كه سرويس دهنده SQL پيوند حلقه تودرتو را انتخاب مي‌كند. اگرچه ستون OrderID در جدول سفارشات Unique است، اما در صورتي كه Unique بودن را در تعريف شاخص مشخص نكنيد، بهينه ساز متوجه نخواهد شد كه مقادير كليدي Unique هستند. بنابراين هنگامي كه مجدداً شاخص كلاستري را در جدول سفارشات مي‌سازيد و مشخص كنيد كه آن شاخص بايد Unique باشد، طرح پرس و جوي اصلاح شده نشان مي‌دهد كه سرويس دهنده SQL از يك پيوند ادغامي استفاده مي‌كند. در برخي از حالتها، بهينه ساز سرويس دهنده SQL ممكن است به دليل به صرفه بودن تصميم بگيرد يكي از وروديها را قبل از پيوند مرتب كند و بعد پيوند ادغامي را اجرا نمايد. اگر ليست 1 را كمي تغيير دهيد به گونه‌اي كه شاخص اوليه كه در جدول جزئيات سفارشات ساخته مي‌شود كلاستري نباشد، طبق آنچه كه ليست 2 نمايش مي‌دهد، طرح پرس و جو عمل مرتب سازي را قبل از پيوند ادغامي نشان مي‌دهد.

جدولهايتان را بشناسيد

معمولاً، بهينه ساز پرس و جو تعيين مي‌كند كه سرويس دهنده SQL چه نوع پيوندي را استفاده خواهد كرد. شما مي‌توانيد پرس و جوي پيوند خود را نوشته و انتخاب استراتژي را به عهده SQL بگذاريد. بهرحال، دانستن نحوه اجراي پيوندها در سرويس دهنده SQL مي‌تواند انتخاب شما در مورد شاخصهاي مفيدتر كمك كند. بهينه ساز معمولاً در صورتي كه يكي از وروديهاي پيوند در مقايسه با ورودي ديگري كوچكتر و ورودي بزرگتر داراي يك شاخص كلاستري روي ستون پيوند باشد، از پيوند حلقه تودرتو استفاده مي‌كند. اگر هر دو ورودي در ستون پيوند مرتب شده باشند و بخصوص يكي از وروديها داراي شاخص كلاستري unique باشد نوع پيوند به احتمال زياد ادغامي خواهد بود.

هر دو پيوند ادغامي و حلقه تودرتو نياز به شاخصهاي مناسب در جدولها دارند. بهرحال، اگر برنامه كاربردي شما باعث شود كه كاربرها پرس و جوهاي ويژه بسازند، ممكن است در ابتدا ندانيد كه بهترين ستون براي شاخصها كدام است. سومين نوع پيوند، پيوند hash است كه باعث مي‌شود سرويس دهنده SQL به يك عملكرد پيوند بسيار خوب دست بيابد حتي زماني كه جداول شما از شاخصهاي مفيدي برخوردار نباشند.