Oracle index 생성 전에 index에 대한 size 예상하는 방법.

Oracle 관련된 전문 블로거들의 RSS를 구독하고 있습니다. 

좀 흥미로운 내용이 있어 소개해 드립니다. 

Quiz: Explaining index creation

Tanel Poder's blog: Core IT for Geeks and Pros

Did you know that it’s possible to use EXPLAIN PLAN FOR CREATE INDEX ON table(col1,col2,col3) syntax for explaining what exactly would be done when an index is created?

That’s useful for example for seeing the Oracle’s estimated index size without having to actually create the index.

You can also use EXPLAIN PLAN FOR ALTER INDEX i REBUILD to see whether this operation would use a FULL TABLE SCAN or a FAST FULL INDEX SCAN (offline index rebuilds of valid indexes can use this method).

위의 내용은 읽어 보시면 아시겠지만, EXPLAIN PLAN FOR 명령을 이용해 생성할 INDEX의 size를 추측해 보는 방법을 설명하고 있습니다. 

EXPLAIN PLAN FOR는 일반적으로 수행행되는 SQL의 PLAN이나 statistic 값들을 확인하기 위해 사용되는데, 

해당 SQL이 수행되면서 읽을 양으로 INDEX size를 판단할 수 있다는 내용입니다. 

위 포스트에서 설명한 내용을 그대로 한번 따라가 보겠습니다. 

SQL> show user

USER is "SCOTT"

SQL> explain plan set statement_id = 'cr_emp_idx01' for  create index emp_idx01 on emp(empno);   

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 188079254

------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------

|   0 | CREATE INDEX STATEMENT |           |    14 |    56 |    13   (0)| 00:00:01 |

|   1 |  INDEX BUILD NON UNIQUE| EMP_IDX01 |       |       |            |          |

|   2 |   SORT CREATE INDEX    |           |    14 |    56 |            |          |

|   3 |    TABLE ACCESS FULL   | EMP       |    14 |    56 |    12   (0)| 00:00:01 |

------------------------------------------------------------------------------------

Note

-----

   - estimated index size: 65536  bytes

14 rows selected.

위의 plan 결과에 따르면 해당 index는 65536 bytes가 되겠군요.
그럼 INDEX를 한번 만들어 보겠습니다.

SQL> create index emp_idx01 on emp(empno);

Index created.

SQL> column segment_name format a30

SQL> column segment_type format a30

SQL> select segment_name,segment_type,bytes from user_segments where segment_name= 'EMP_IDX01';

SEGMENT_NAME                   SEGMENT_TYPE                        BYTES

------------------------------ ------------------------------ ----------

EMP_IDX01                      INDEX                             1048576

SQL>  

SQL> connect system/manager

Connected.

SQL> select segment_name,segment_type,tablespace_name from dba_segments where segment_name = 'EMP_IDX01'; 

SEGMENT_NAME                   SEGMENT_TYPE                   TABLESPACE

------------------------------ ------------------------------ ----------

EMP_IDX01                      INDEX                          TS_TEST01

SQL> column tablespace_name format a10

SQL> select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT from dba_tablespaces where tablespace_name = 'TS_TEST01';

TABLESPACE INITIAL_EXTENT NEXT_EXTENT

---------- -------------- -----------

TS_TEST01         1048576     1048576

EMP_IDX01이 저장될 tablespace의 initial extent 값이 1048576이라
INDEX size 65536 bytes는 한개의 extent에 모두 저장되었군요.

PLAN에서 보여준 statistic 값은 Block scan 결과만 보여주는 것이니 PCTFREE 등의 값의 영향으로 약간의 가감은 필요할 것 같습니다만.
대충 얼마정도의 공간이 필요할 것이다라는 것에 대한 힌트는 될 것 같습니다.

* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!