ESC را فشار دهید تا بسته شود

زمیوس آموزش، یادگیری و سرگرمی

چرا ایندکس (Index) در Oracle بی‌اثر می‌شود و چگونه سرعت جستجو را برگردانیم؟

چرا ایندکس (Index) در Oracle بی‌اثر می‌شود و چگونه سرعت جستجو را برگردانیم

اگر با پایگاه داده Oracle کار کرده باشی، حتماً تجربه کردی که گاهی ایندکس‌های ساخته‌شده روی جدول‌ها، ناگهان اثر خودشان را از دست می‌دهند.

این یعنی Queryها به جای استفاده از ایندکس، به سراغ Full Table Scan می‌روند و سرعت به شدت پایین می‌آید.

در این مقاله آموزش اوراکل در بخش آموزش بهینه سازی کوئری SQL Tuning بررسی کنیم که چرا این اتفاق می‌افتد و چه کارهایی می‌توانیم برای بازگرداندن عملکرد ایندکس انجام دهیم.

قابلیت Automatic Indexing یعنی اوراکل خودش تصمیم می‌گیرد کجا ایندکس بسازد یا حذف کند تا سرعت Queryها افزایش یابد، بدون اینکه DBA وقتش را صرف کند.

پیشنهاد می کنم این مقاله زیر رو حتما مطالعه کنی.

در این مقاله شما می خوانید

دلایل اصلی بی‌اثر شدن ایندکس در Oracle

۱. آمار (Statistics) قدیمی یا ناقص

اوراکل برای تصمیم‌گیری در مورد استفاده از ایندکس، نیاز به آمار دقیق دارد.

اگر آمار جدول یا ایندکس قدیمی شود، Optimizer ممکن است مسیر اشتباهی را انتخاب کند.

💡 راه‌حل: استفاده از DBMS_STATS برای بروزرسانی آمار:

				
					BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => TRUE);
END;
/

۲. توابع روی ستون ایندکس‌شده

وقتی تابعی مثل TO_CHAR یا UPPER روی ستون ایندکس‌شده اعمال شود، مقدار واقعی ایندکس تغییر شکل می‌یابد و اوراکل دیگر نمی‌تواند آن را استفاده کند.

💡 راه‌حل: ساخت Function-Based Index:

				
					CREATE INDEX idx_hire_year
ON employees (TO_CHAR(hire_date, 'YYYY'));

۳. ناسازگاری نوع داده (Data Type Conversion)

اگر در شرط WHERE نوع داده مقدار و ستون متفاوت باشد، Oracle مجبور به تبدیل ضمنی می‌شود و ایندکس را کنار می‌گذارد.

💡 راه‌حل: همیشه نوع داده را همانند ستون استفاده کن:

				
					SELECT * FROM customers
WHERE customer_id = 1001; -- be jaye '1001'

۴. عدم Selectivity مناسب

اگر شرط WHERE تعداد زیادی رکورد را برگرداند (مثل ۹۵٪ جدول)، استفاده از ایندکس منطقی نیست و Optimizer ترجیح به Full Table Scan می‌دهد.

۵. استفاده از Hintهای اجباری FTS

گاه برنامه‌نویس برای تست یا اجبار، از Hintهایی مثل FULL() در Query استفاده کرده که ایندکس را کاملاً بی‌اثر می‌کند.

💡 راه‌حل: حذف یا اصلاح Hint.

۶. ایندکس غیرقابل دسترس (UNUSABLE)

بعد از عملیات‌هایی مثل ALTER TABLE ... MOVE یا حذف پارتیشن، ایندکس ممکن است وضعیت UNUSABLE پیدا کند.

💡 بررسی و تعمیر:

				
					SELECT index_name, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

ALTER INDEX idx_emp_name REBUILD;

🛠 روش‌های بازگرداندن اثر ایندکس

  • بروزرسانی Statistics با DBMS_STATS
  • استفاده از Function-Based Index برای ستون‌هایی که تابع رویشان اجرا می‌شود
  • هماهنگی نوع داده در شرط WHERE
  • اجبار به استفاده از ایندکس با Hint درست:
				
					SELECT /*+ INDEX(employees idx_emp_name) */
FROM employees
WHERE last_name = 'King';
  • بازسازی ایندکس در صورت UNUSABLE بودن
  • بررسی Plan با DBMS_XPLAN.DISPLAY

مثال کاربردی برای بازگرداندن اثر ایندکس

فرض کن جدول SALES با ۱۰ میلیون رکورد داری:

				
					CREATE INDEX idx_sales_date ON sales (sale_date);

اگر این Query را اجرا کنی:

				
					SELECT * FROM sales
WHERE TO_CHAR(sale_date, 'YYYY-MM-DD') = '2025-11-01';

🔻 ایندکس بی‌اثر می‌شود چون تابع روی ستون اعمال شده

✅ راه‌حل:

				
					CREATE INDEX idx_sales_date_char 
ON sales (TO_CHAR(sale_date, 'YYYY-MM-DD'));

سوالات متداول درباره روش‌های بازگرداندن اثر ایندکس در اوراکل

معمول‌ترین علت استفاده نشدن ایندکس در Oracle قدیمی بودن یا ناقص بودن آمار (Statistics) است.

وقتی Optimizer اطلاعات دقیق از حجم داده یا Selectivity ستون‌ها نداشته باشد، ممکن است تصمیم به Full Table Scan بگیرد.

این مشکل با اجرای DBMS_STATS به‌روز می‌شود.

بله. وقتی تابع روی ستون ایندکس‌شده اجرا می‌شود، مقدار واقعی ایندکس تغییر شکل پیدا می‌کند و Oracle دیگر نمی‌تواند از آن استفاده کند.

راه‌حل، ایجاد Function-Based Index است که مقدار تبدیل‌شده را ایندکس کند.

ساده‌ترین راه استفاده از دستور زیر است:

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE last_name = ‘King’;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

اگر در ستون عملیات (Operation) عبارت INDEX RANGE SCAN یا INDEX UNIQUE SCAN دیده شود، یعنی ایندکس استفاده شده است.

وقتی ایندکس به دلیل عملیات‌هایی مثل ALTER TABLE ... MOVE یا حذف پارتیشن، UNUSABLE می‌شود، Oracle نمی‌تواند از آن استفاده کند. برای فعال‌سازی مجدد:

ALTER INDEX idx_emp_name REBUILD;

در صورت زیاد بودن رکوردها، بهتر است ONLINE REBUILD استفاده شود تا عملیات بدون downtime انجام شود.

جمع‌بندی

ایندکس ابزار قدرتمندی برای تسریع جستجوست، ولی باید علت بی‌اثر شدنش را دقیق شناخت: از آمار قدیمی گرفته تا استفاده از توابع یا ناسازگاری نوع داده.

با پیاده‌سازی نکات بالا، می‌توانی همیشه از عملکرد ایندکس‌ها مطمئن باشی و Queryها را سریع‌تر اجرا کنی.

📥 اگر سوالی داری در مورد روش‌های بازگرداندن اثر ایندکس در Oracle داری، در بخش کامنت‌ها بپرس.

سؤالی درباره این مقاله داری؟

اگر نکته‌ای در این مقاله برات مبهم بود یا خواستی بیشتر بدونی، همین حالا برام بنویس تا دقیق و صمیمی پاسخت رو بدم — مثل یه گفت‌وگوی واقعی 💬

برو به صفحه پرسش و پاسخ

میثم راد

من یه برنامه نویسم که حسابی با دیتابیس اوراکل رفیقم! از اونایی ام که تا چیزی رو کامل نفهمم،ول کن نیستم، یادگرفتن برام مثل بازیه، و نوشتن اینجا کمک می کنه تا چیزایی که یاد گرفتم رو با بقیه به شریک بشم، با هم پیشرفت کنیم.

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *