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 نیز از این روش استفاده می شود با انجام این عمل، داده ها به صورت مرتب شده صعودی برگردانده می شوند و مقادیر هم اندازه هم بر اساس rowed مرتب می شوند. همچنین در این روش روی مقادیر مرتب شده عمل scan انجام میشود.
برای ملزم ساختن optimizer در انتخاب این روش(البته در صورت امکان)، می توانیم از hint، /*+ INDEX(table index) */ استفاده کنیم.
Index Skip Scan : زمانی که یک ایندکس با چند ستون می سازیم(ایندکس ترکیبی)، ممکن است پرس و جویی توسط کاربر زده شود که تنها به ستونهای اخر این ایندکس نیاز داشته باشد و هیچ اشاره ای به ستون اول نداشته باشد، در نسخه های قدیمی اوراکل، optimizer از این ایندکس برای پاسخدهی به این پرس و جو استفاده نمی کرد ولی در نسخه های جدیدتر، اوراکل با استفاده از مدل Index Skip Scan از ستونهای جلویی صرف نظر می کند و با همان ایندکس هم می تواند به این پرس و جو پاسخ دهد یعنی از این ایندکس به صورت منطقی دو subindexes می سازد تعداد subindexهای منطقی، به اندازه تعداد رکوردهای متمایز شده(distinct) ستون اول می باشد یعنی اگر ستون اول 100 رکورد داشته باشد که 50تای آن تکراری باشد، تعداد ایدکسهای منطقی، برابر با 50 خواهد شد. پس به راحتی می توانیم نتیجه بگیریم که اگر تعداد رکوردهای متمایزشده در ستون اول(leading column) کم باشد، این روش کارامد خواهد بود. به بیان دیگر، اگر کاردینالیتی ستون اول ایندکس کم باشد، index skip scan موثر خواهد بود.
از جمله فواید استفاده از این روش آن است که نیاز به تعداد ایندکس کمتری در سیستم خواهیم داشت که نهایتا منجر به صرفه جویی در مصرف فضا و نیز زمان نگهداری ایندکس می شود.
hint مربوط به این روش، /*+ INDEX_SS (table index) */ می باشد.
برای مثال فرض کنید که ایندکسی با نام indx_person با دو ستون sex,lname را بر روی جدولی با اسم person ساختیم و فردی چنین پرس و جویی را وارد می کند:
select sex,lnamefrom person where lname=’usefzadeh’
در این صورت به دلیل اینکه ستون اول ایندکس(sex) در پرس و جو نیامده است، optimizer از روش Index Skip Scan استفاده می کند به این صورت که یکبار فرض می کند sex=1 است و تمامی رکوردهای lname=’usefzadeh’ را نشان می دهد و بار دیگر با sex=2 مسئله را حل می کند.
select sex,lnamefrom person where lname=’usefzadeh’ and sex=1
union
select sex,lnamefrom person where lname=’usefzadeh’ and sex=2;
Index Full Scan : در این روش اوراکل همه سطرهای موجود در ایندکس را می خواند و نیز ممکن است با استفاده از rowid مشخص شده در ایندکس، به table نیز رجوع شود(TABLE ACCESS BY INDEX ROWID). هزینه sort را بسیار کاهش می دهد و در صورتی که در query مورد نظر sort داشته باشیم، این مورد برای سیستم بهتر است. زمانی که یک query در دستورش ORDER BY داشته باشد و نیز ستون مورد نظر هم nullable نباشد. معمولا زمانی که بخواهیم مقدار MIN/MAX یک ستون را پیدا کنیم، از روش INDEX FULL SCAN (MIN/MAX) برای این کار استفاده می شود.
- در صورتی که در شرط به ستونی که در ایندکس موجود است، اشاره شده باشد.
- در شرط به ستون خاصی اشاره نشده ولی ایندکس تمامی ستونهای اشاره شده در query را داشته باشد و نیز حداقل یکی از آن ستونها nullable نباشند در این صورت باز index full scan انجام میشود.
- همچنین زمانی که از (!= or <>) در پرس و جو استفاده شده باشد، این روش قابل استفاده است.
- زمانی که از عملگر like به صورت leading wildcard استفاده کرده باشیم(like ‘%ABC’)، معمولا از این روش پیمایش استفاده می شود.
Index Fast Full Scan : معمولا زمانی که ایندکسی تمامی ستونهای خواسته شده در یک پرس و جو را در خودش داشته باشد، optimizer را مجاب می کند تا از روش Index Fast Full Scan استفاده کند(البته نه همیشه) پس باید همه ستونهای select and where clauses در ایندکس موجود باشند و از طریق این روش، هیچگونه ارجاعی به 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 بهینه کننده را الزام کنیم تا در صورت امکان از این روش برای پاسخدهی به یک پرس و جوی خاص استفاده کند.
در چه صورتی full table scan در بانک صورت می پذیرد؟
الف) در صورتی که query ما تمامی رکوردهای جدول را بخواهد(بدون where باشد.)
ب)در صورتی که از هیچ ایندکسی استفاده نکرده باشیم یا ایندکس لازمه را نداشته باشیم.
ج)در صورتی که جدول ما آنقدر کوچک باشد که تعداد بلاکهای اشغالی ان از عدد پارامتر db_file_multiblock_read_count کوچکتر باشد.یعنی اینکه از حداقل تعداد بلاک ورودی/خروجی در هر بار، کمتر باشد.
د) اگر پارمتری که گفته شد(db_file_multiblock_read_count) با مقدار بالا تنظیم شده باشد.
در چه صورتی در مسیر دسترسی از Row ID Scans استفاده می کند؟
بهترین حالت ممکن می باشد به این صورت است که اگر بخواهیم از بین 100 رکوردی که داریم، row id یکی از رکوردها را انتخاب کنیم، و در مقابل شرط where قرار دهیم، با سرعت بسیار بالایی کار جستجو را انجام می دهد.
- در صورتی که عناصر داخل جدولی را حذف کنیم، بلاکهای مربوطه از used به unused تغییر حالت می دهند که در هنگام خواندن جدول مورد نظر، تمامی بلاکهای مربوط به این جدول، باید به داخل حافظه buffer cache بیایند که این باعث کندی شدید در سیستم می شود. برای حل این مشکل باید جدول را به جای دیگر انتقال دهیم یا باید جداول را shrink کنیم و یا tablespace را انتقال دهیم.
