همه چیز در مورد Index-ایندکس در اوراکل

تفاوت index و virtual index و cluster index در Oracle

• براي دوري از full-table scans and disk sorts بر روي جداول با ديتاي زياد از index استفاده مي كنيم. و باعث سريع شدن سرعت اجراي كوئري ها مي شود.

• استفاده از Index مورد استفاده براي تمام كاربران و session ها.

• استفاده از Virtual Indexes معمولا براي تست و تغيير استراتژي روي Indexing مي باشد. و فقط در آن session كه ايجاد مي شود قابل استفاده مي باشد.

• استفاده از cluster index در واقع چنين اصطلاحي بصورت مستقيم توسط اوراكل پشتيباني نمي شود. ما مي توانيد از اصطلاح indexed clusters كه بطور معمول index ايجاد شده بر روي يك cluster table مي باشد.در اوراكل “cluster” يعني جايي كه سطرهاي مختلف مي توانند در يك ديتابلاك مقيم مي شوند و يك راهي كه جداول وابسته بهم بصورت فيزيكي با هم اميخته شوند.

استفاده از [COMPUTE STATISTICS]

وقتی ما یک Index را ایجاد می کنید بصورت پیش فرض اوراکل بصورت B-tree indexes ایجاد می کند.
یک کلمه کلیدی است که به اوراکل می گوید هنگام ایجاد ایندکس یک مجموعه از آمار و آنالیز را جمع آوری کند که این آمار بیشتر توسط بهینه ساز استفاده شده وقتی که بعدا یک دستور اجرا می شود.

CREATE INDEX emp_idx ON emp (emp_id, emp_name) COMPUTE STATISTICS;

استفاده از [reverse-key]

برای افزایش سرعت در عملیات Insert مورد استفاده قرار می گیرد. مخصوصا برای حجم زیاد بارگذاری دیتا و Insert زیاد بسیار تاثیر گذار خواهد بود
همچنین هنگام استفاده از OPS, RAC and Grid باعث کاهش contention می شود.

create index reverse_idx on t2(c1) reverse;

استفاده از [NOSORT]

وقتی شما یک ایندکس را در Oracle ایجاد می کنید بصورت پیش فرض روی همان ستونی که ایجاد کرده بصورت مرتب شده ایجاد می شود که خود همین مسئله شمان زیادی را در صورت داشتن رکوردهای زیاد مصرف می کند که شما با استفاده از کلمه کلیدی می توانید جلوی اینکار را بگیرد. حال اگر فیلد شما بصورت مرتب شده می باشد از این کلمه کلیدی استفاده کنید.

استفاده از [Local/Global]

وقتی از پارتیشن بندی اوراکل استفاده می کنید. تعیین می کنید که ایندکس روی آن به چه صورت می باشد.

و Global برای رابطه های یک به چند (one-to-many) استفاده می شود و اجازه میدهد که یک ایندکس  به چندین table partition پارتیشن Map شود.

و Local برای رابطه های یک به یک one-to-one) mapping) بین ایندکس پارتیشن و table partition مورد استفاده قرار می گیرد.

CREATE INDEX year_idx
on all_fact (order_date)
LOCAL
(PARTITION name_idx1),
(PARTITION name_idx2),
(PARTITION name_idx3);

CREATE INDEX item_idx
on all_fact (item_nbr)
GLOBAL
(PARTITION city_idx1 VALUES LESS THAN (100)),
(PARTITION city_idx1 VALUES LESS THAN (200)),
(PARTITION city_idx1 VALUES LESS THAN (300));

استفاده از [DOMAIN/TEXT]

در خصوص domain index : بدین گونه می باشد که به توسعه دهنده این اجازه را میدهد که بتواند ساختار ایندکس خودش را در اوراکل ایجاد و هدف آن، پیاده سازی یکسری index type های جدیدی که بصورت استاندارد وجودندارد .خیلی از مواقع ما برای سیستم های خاص پردازش های خاصی نیاز داریم مثلا وقتی بخواهیم بر روی نوعی ازدیتاهای Special و video clip ها را استفاده کنیم.
نمونه خوب آن برای پیاده سازی text indexes می باشد که می خواهیم امکانات جستجو بر روی آیتم های که دارای متن های طولانی می باشد را فراهم کنیم.

چگونه می توانیم domain index را پیاده سازی کنیم؟

در ابتدا باید یک index type را تعریف کنیم که در واقع یک schema object می باشد که شامل همه چیزهای روتینی می باشد که برای یک ایندکس استفاده می شود و به سه قسمت کلی تقسیم می شود:

Index definition routines
Index maintenance routines
Index scan routines

یک مثال در خصوص text index:

CREATE INDEX my_docs_doc_idx ON my_docs(doc) INDEXTYPE IS CTXSYS.CONTEXT;

استفاده از [Invisible]

یکی از ویژگی های آن برای تست می باشد. بدین صورت که بهینه ساز دیگر از این ایندکس استفاده نمی کند.معمولا قبل از اینکه بخواهید آنرا کلا حذف کنید بدین صورت استفاده می کنید که تاثیر آنرا در ((Oracle Cost Based Optimizer(CBO)  بهینه ساز را متوجه بشوید.

ALTER INDEX emp_idx INVISIBLE;

ايندكس تكي و ايندكس هاي تركيبي

اگر بر روي جدول خود از ايندكس تكي و ايندكس هاي تركيبي استفاده كرده باشيد. بصورت معمول ايندكس تكي سريعتر از ايندكس هاي تركيبي scan مي شوند.

اگر همه اين ايندكس هاي بصورت تركيبي باشد، اوراكل دو روش براي ايندكس هاي تركيبي استفاده مي كند اولا بصورت Index join و دوما بصورت عملكردهاي data warehouse استفاده مي كند. روش دوم از bitmap index استفاده مي نمايد كه در عمليات Insert,update,delete كند مي باشد .

اين را بدانيد كه چندين ستون index B-tree سريعتر از multiple bitmap indexes مي باشد.

ایندکس گذاری بروری Number یا Varchar

اگر شما در دنياي اوراكل اين سوال را مطرح كنيد كه Index بروي Varchar2 بهتر است يا Index بر روي Number به شما جواب مي دهند كه سوال شما اشتباه مي باشد.

بستگي به طراحي شما وقتي نياز بود كه فيلدي را از نوع String قرار مي دهيد . جايي كه نياز بود فيلد را از نوع Number قرار مي دهيد. حال تصميم مي گيريد كه آيا Index نياز دارد يا نه. بعد براي آن از Index استفاده مي كنيد.

بصورت نرمال Index بر روي integer سريعتر ولي بستگي به سايز فيلد integer و varchar2 دارد.ولي بصورت كلي نمي توان گفت كدام پيشتاز و سريعتر است.
فراتر از اين مسئله، Index بر روي فيلدي با مقدار يونيك خيلي بهتر عمل مي كند تا فيلدي كه يونيك نمي باشد.

و در آخر براي اينكه بخواهيد مزايا و معايب هر كدام را بررسي كنيد بايد خيلي از ويژگي ها از قبيل index-organized tables, clustered tables, partitioning و غيره را نيز مورد ارزيابي قرار دهيد.

مقایسه Analyze index Vs gather_index_stats

بهتر است از Dbms_stats.gather_index_stats  به جای Analyze استفاده کنید، دلیلش در این لینک کامل با مثال شرح داده شده است.

هنگام حذف رکوردها عملکرد Dbms_stats بر روی دیتای واقعی که وجود دارد می باشد در صورتیکه Analyze بدین صورت نمی باشد.

نظر دوستان: Amir Jamiri Noor

استفاده از analyze صرفا برای chained rows ‌ و اصلاح ساختار استفاده میشه Validate Structure از نسخه 10g به بعد اطلاعات مورد نیاز optimizer از خروجی اطلاعات آماری dbms_stat استفاده میشود. درواقع درتوسعه جدید optimizer اطلاعات آماری Analyze غیر قابل استفاده و deprecate شده است.

نکته ها:

• اگر بر روي جدول خود از ايندكس تكي و ايندكس هاي تركيبي استفاده كرده باشيد. بصورت معمول ايندكس تكي سريعتر از ايندكس هاي تركيبي scan مي شوند. حال اگر همه اين ايندكس هاي بصورت تركيبي باشد.

• اوراكل از دو روش استفاده مي كند روش اول بصورت Index join  و روش دوم از bitmap index استفاده مي نمايد كه در عمليات Insert,update,delete كند مي باشد .

• حال اين را بدانيد كه چندين ستون index B-tree سريعتر از multiple bitmap indexes مي باشد

• در اوراكل CBO- Cost-Based Optimizer بصورت اتومات تشخيص مي دهد كه كجا و چه موقع از Index استفاده نمايد. حال در بعضي اوقات در شرايط خاص مي خواهيد كه اين عمل را شما مديريت كنيد و مي توانيد از hint ها استفاده نماييد.

• اگر جدول شما از ایندکس استفاده نکرد با اینکه ایندکس گذاری شده است، چیکار کنیم؟

  • ابتدا این دستور را اجرا کنید:

analyze TABLE yourtablename compute statistics FOR COLUMNS yourfieldname;

• • سپس اگر باز هنوز از ایندکس استفاده نکرد. مقدار PGA خود را تغییر دهید.

امیدوار هستم که مورد استفاده دوستان قرار گیرد.