Oracle Indexing কৌশল: সঠিক Index বেছে নেওয়া (আর কখন করবেন না তা জানা)

কোয়েরি ধীর হলে মানুষ সবার আগে যেটির দিকে হাত বাড়ায় তা হলো index, আর দ্রুত হলে যেটির কথা সবার শেষে ভাবে সেটিও index। দুটো প্রবৃত্তিই ঝামেলা ডেকে আনে। index কোনো বিনামূল্যের পারফরম্যান্স নয় - এটি একটি লেনদেন: দ্রুত read-এর বিনিময়ে ধীর write আর বেশি জায়গা। খুব কম দিলে কোয়েরি লাখ লাখ সারি স্ক্যান করে; খুব বেশি দিলে প্রতিটি insert হামাগুড়ি দেয় আর optimizer অপশনের সমুদ্রে ডোবে। প্রোডাকশন ডেটাবেজ টিউন করার আঠারো বছর পর আমার মত হলো, indexing আসলে প্রতিটি index টাইপ জানার ব্যাপার কম, বরং সচেতনভাবে নেওয়া কয়েকটি ভালো সিদ্ধান্তের ব্যাপার বেশি। এই গাইডে থাকছে যেসব index টাইপ গুরুত্বপূর্ণ, সেগুলোর মধ্যে কীভাবে বেছে নেবেন, composite-কলাম ক্রম, function-based index, আর - সমান গুরুত্বপূর্ণ - কখন index করবেন না।

১. প্রতিটি Index যে লেনদেন করে

যেকোনো index টাইপের আগে, আপনি যে চুক্তিতে যাচ্ছেন তা বুঝে নিন। একটি index হলো একটি আলাদা, সাজানো কাঠামো যা Oracle-কে পুরো টেবিল স্ক্যান না করে সারি খুঁজে পেতে দেয়। এটি read দ্রুত করে। কিন্তু প্রতিটি INSERT, (indexed কলামের) UPDATE, আর DELETE-কে প্রতিটি প্রভাবিত index-ও রক্ষণাবেক্ষণ করতে হয় - তাই write ধীর হয় এবং ডেটাবেজ বেশি জায়গা ও বেশি redo ব্যবহার করে।

এ কারণেই 'একটা index যোগ করো' স্বয়ংক্রিয়ভাবে ভালো পরামর্শ নয়। read-ভারী রিপোর্টিং টেবিলে বেশি index সাধারণত সাহায্য করে। পনেরোটি index-ওয়ালা একটি write-ভারী OLTP টেবিলে প্রতিটি insert ষোলোটি কাঠামো আপডেট করে, আর টেবিলটি insert-bound হয়ে পড়তে পারে। সঠিক index সংখ্যা হলো সবচেয়ে কম সংখ্যা যা আপনার আসল কোয়েরি প্যাটার্নকে সেবা দেয় - আর সেই সংখ্যাটি খুঁজে পাওয়াই আসল দক্ষতা।

২. B-tree: ডিফল্ট, আর সাধারণত সঠিক

ব্যালান্সড-ট্রি (B-tree) index হলো Oracle-এর ডিফল্ট এবং বেশিরভাগ ক্ষেত্রেই সঠিক পছন্দ। এটি high-cardinality কলামে দুর্দান্ত - অনেক স্বতন্ত্র মানওয়ালা কলাম, যেমন একটি primary key, একটি ইমেইল ঠিকানা, একটি অর্ডার নম্বর, একটি তারিখ।

-- The everyday index
CREATE INDEX ord_customer_ix ON orders(customer_id);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX cust_email_uix ON customers(email);

B-tree index স্বয়ংক্রিয়ভাবে ব্যালান্সড থাকে, range scan (BETWEEN, <, >) ও equality দুটোই সমানভাবে সাপোর্ট করে, আর OLTP পারফরম্যান্সের মেরুদণ্ড। সন্দেহ হলে সেটি B-tree। আকর্ষণীয় সিদ্ধান্তগুলো কলাম আর ক্রম নিয়ে, টাইপ নিয়ে নয়।

৩. Bitmap: ওয়্যারহাউসে শক্তিশালী, OLTP-তে বিপজ্জনক

একটি bitmap index প্রতিটি স্বতন্ত্র মানের জন্য একটি bitmap সংরক্ষণ করে - কোন সারিগুলোতে সেটি আছে তার। এটি low-cardinality কলামের জন্য দারুণ - লিঙ্গ, স্ট্যাটাস, অঞ্চল, হ্যাঁ/না ফ্ল্যাগ - বিশেষত যখন কোয়েরি এমন কয়েকটি কলামকে AND/OR দিয়ে মেলায়, যেমন অ্যানালিটিক্স কোয়েরি করে।

-- Great in a read-mostly data warehouse
CREATE BITMAP INDEX sales_region_bx ON sales(region);
CREATE BITMAP INDEX sales_channel_bx ON sales(channel);
-- WHERE region='EAST' AND channel='ONLINE' -> bitmaps combined very fast

কিন্তু একটি ফাঁদ আছে যা মানুষকে বাজেভাবে ধরে: bitmap index আর OLTP মেলে না। যেহেতু একটি একক bitmap সেগমেন্ট অনেক সারিকে কভার করে, একটি সারির DML অন্য writer-দের জন্য একটি পুরো সারির পরিসর lock করে দিতে পারে। concurrent insert ও update আছে এমন টেবিলে bitmap index পঙ্গু করে দেওয়া lock contention ঘটায় (locking গাইড-এর enq: TX wait)। মূল নিয়ম: bitmap index-এর জায়গা read-mostly ওয়্যারহাউস আর রিপোর্টিং টেবিলে, কখনও হট ট্রানজ্যাকশনাল টেবিলে নয় - সেখানে low-cardinality কলামেও B-tree ব্যবহার করুন।

৪. Composite Index: কলামের ক্রমই সব

একটি composite (মাল্টি-কলাম) index একসঙ্গে কয়েকটি কলাম কভার করে। ভালোভাবে ব্যবহার করলে এটি একটি কোয়েরিকে পুরোপুরি index থেকেই সন্তুষ্ট করতে পারে। কিন্তু কলামের ক্রম বদলে দেয় কোন কোয়েরিগুলোকে এটি সাহায্য করে - তার সবকিছু।

-- Index on (customer_id, order_date)
CREATE INDEX ord_cust_date_ix ON orders(customer_id, order_date);

এই index WHERE customer_id = :x AND order_date > :d-এর জন্য চমৎকার, আর শুধু WHERE customer_id = :x-এর জন্যও ব্যবহারযোগ্য। কিন্তু শুধু WHERE order_date > :d-এর জন্য এটি প্রায় অকেজো - কারণ leading কলাম (customer_id) predicate-এ নেই, আর একটি index হলো প্রথম কলাম অনুযায়ী সাজানো একটি ফোনবুক। পদবি অনুযায়ী সাজানো বইয়ে জুন মাসে জন্মানো সবাইকে দক্ষভাবে খুঁজে পাওয়া যায় না।

ডিজাইনের নিয়ম: equality predicate-এ ব্যবহৃত কলাম দিয়ে শুরু করুন, তারপর range কলাম; equality কলামগুলোর মধ্যে, কোয়েরি ভিন্ন হলে সবচেয়ে selective-টি আগে রাখুন। আর কোনো কোয়েরির WHERE ও SELECT কলাম কভার করার চেষ্টা করুন যাতে Oracle টেবিল স্পর্শ না করেই index থেকে উত্তর দিতে পারে (একটি 'index-only' scan) - সবচেয়ে বেশি মূল্যের টিউনিং জয়গুলোর একটি, আর পারফরম্যান্স টিউনিং গাইড-এর পদ্ধতির কেন্দ্রে।

৫. Function-Based Index: Expression-কে Indexable করা

last_name-এর ওপর একটি সাধারণ index WHERE UPPER(last_name) = 'KHAN'-এর জন্য কিছুই করে না - কলামে একটি ফাংশন প্রয়োগ করলে সেটি index থেকে লুকিয়ে যায়, full scan-এ বাধ্য করে। একটি function-based index বদলে EXPRESSION-টিকে index করে:

-- Now case-insensitive searches use an index
CREATE INDEX cust_uname_fbi ON customers(UPPER(last_name));
-- WHERE UPPER(last_name)='KHAN'  -> uses cust_uname_fbi

-- Also great for computed predicates and partial indexing
CREATE INDEX ord_open_fbi ON orders(CASE WHEN status='OPEN' THEN order_id END);
-- indexes only OPEN orders -> small, fast 'partial' index

Function-based index নীরবে 'index কেন ব্যবহার করছে না?' ধরনের একটি বিশাল শ্রেণির সমস্যা সমাধান করে: date truncation, concatenation, case-insensitive সার্চ, আর computed ফ্ল্যাগ। একটিই সতর্কতা হলো সামঞ্জস্য - কোয়েরির expression অবশ্যই index-এর expression-এর সঙ্গে হুবহু মিলতে হবে।

৬. কখন Index করবেন না

সংযমই ভালো indexing-এর অর্ধেক। index যোগ করবেন না যখন:

• কলামটি কদাচিৎ কোয়েরি হয়। কোনো কোয়েরি ব্যবহার করে না এমন index নিছক বাড়তি বোঝা - শূন্য read সুবিধায় write খরচ আর জায়গা।
• টেবিলটি ছোট। Oracle একটি ছোট টেবিল index নেভিগেট করার চেয়ে দ্রুত ফুল-স্ক্যান করে। index স্কেলে গুরুত্বপূর্ণ, ২০০-সারির লুকআপে নয়।
• হট OLTP টেবিলে কলামটি low-cardinality। B-tree (কম selectivity) বা bitmap (locking) কোনোটাই ভালো খাপ খায় না; প্রায়ই উত্তর হলো কোনো index-ই নয়।
• একটি কোয়েরি টেবিলের বড় একটি অংশ ফেরত দেয়। কোনো কোয়েরি ৪০% সারি পড়লে ফুল স্ক্যান এমনিতেই index-কে হারায় - optimizer এটি জানে, এ কারণেই 'index আছে কিন্তু ব্যবহার হচ্ছে না' প্রায়ই সঠিক আচরণ, বাগ নয়।
• আপনি কভারেজ ডুপ্লিকেট করছেন। (A)-এর ওপর একটি index অপ্রয়োজনীয় যদি আপনার আগে থেকেই (A, B) থাকে - composite-টি ইতিমধ্যে leading-কলাম A সেবা দেয়।

৭. মৃত Index খুঁজে সরানো - নিরাপদে

বছরের পর বছর টেবিলে এমন index জমে যা কেউ ব্যবহার করে না - প্রতিটি প্রতিটি insert-এর ওপর কর বসায়। নিরাপদে সেগুলো খুঁজে পাওয়া একটি দুই-ধাপের প্রক্রিয়া, আর invisible index হলো পেশাদারের হাতিয়ার।

-- Which indexes are actually being used? (monitor over a real workload period)
SELECT name AS index_name, total_access_count, last_used
FROM   dba_index_usage
WHERE  owner='SALES' ORDER BY total_access_count;

-- Suspect an index is unused? Make it INVISIBLE (optimizer ignores it,
-- but it's still maintained) - a reversible test:
ALTER INDEX ord_old_ix INVISIBLE;
-- ...run for a week; if nothing breaks and nothing slows...
DROP INDEX ord_old_ix;
-- panic? instantly reverse:  ALTER INDEX ord_old_ix VISIBLE;

Invisible index আপনাকে একটি index 'সফট-ড্রপ' করতে আর আসল drop-এর ঝুঁকি ছাড়াই এর প্রভাব দেখতে দেয় - কোনো জরুরি কোয়েরি ধীর হলে একটি কমান্ড সেটি ফিরিয়ে আনে। drop করে আশায় থাকার চেয়ে এটি অনেক নিরাপদ, আর একটি নতুন index তার write খরচে প্রতিশ্রুতিবদ্ধ হওয়ার আগে সেটি সাহায্য করে কি না তা পরীক্ষা করারও এটাই উপায়। (DBA_INDEX_USAGE-এর মাধ্যমে Index Usage Tracking হলো আসল ব্যবহার দেখার আধুনিক, কম-ওভারহেড উপায়।)

৮. একটি বাস্তব কেস: পনেরোটি Index, ধীর Insert

এক ক্লায়েন্টের অর্ডার-ইনটেক টেবিল এমন অবস্থায় নেমেছিল যে লোডের নিচে insert লক্ষণীয়ভাবে সময় নিচ্ছিল, চেকআউট ফ্লোকে হুমকিতে ফেলছিল। টেবিলটি বহন করছিল পনেরোটি index - বছরের পর বছর জমা, প্রতিটি কোনো এক রিপোর্ট দ্রুত করতে যোগ করা, কখনও একটিও সরানো হয়নি। প্রতিটি insert ষোলোটি কাঠামো রক্ষণাবেক্ষণ করছিল।

সমাধানটি ছিল বিয়োগ, নিরাপদে করা। Index Usage Tracking দেখাল যে পনেরোটির মধ্যে ছয়টিকে এক মাসে কোনো কোয়েরি স্পর্শ করেনি। আমরা সেই ছয়টি invisible করলাম, এক সপ্তাহ চালালাম কোনো কোয়েরি regression ছাড়াই, তারপর সেগুলো drop করলাম। insert সময় তীব্রভাবে কমল আর চেকআউটের চাপ কমে এল। বাকি নয়টির মধ্যে দুটি ছিল composite index-এর অপ্রয়োজনীয় leading-কলাম ডুপ্লিকেট, তাই সেগুলোও গেল। টিম যে শিক্ষাটি ধরে রাখল সেটিই রাখার মতো: একটি index প্রতিটি write-এর ওপর একটি স্থায়ী খরচ, তাই প্রশ্ন কখনও কেবল 'এই কোয়েরিতে কি একটি index সাহায্য করবে?' নয়, বরং 'এই index কি এখনও তার জায়গা অর্জন করে?' - আর invisible-index কৌশল আপনাকে প্রোডাকশন নিয়ে জুয়া না খেলেই তার উত্তর দিতে দেয়।

৯. একটি হাতে-কলমে উদাহরণ: Covering Index ডিজাইন

সেকশন ৪ বলেছিল কোনো কোয়েরির WHERE ও SELECT কলাম কভার করা সবচেয়ে বড় জয়গুলোর একটি। একটি আসল কোয়েরিতে সেটি দেখতে কেমন তা এখানে দেখাচ্ছি, কারণ তত্ত্বের চেয়ে মেকানিক্স নকল করা সহজ।

এক ক্লায়েন্টের একটি অর্ডার-হিস্টরি স্ক্রিন প্রতিটি পেজ ভিউতে এই কোয়েরি চালাত, আর buffer gets-এর হিসাবে এটি ছিল AWR-এর শীর্ষ SQL:

SELECT order_id, order_date, total_amount
FROM   orders
WHERE  customer_id = :cust AND status = 'OPEN'
ORDER  BY order_date DESC;

-- The real plan, straight from the cursor cache:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-- INDEX RANGE SCAN   ord_customer_ix        (customer_id only)
-- TABLE ACCESS BY INDEX ROWID BATCHED  orders   <- ~1,400 buffer gets
-- SORT ORDER BY

index দ্রুত গ্রাহকের এন্ট্রিগুলো খুঁজে পেল। কিন্তু Oracle এরপর STATUS যাচাই করতে আর বাকি কলামগুলো আনতে বারোশো বার টেবিলে গেল, আর টিকে থাকা সারিগুলো sort করল। টেবিল ভিজিটগুলোই ছিল পুরো খরচ।

covering সংস্করণটি প্রয়োজনীয় প্রতিটি কলাম index-এ রাখে, এমন এক ক্রমে যা sort-টিকেও বাদ দিয়ে দেয়:

CREATE INDEX ord_cust_cover_ix ON orders
  (customer_id, status, order_date DESC, total_amount, order_id);

-- New plan: INDEX RANGE SCAN ord_cust_cover_ix ... 6 buffer gets
-- No table access. No sort. The index IS the answer.

buffer gets প্রতি এক্সিকিউশনে প্রায় ১,৪০০ থেকে ৬-এ নেমে এল, আর স্ক্রিনটি ঢিমেতাল থেকে তাৎক্ষণিক হয়ে গেল। আর কিছুই বদলায়নি।

অভিজ্ঞতা থেকে দুটি সতর্কতা। একটি covering index চওড়া, তাই এটি সরু index-এর চেয়ে প্রতিটি write-এর ওপর বেশি কর বসায় - কৌশলটি সত্যিকারের হট কোয়েরির জন্য তুলে রাখুন, প্রতিটি রিপোর্টের জন্য নয়। আর covering index তৈরি হওয়ার পর পুরনো একক-কলাম ord_customer_ix অপ্রয়োজনীয় হয়ে গেল এবং সেকশন ৭-এর invisible-index রুটিনের মাধ্যমে অবসরে পাঠানো হলো।

চওড়া composite index-এ আরেকটি জয় লুকিয়ে আছে: compression। leading কলামগুলো প্রচুর পুনরাবৃত্ত হয় - একটি customer_id অনেক এন্ট্রিতে থাকে - আর CREATE INDEX ... COMPRESS 1 প্রতিটি পুনরাবৃত্ত prefix একবারই সংরক্ষণ করে। সিদ্ধান্ত নেওয়ার আগে Oracle-কেই জিজ্ঞেস করুন কতটা বাঁচবে: শান্ত সিস্টেমে ANALYZE INDEX ord_cust_cover_ix VALIDATE STRUCTURE চালান (এটি অল্প সময়ের জন্য টেবিল lock করে), তারপর INDEX_STATS থেকে OPT_CMPR_COUNT ও OPT_CMPR_PCTSAVE পড়ুন। আমি ৪০% আকার হ্রাস দেখেছি, যার মানে index-এর আরও বড় অংশ ডিস্কের বদলে buffer cache-এ বাস করে।

১০. Drop করার আগে: ব্যবহারের প্রমাণ সন্দেহবাদীর চোখে পড়ুন

সেকশন ৭ মেকানিক্স দিয়েছে - DBA_INDEX_USAGE, তারপর invisible, তারপর drop। সেই ভিউয়ের একটি শূন্যকে বিশ্বাস করার আগে জেনে নিন তিনটি উপায় যাতে এটি আপনাকে বিভ্রান্ত করতে পারে, কারণ প্রতিটিকেই আমি প্রায় একটি incident ঘটাতে দেখেছি।

Foreign-key index শূন্য কোয়েরি ব্যবহার দেখাতে পারে, তবু অপরিহার্য থাকতে পারে। child টেবিলের FK কলামের একটি index হয়তো কখনও একটি SELECT-ও সেবা দেয় না, কিন্তু এটি ছাড়া parent টেবিলে delete ও key update child টেবিলের ওপর full table lock নেয় - locking গাইড-এর সেই outage প্যাটার্ন। প্রতিটি drop প্রার্থীকে আগে DBA_CONS_COLUMNS-এর সঙ্গে মিলিয়ে নিন।

Unique index constraint প্রয়োগ করে, শুধু কোয়েরি নয়। primary key বা unique constraint-এর পেছনের একটি index কাঠামোগত। DBA_INDEXES-এ UNIQUENESS='UNIQUE' আছে এমন যেকোনো কিছুকে নিষিদ্ধ ধরে নিন যতক্ষণ না যাচাই করেছেন কী তার ওপর নির্ভর করে।

Usage tracking-এর একটি সময়সীমা আছে। যে index শুধু quarter-end close-এ কাজে লাগে সেটি বছরে চারবার চলে, আর এক মাসের পর্যবেক্ষণ উইন্ডো শপথ করে বলবে সেটি মৃত। আপনার মনিটরিং সময়কাল - বা অন্তত invisible পর্যায় - সিস্টেমের প্রতিটি business cycle জুড়ে থাকতে হবে।

আমার প্রি-ড্রপ চেকলিস্ট, ক্রমানুসারে:

1. index-এর কলামগুলো DBA_CONS_COLUMNS-এর সঙ্গে মেলান - PK, unique বা foreign-key constraint-এর পেছনের যেকোনো index বাদ দিন।
2. নিশ্চিত করুন usage উইন্ডো month-end ও quarter-end প্রসেসিং কভার করেছে।
3. DBMS_METADATA.GET_DDL('INDEX', ...) দিয়ে হুবহু CREATE INDEX স্টেটমেন্টটি সংরক্ষণ করুন যাতে পুনর্নির্মাণ এক পেস্ট দূরে থাকে।
4. ALTER INDEX ... INVISIBLE করুন এবং অন্তত একটি পূর্ণ business cycle অপেক্ষা করুন।
5. Drop করুন - আর সংরক্ষিত DDL তবু আরও একটি quarter রেখে দিন।

সাধারণ জিজ্ঞাসা (FAQ)

Oracle-এ কখন B-tree আর কখন bitmap index ব্যবহার করব?

high-cardinality কলাম এবং concurrent write আছে এমন যেকোনো টেবিলের জন্য B-tree (ডিফল্ট) ব্যবহার করুন - কার্যত সব OLTP। bitmap শুধু read-mostly ডেটা ওয়্যারহাউসে low-cardinality কলামের জন্য ব্যবহার করুন, যেখানে একাধিক ফিল্টার জুড়ে এগুলো দক্ষভাবে মেলে। হট ট্রানজ্যাকশনাল টেবিলে কখনও bitmap index দেবেন না: এগুলোর ওপর concurrent DML মারাত্মক lock contention তৈরি করে।

Composite index-এ কলামের ক্রম কি গুরুত্বপূর্ণ?

অসম্ভব রকম। (A, B)-এর ওপর একটি index সেই কোয়েরিগুলোকে কাজে লাগে যেগুলো A-তে, বা A ও B-তে ফিল্টার করে, কিন্তু শুধু B-তে ফিল্টার করা কোয়েরিতে নয় - যেমন পদবি অনুযায়ী সাজানো ফোনবুক প্রথম নাম খুঁজতে অকেজো। equality predicate-এ ব্যবহৃত এবং সবচেয়ে selective কলাম দিয়ে শুরু করুন, আর index-only scan-এর জন্য কোয়েরির WHERE ও SELECT কলাম কভার করার চেষ্টা করুন।

function-based index কী?

একটি কাঁচা কলামের বদলে একটি expression-এর ওপর index, যেমন UPPER(last_name) বা TRUNC(order_date)। এটি এমন predicate-কে - যা কলামে একটি ফাংশন প্রয়োগ করে এবং সাধারণ index থেকে কলামটি লুকিয়ে full scan-এ বাধ্য করত - index ব্যবহার করতে দেয়। কোয়েরির expression অবশ্যই index-এর expression-এর সঙ্গে হুবহু মিলতে হবে।

index কি খুব বেশি হতে পারে?

হ্যাঁ। প্রতিটি insert, তার কলামের update, আর delete-এ প্রতিটি index রক্ষণাবেক্ষণ করতে হয়, তাই অতিরিক্ত index write ধীর করে, জায়গা ও redo খরচ করে, আর optimizer-কে যাচাই করার জন্য আরও প্ল্যান দেয়। unused index সরালে write-ভারী টেবিলগুলো প্রায়ই লক্ষণীয়ভাবে দ্রুত হয়। নিরাপদে খুঁজে সরাতে Index Usage Tracking ও invisible index ব্যবহার করুন।

একটি index drop করা নিরাপদে কীভাবে পরীক্ষা করব?

ALTER INDEX ... INVISIBLE দিয়ে সেটিকে invisible করুন। optimizer তখন সেটি উপেক্ষা করে (যেন drop হয়েছে) অথচ Oracle সেটি রক্ষণাবেক্ষণ করে চলে, তাই আপনি কিছুদিন আপনার আসল workload চালিয়ে regression খেয়াল করতে পারেন। কিছু না ভাঙলে DROP করুন; কোনো কোয়েরি ধীর হলে ALTER INDEX ... VISIBLE সঙ্গে সঙ্গে ফিরিয়ে আনে - drop করে আশায় থাকার চেয়ে অনেক নিরাপদ।