ایندکس های مجازی اوراکل

ایندکس های مجازی اوراکل

 فرآیندهای بهینه‌سازی دستورات SQL غالباً نیازمند آزمایش نمودن شیوه‌های indexing گوناگون به منظور بررسی میزان تأثیرات مثبت و منفی آنها در عملیات اجرایی می‌باشد. تبعاً ایجاد index های اضافی پیچیدگی‌های خاص خود را به همراه دارد. وارد نمودن index های اضافی در جداول بزرگ هزینه‌های زیادی را چه از لحاظ زمانی و همچنین فضای ذخیره‌سازی بر فرآیند وارد می‌سازد. از طرفی این index های اضافی می‌توانند مورد استفاده دیگر session ها نیز قرار بگیرند، این امر می‌تواند بر کارایی بخش‌های دیگر application شما تأثیر منفی داشته باشد که بالتبع زمانی که شما در حال خطایابی در سامانه می‌باشید مشکل‌زا خواهد بود.

ایندکس های مجازی اوراکل بر خلاف index های معمول به هیچ بخشی مرتبط نیست بنابراین در اینجا زمان ایجاد و فضای ذخیره‌سازی مورد نیاز دو مبحث کاملاً غیر مرتبط می‌باشند. به علاوه توسط دیگر session ها نیز قابل استفاده نمی‌باشد که در نتیجه هیچگونه تأثیر منفی در کارایی عادی سامانه شما نخواهد داشت. در اینجا سعی داریم چگونگی استفاده از virtual index را به همراه یک مثال ساده شرح دهیم.

برای شروع یک جدول ایجاد می‌نماییم.

CREATE TABLE objects_tab AS SELECT * FROM all_objects;

ALTER TABLE objects_tab ADD (

  CONSTRAINT objects_tab_pk PRIMARY KEY (object_id)

);

EXEC DBMS_STATS.gather_table_stats(USER, ‘objects_tab’, cascade=>TRUE);

در صورتی که با استفاده از کلید اصلی یک query به جدول اعمال نماییم، می‌توانیم تأثیرات زیر را در فرآیند اجرایی مشاهده نماییم.

SQL> SET AUTOTRACE TRACEONLY EXP

SQL> SELECT * FROM objects_tab WHERE object_id = 10;

Execution Plan

———————————————————-

Plan hash value: 2097082964

———————————————————————————————-

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

———————————————————————————————-

|   ۰ | SELECT STATEMENT            |                |     ۱ |    ۹۲ |     ۲   (۰)| ۰۰:۰۰:۰۱ |

|   ۱ |  TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB    |     ۱ |    ۹۲ |     ۲   (۰)| ۰۰:۰۰:۰۱ |

|*  ۲ |   INDEX UNIQUE SCAN         | OBJECTS_TAB_PK |     ۱ |       |     ۱   (۰)| ۰۰:۰۰:۰۱ |

———————————————————————————————-

Predicate Information (identified by operation id):

—————————————————

   ۲ – access(“OBJECT_ID”=10)

SQL>

در صورت استفاده از یک ستون non-indexed برای اعمال query در جدول، یک پیمایش کامل را مشاهده خواهیم نمود.

SQL> SET AUTOTRACE TRACEONLY EXP

SQL> SELECT * FROM objects_tab WHERE object_name = ‘USER_TABLES’;

Execution Plan

———————————————————-

Plan hash value: 821620785

———————————————————————————

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

———————————————————————————

|   ۰ | SELECT STATEMENT  |             |     ۲ |   ۱۸۴ |   ۲۰۷   (۵)| ۰۰:۰۰:۰۳ |

|*  ۱ |  TABLE ACCESS FULL| OBJECTS_TAB |     ۲ |   ۱۸۴ |   ۲۰۷   (۵)| ۰۰:۰۰:۰۳ |

———————————————————————————

Predicate Information (identified by operation id):

—————————————————

   ۱ – filter(“OBJECT_NAME”=’USER_TABLES’)

SQL>

به منظور ایجاد ایندکس های مجازی بر روی این ستون، کافیست تنها عبارت NOSEGMENT را به دستور CREATE INDEX اضافه نمایید.

SQL> CREATE INDEX objects_tab_object_name_vi ON objects_tab(object_name) NOSEGMENT;

Index Created

SQL>

اگر query قبلی را تکرار نماییم مشاهده می‌شود که ایندکس های مجازی در optimizer وجود ندارد.

SQL> SET AUTOTRACE TRACEONLY EXP

SQL> SELECT * FROM objects_tab WHERE object_name = ‘USER_TABLES’;

Execution Plan

———————————————————-

Plan hash value: 821620785

———————————————————————————

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

———————————————————————————

|   ۰ | SELECT STATEMENT  |             |     ۲ |   ۱۸۴ |   ۲۰۷   (۵)| ۰۰:۰۰:۰۳ |

|*  ۱ |  TABLE ACCESS FULL| OBJECTS_TAB |     ۲ |   ۱۸۴ |   ۲۰۷   (۵)| ۰۰:۰۰:۰۳ |

———————————————————————————

Predicate Information (identified by operation id):

—————————————————

   ۱ – filter(“OBJECT_NAME”=’USER_TABLES’)

SQL>

برای فعال‌سازی قابلیت ایندکس مجازی نیاز به تنظیم پارامتر _use_nosegment_indexes است.

SQL> ALTER SESSION SET “_use_nosegment_indexes” = TRUE;

Session Altered

SQL>

حال با تکرار query فوق مشاهده می‌کنیم که از ایندکس مجازی استفاده شده است.

SQL> SET AUTOTRACE TRACEONLY EXP

SQL> SELECT * FROM objects_tab WHERE object_name = ‘USER_TABLES’;

Execution Plan

———————————————————-

Plan hash value: 4006507992

———————————————————————————————————-

| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

———————————————————————————————————-

|   ۰ | SELECT STATEMENT            |                            |     ۲ |   ۱۸۴ |     ۲   (۰)| ۰۰:۰۰:۰۱ |

|   ۱ |  TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB                |     ۲ |   ۱۸۴ |     ۲   (۰)| ۰۰:۰۰:۰۱ |

|*  ۲ |   INDEX RANGE SCAN          | OBJECTS_TAB_OBJECT_NAME_VI |     ۲ |       |     ۱   (۰)| ۰۰:۰۰:۰۱ |

———————————————————————————————————

Predicate Information (identified by operation id):

—————————————————

   ۲ – access(“OBJECT_NAME”=’USER_TABLES’)

SQL>

ایندکس های مجازی در USER_INDEXES view نمایش داده نمی‌شود اما در USER_OBJECTS view قابل مشاهده است.

SQL> SET AUTOTRACE OFF

SQL> SELECT index_name FROM user_indexes;

INDEX_NAME

——————————

OBJECTS_TAB_PK

۱ row selected.

SQL> SELECT object_name FROM user_objects WHERE object_type = ‘INDEX’;

OBJECT_NAME

—————————————————————————————————-

OBJECTS_TAB_PK

OBJECTS_TAB_OBJECT_NAME_VI

۲ rows selected.

SQL>

جمع‌آوری آمار روی ایندکس های مجازی به همان روش index های معمولی است ولی همانطور که قبلاً مشاهده شد هیچ داده‌ای در USER_INDEXES view وجود ندارد.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘objects_tab_object_name_vi’);

PL/SQL procedure successfully completed.

SQL>

اوراکل از ایجاد یک ایندکس مجازی دیگر بر روی ستون یکسان ممانعت به عمل می‌آورد، ولی می‌توان یک real index بر روی این ستون ایجاد نمود.

SQL> CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT;

CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT

                                                        *

ERROR at line 1:

ORA-01408: such column list already indexed

SQL> CREATE INDEX objects_tab_object_name_i ON objects_tab(object_name);

Index created.

SQL>