index scan و table scan

 در هنگام اجرای پرس و جو، بعد از مراحل پارس دستور، بهینه کننده با توجه به آمارهایی که از objectها در اختیار دارد،باید محاسباتی را انجام دهد و در صورت امکان، فرم دستور را تغییر دهد و در نهایت هم برای آن دستور، یک نقشه اجرایی بسازد. نقشه اجرایی مشخص می کند که برای اجرای دستور، باید به چه اشیاهایی رجوع شود همچنین نحوه دستیابی به اطلاعات آن اشیاها را نیز مشخص می کند.

در این قسمت چند مورد از متدهای دستیابی به اشیا که توسط بهینه کننده در نقشه اجرایی مشخص می شود، به طور اختصار توضیح داده خواهند شد.

 

full table scan :

این متد در موراد زیر برگزیده می شود:

الف) در صورتی که پرس و جو تمامی اطلاعات جدول را بخواهد.

ب)در صورتی که از هیچ ایندکسی استفاده نکرده باشیم یا ایندکس مناسبی موجود نباشد.

ج)در صورتی که جدول آنقدر کوچک باشد که تعداد بلاکهای اشغالی ان از عدد پارامتر db_file_multiblock_read_count کوچکتر باشد. یعنی از حداقل تعداد بلاک ورودی/خروجی در هر بار، کمتر باشد.

 

Row ID Scan:

بهترین حالت ممکن برای دسترسی به اطلاعات، دسترسی به ان با داشتن مشخصات فیزیکی(rowid) می باشد. یعنی از بین چندین رکوردی که وجود دارد، rowid یکی از رکوردها انتخاب شود و در مقابل شرط where قرار گیرد.

Index Unique Scan : معمولا در زمان ایجاد کلید اصلی یا unique key، به طور خودکار یک ایندکس بر روی آنها ساخته می شود که این ایندکس index unique نامیده می شود هر موقع این ایندکس در مسیر دسترسی قرار بگیرد و توسط optimizer انتخاب شود، نوع عملش Index Unique Scan نامیده می شود. در این روش معمولا یک سطر از ایندکس توسط اوراکل خوانده می شود. این روش، سریع ترین روش برای برگرداندن تک رکورد می باشد. پس شرایط اصلی رجوع optimizer به ایندکس با این روش، به صورت زیر است:

·         ایندکس مرجع از نوع unique index باشد.

·          در پرس و جوی از عملگر مساوی استفاده شود یعنی شرط مساوی روی ستونی که ایندکس روی آن وجود دارد برای مثال WHERE id-person=30.

Index Range Scan: رایج ترین مدل پیمایش ایندکس در اوراکل index range scan می باشد و زمانی که شرط محدود کننده مقادیر در یک محدوده خاص در پرس و جو وجود داشته باشد، معمولا optimizer این نوع از پیمایش را در نقشه اجرایی پرس و جو تعیین می کند.

البته شرط آن می‏تواند علامت بزرگتر، کوچکتر، مساوی و ... را شامل شود((>, >=, <, <=, BETWEEN)). همچنین زمانی که از عملگر like به صورت trailing wildcard استفاده می کنیم(like ‘ABC%’) معمولا از روش index range scan استفاده می شود. در بعضی از مواقع، برای انجام یک ORDER BY clause نیز از این روش استفاده می شود با انجام این عمل، داده ها به صورت مرتب شده صعودی برگردانده می شوند و مقادیر هم اندازه هم بر اساس rowid مرتب می شوند.

برای ملزم کردن optimizer در انتخاب این روش(البته در صورت امکان)، می توانیم از hint، /*+ INDEX(table index) */ استفاده کنیم.

Index Skip Scan : زمانی که یک ایندکس با چند ستون می سازیم(ایندکس ترکیبی)، ممکن است پرس و جویی توسط کاربر زده شود که تنها به ستونهای اخر این ایندکس نیاز داشته باشد و هیچ اشاره ای به ستون اول نداشته باشد، در نسخه های قدیمی اوراکل، optimizer از این ایندکس برای پاسخدهی به این پرس و جو استفاده نمی کرد  ولی در نسخه های جدیدتر، اوراکل با استفاده از مدل Index Skip Scan از ستونهای جلویی صرف نظر می کند و با همان ایندکس هم می تواند به این پرس و جو پاسخ دهد در واقع، از ایندکس را به صورت منطقی subindexes می سازد تعداد subindexهای منطقی، به اندازه تعداد رکوردهای متمایز شده(distinct) ستون اول می باشد یعنی اگر ستون اول 100 رکورد داشته باشد که 50تای آن تکراری باشد، تعداد ایندکسهای منطقی، برابر با 50 خواهد شد.

 پس به راحتی می توانیم نتیجه بگیریم که اگر تعداد رکوردهای متمایزشده در ستون اول(leading column) کم باشد، این روش کارامد خواهد بود. به بیان دیگر، اگر کاردینالیتی ستون اول ایندکس کم باشد، index skip scan موثر خواهد بود.

برای مثال فرض کنید که ایندکسی با نام indx_person با دو ستون sex,lname را بر روی جدولی با اسم person ساختیم و فردی چنین پرس و جویی را وارد می کند:

select * from person  where lname='usefzadeh'

در این صورت به دلیل اینکه ستون اول ایندکس(sex) در پرس و جو نیامده است، optimizer می تواند از روش Index Skip Scan استفاده کند به این صورت که یکبار فرض می کند sex=1 است و تمامی رکوردهای lname='usefzadeh' را نشان می دهد  و بار دیگر با  sex=2 مسئله را حل می کند.

select * from person  where lname='usefzadeh'  and sex=1

union all

select *  from person  where lname='usefzadeh'  and sex=2;

از جمله فواید استفاده از این روش آن است که نیاز به تعداد ایندکس کمتری در سیستم خواهیم داشت که نهایتا منجر به صرفه جویی در مصرف فضا و نیز زمان نگهداری ایندکس می شود.

hint مربوط به این روش، /*+ INDEX_SS (table index) */ می باشد.

Index Full Scan :  در این روش اوراکل همه سطرهای موجود در ایندکس را می خواند و نیز ممکن است با استفاده از rowid مشخص شده در ایندکس، به table نیز رجوع شود(TABLE ACCESS BY INDEX ROWID). همچنین بلاکهای ایندکس، به صورت single به داخل حافظه آورده می شوند و برخلاف full table scan از  multiblock I/O استفاده نمی شود. این روش، هزینه sort را بسیار کاهش می دهد و در صورتی که پرس و جوی مورد نظر sort داشته باشد، این روش برای سیستم مناسب است. موارد کاربرد دیگری هم برای این روش قابل تصور می باشد:

·         در شرط به ستون خاصی اشاره نشده باشد ولی ایندکس تمامی ستونهای اشاره شده در پرس و جو را داشته باشد و نیز حداقل یکی از آن ستونها nullable نباشند.

·         از عملگرهای (!= or <>) در پرس و جو استفاده شده باشد.

·         از عملگر like به صورت leading wildcard استفاده شود (like '%ABC').

Index Fast Full Scan : معمولا زمانی که ایندکس تمامی ستونهای خواسته شده در یک پرس و جو را در خودش داشته باشد، optimizer گرایش دارد تا از روش Index Fast Full Scan استفاده کند پس باید همه ستونهای select  و where clause در ایندکس موجود باشند و از طریق این روش، هیچگونه ارجاعی به table نخواهیم داشت.

 ویژگی منحصر بفرد این مدل در مقایسه با بقیه index scanها در این است که از multiblock I/O استفاده می شود(بسته به اندازه پارامتر db_file_multiblock_read_count) یعنی برای انتقال ایندکس به بافرکش، با ایندکس همانند یک جدول برخورد می شود(full table scan) و بلاکها را به صورت parallel به داخل حافظه منتقل می کند. البته بلاکها بصورت unorder خوانده می شوند در صورتیکه در روش Index Full Scan بلاکها بصورت order خوانده می شوند.

 پس اگر دستور sqlای ORDER BY داشته باشد، نیاز به مرتب سازی خواهیم داشت. همچنین حداقل یک ستون در ایندکس باید NOT NULL باشد.

برای شمارش تعداد سطرها(count(*)) با شرطهای خاص، معمولا از fast full-index scan استفاده می شود. زمانیکه پرس و جو بیشتر از 10% تا 20% رکوردهای ایندکس برمی گرداند، این روش مناسب است.

همه این نکاتی که در مورد انتخاب این روش توسط optimizer گفته شده، به آمارهای ایندکس و جدول بستگی دارد و به خاطر متغیرهای زیادی که وجود دارد، به راحتی نمی توان در مورد انتخاب optimizer اظهار نظر کنیم. همچنین می توانیم با hintای به نام index_ffs بهینه کننده را الزام کنیم تا در صورت امکان از این روش برای پاسخدهی به یک پرس و جوی خاص استفاده کند.