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

روش اول: استفاده از execution plan

SQL> explain plan for create index ind1 on mytbl(SOURCE);

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 3859176188

——————————————————————————–

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

——————————————————————————–

|   0 | CREATE INDEX STATEMENT |       |   287K|    24M|  1954   (1)| 00:00:01 |

|   1 |  INDEX BUILD NON UNIQUE| IND1                   |       |       |            |          |

|   2 |   SORT CREATE INDEX    |                          |   287K |    24M|            |          |

|   3 |    TABLE ACCESS FULL   | MYTBL |   287K|    24M|  1259   (1)| 00:00:01 |

——————————————————————————–

PLAN_TABLE_OUTPUT

——————————————————————————–

Note

—–

   – estimated index size: 33M bytes

14 rows selected.

***ساختار و حجم جدول mytbl را در قسمت زیر مشاهده می کنید:

SQL> desc mytbl;

Name   Type           Nullable Default Comments

—— ————– ——– ——- ——–

OBJ#   NUMBER                                  

LINE   NUMBER                                  

SOURCE VARCHAR2(4000) Y 

SQL> select bytes/1024/1024 SIZE_MB from dba_segments where segment_name=’MYTBL’;

   SIZE_MB

———-

        37

توجه! قبل از استفاده از این روش، باید از بروز بودن آمار جدول mytbl مطئمن باشیم.

SQL> exec dbms_Stats.gather_Table_Stats(‘USEF’,’MYTBL’,Method_Opt=>’For Table For All Indexes For All Indexed Columns’,Cascade=>True);

PL/SQL procedure successfully completed

روش دوم:استفاده از پروسیجر create_index_cost

SQL> variable used_bytes number;

SQL> variable alloc_bytes number;

SQL> exec dbms_space.create_index_cost( ‘create index ind1 on mytbl(SOURCE)’, :used_bytes, :alloc_bytes );

PL/SQL procedure successfully completed.

SQL> print used_bytes;

USED_BYTES

———-

  25627728

SQL> print alloc_bytes

ALLOC_BYTES

———–

   33554432

همانطور که می بینید، تخمین سایز ایندکس در روش دوم هم برابر با 33 مگابایت می باشد.

در پایان ایندکس را ایجاد می کنیم تا تفاوت تخمین و سایز واقعی ایندکس را مقایسه کنیم:

SQL> create index ind1 on mytbl(SOURCE);

Index created

SQL> select bytes/1024/1024 SIZE_MB from dba_segments where segment_name=’IND1′;

   SIZE_MB

———-

      36

فضای واقعی ایندکس ind1، برابر با 36 مگابایت می باشد که با تخمین صورت گرفته، سه مگابایت اختلاف دارد.

 

تخمین سایز ایندکس و فشرده سازی

در مثال زیر، کیفیت تخمین سایز ایندکس را در زمانی که قرار است ایندکس به صورت compress ایجاد شود، خواهیم دید:

SQL> explain plan for create index ind1 on mytbl(SOURCE) COMPRESS ADVANCED HIGH;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 3859176188

——————————————————————————–

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

——————————————————————————–

|   0 | CREATE INDEX STATEMENT |       |   287K|    24M|  1954   (1)| 00:00:01 |

|   1 |  INDEX BUILD NON UNIQUE| IND1  |       |       |            |          |

|   2 |   SORT CREATE INDEX    |       |   287K|    24M|            |          |

|   3 |    TABLE ACCESS FULL   | MYTBL |   287K|    24M|  1259   (1)| 00:00:01 |

——————————————————————————–

PLAN_TABLE_OUTPUT

——————————————————————————–

Note

—–

   – estimated index size: 33M bytes

14 rows selected.

SQL> create index ind1 on mytbl(SOURCE) COMPRESS ADVANCED HIGH;

Index created.

SQL> select bytes/1024/1024 SIZE_MB from dba_segments where segment_name=’IND1′;

   SIZE_MB

———-

        29

همانطور که می بینید، تغییری در سایز تخمینی ایندکس ایجاد نشده است اما سایز واقعی آن، کاهش یافته است!

 

تخمین سایز ایندکس و مقادیر null

سوال: اگر درصد قابل توجهی از مقادیر موجود در ستون source برابر با null باشند، چه تاثیری در کیفیت این تخمین ایجاد خواهد کرد؟ آیا تخمین با همان درصد خطای قبلی اتفاق خواهد افتاد؟ پاسخ را در ادامه می بینید.

SQL> drop index IND1;

Index dropped.

SQL>  update mytbl set source =null;

287952 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_Stats.gather_Table_Stats(user,’MYTBL’,Method_Opt=>’For Table For All Indexes For All Indexed Columns’,Cascade=>True);

PL/SQL procedure successfully completed.

SQL> explain plan for create index ind1 on mytbl(SOURCE);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 3859176188

——————————————————————————–

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

——————————————————————————–

|   0 | CREATE INDEX STATEMENT |       |   287K|  2530K|  1405   (1)| 00:00:01 |

|   1 |  INDEX BUILD NON UNIQUE| IND1  |       |       |            |          |

|   2 |   SORT CREATE INDEX    |       |   287K|  2530K|            |          |

|   3 |    TABLE ACCESS FULL   | MYTBL |   287K|  2530K|  1259   (1)| 00:00:01 |

——————————————————————————–

PLAN_TABLE_OUTPUT

——————————————————————————–

Note

—–

   – estimated index size: 7340K bytes

14 rows selected.

SQL> create index ind1 on mytbl(SOURCE);

Index created.

SQL> select bytes from dba_segments where segment_name=’IND1′;

     BYTES

———-

     65536

همانطور که مشاهده شد، در این حالت، سایز تخمینی برابر هفت مگابایت بوده در صورتی که اندازه واقعی ایندکس برابر با 65 کیلو بایت می باشد!!!