Index Organized Table

■ IOT(Index Organized Table)

 

▲ Hr로 접속해서 new_employees 테이블을 있으면 drop 한 후, 다음과 같이 new_employees 라는 IOT를 생성하시오.    이 테이블에 hr.employees 테이블과 동일한 논리적 구조를 가지며,    employee_id 칼럼을 primary key로 이름은 new_employees_employee_id_pk로 지정하시오.       Create table new_employees          (employee_id NUMBER(6),           first_name VARCHAR2(20),           last_name VARCHAR2(25),           email VARCHAR2(25),           phone_number VARCHAR2(20),           hire_date DATE,           job_id VARCHAR2(10),           salary NUMBER(8,2),           commission_pct NUMBER(2,2),           manager_id NUMBER(6),           department_id NUMBER(4),           CONSTRAINT new_employees_employee_id_pk PRIMARY KEY (employee_id))       ORGANIZATION INDEX; ▲ 생성된 IOT에 대해서 user_tables, user_indexes 뷰로 조회하시오. ▲ hr.employees 테이블의 데이터를 그대로 new_employees 테이블로 INSERT 하고 COMMIT 하시오. ▲ new_employees 테이블의 last_name 열에 대해 추가 index를 생성하시오.    이때 인덱스 이름은 last_name_new_employees_idx로 지정하시오. 또한 생성된 인덱스에 대한 통계정보를 수집하시오. ▲ LAST_NAME_NEW_EMPLOYEES_IDX에 대한 생성된 통계정보를 user_indexes뷰로 조회하시오. ▲ NEW_EMPLOYEES 테이블의 JOB_ID 컬럼에 대한 BITMAP 인덱스를 생성하시오.  인덱스명은 JOB_ID_BITMAP_IDX로 하시오. 결과는? 방법은? ▲ 다음의 쿼리를 수행하시오.    SELECT table_name, iot_name, iot_type FROM user_tables;    SELECT index_name, index_type FROM user_indexes; ▲ 생성된 NEW_EMPLOYEES 테이블을 삭제하시오.

 

SQL> conn /as sysdba

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql                                                

SQL> grant plustrace to hr;

 

SQL> conn hr/hr

Connected.

 

 

SQL> select * from tab;

 

TNAME                                TABTYPE  CLUSTERID

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

APARTMENTS                         TABLE

COUNTRIES                           TABLE

DEPARTMENTS                        TABLE

DEPT                                  TABLE

DEPT_TEST                            TABLE

EMP                                   TABLE

EMPLOYEES                           TABLE

EMP_DETAILS_VIEW                    VIEW

JOBS                                  TABLE

JOB_HISTORY                         TABLE

LOCATIONS                            TABLE

PLAN_TABLE                           TABLE

REGIONS                              TABLE

 

13 rows selected.

 

 

SQL> Create table new_employees

      (employee_id NUMBER(6),

       first_name VARCHAR2(20),

       last_name VARCHAR2(25),

       email VARCHAR2(25),

       phone_number VARCHAR2(20),

       hire_date DATE,

       job_id VARCHAR2(10),

       salary NUMBER(8,2),

       commission_pct NUMBER(2,2),

       manager_id NUMBER(6),

       department_id NUMBER(4),

       CONSTRAINT new_employees_employee_id_pk PRIMARY KEY (employee_id))

       ORGANIZATION INDEX;

 

Table created.

 

SQL> select table_name, iot_name, iot_type

      from user_tables

      where table_name like 'NEW_EMPLOYEES%';

 

TABLE_NAME                     IOT_NAME                       IOT_TYPE

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

NEW_EMPLOYEES                                                 IOT

 

SQL> col table_name format a15

SQL> select index_name, index_type, table_name

       from user_indexes

      where table_name like 'NEW_EMPLOYEES%';

 

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME

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

NEW_EMPLOYEES_EMPLOYEE_ID_PK   IOT - TOP                   NEW_EMPLOYEES

 

SQL> insert into new_employees  select * from employees;

 

107 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> create index last_name_new_employees_idx on new_employees(last_name);

 

Index created.

 

SQL>exec dbms_stats.gather_index_stats('HR','LAST_NAME_NEW_EMPLOYEES_IDX');

PL/SQL procedure successfully completed.

 

SQL> col index_type format a15

SQL> select index_name, index_type, blevel, leaf_blocks

      from user_indexes

     where index_name = 'LAST_NAME_NEW_EMPLOYEES_IDX';

 

INDEX_NAME                     INDEX_TYPE          BLEVEL LEAF_BLOCKS

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

LAST_NAME_NEW_EMPLOYEES_IDX    NORMAL                   0           1

 

 

SQL> create bitmap index job_id_bitmap_idx on new_employees(job_id);

create bitmap index job_id_bitmap_idx on new_employees(job_id)

                                         *

ERROR at line 1:

ORA-28669: bitmap index can not be created on an IOT with no mapping table

 

SQL> alter table new_employees move mapping table;

 

Table altered.

 

SQL> select * from tab;

 

TNAME                                TABTYPE  CLUSTERID

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

APARTMENTS                         TABLE

COUNTRIES                           TABLE

DEPARTMENTS                        TABLE

DEPT                                  TABLE

DEPT_TEST                            TABLE

EMP                                   TABLE

EMPLOYEES                           TABLE

EMP_DETAILS_VIEW                    VIEW

JOBS                                  TABLE

JOB_HISTORY                         TABLE

LOCATIONS                            TABLE

NEW_EMPLOYEES                    TABLE

PLAN_TABLE                           TABLE

REGIONS                              TABLE

SYS_IOT_MAP_25638                   TABLE

 

15 rows selected.

 

SQL> create bitmap index job_id_bitmap_idx on new_employees(job_id);

 

Index created.

 

SQL> col table_name format a20

SQL> select table_name, iot_name, iot_type from user_tables;

 

TABLE_NAME           IOT_NAME                       IOT_TYPE

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

APARTMENTS

COUNTRIES                                                           IOT

DEPARTMENTS

DEPT

DEPT_TEST

EMP

EMPLOYEES

JOBS

JOB_HISTORY

LOCATIONS

NEW_EMPLOYEES                                                    IOT

PLAN_TABLE

REGIONS

SYS_IOT_MAP_25638    NEW_EMPLOYEES                 IOT_MAPPING

 

14 rows selected.

 

 

SQL> select index_name, index_type from user_indexes;

 

INDEX_NAME                     INDEX_TYPE

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

COUNTRY_C_ID_PK                   IOT - TOP

DEPT_ID_PK                            NORMAL

DEPT_LOCATION_IX                    NORMAL

EMP_DEPARTMENT_IX                 NORMAL

EMP_EMAIL_UK                        NORMAL

EMP_EMP_ID_PK                       NORMAL

EMP_JOB_IX                           NORMAL

EMP_MANAGER_IX                    NORMAL

EMP_NAME_IX                         NORMAL

JHIST_DEPARTMENT_IX                NORMAL

JHIST_EMPLOYEE_IX                   NORMAL

INDEX_NAME                          INDEX_TYPE

JHIST_EMP_ID_ST_DATE_PK            NORMAL

JHIST_JOB_IX                           NORMAL

JOB_ID_BITMAP_IDX                   BITMAP

JOB_ID_PK                             NORMAL

LAST_NAME_IDX                       FUNCTION-BASED

                                        NORMAL

LAST_NAME_NEW_EMPLOYEES_IDX    NORMAL

LOC_CITY_IX                           NORMAL

LOC_COUNTRY_IX                    NORMAL

LOC_ID_PK                             NORMAL

LOC_STATE_PROVINCE_IX              NORMAL

NEW_EMPLOYEES_EMPLOYEE_ID_PK   IOT - TOP

REG_ID_PK                            NORMAL

SYS_IL0000024767C00002$$               LOB

SYS_IL0000024767C00003$$               LOB

 

25 rows selected.

 

SQL> set autot on

 

SQL>  select EMPLOYEE_ID,FIRST_NAME,SALARY from new_employees where salary >10000;

 

EMPLOYEE_ID FIRST_NAME                                   SALARY

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

        100 Steven                                        24000

        101 Neena                                         17000

        102 Lex                                           17000

        108 Nancy                                         12000

        114 Den                                           11000

        145 John                                          14000

        146 Karen                                         13500

        147 Alberto                                       12000

        148 Gerald                                        11000

        149 Eleni                                         10500

        162 Clara                                         10500

        168 Lisa                                          11500

        174 Ellen                                         11000

        201 Michael                                       13000

        205 Shelley                                       12000

 

15 개의 행이 선택되었습니다.

 

경   과: 00:00:00.01

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=408 Bytes=15504)

   1    0   INDEX (FAST FULL SCAN) OF 'NEW_EMPLOYEES_EMPLOYEE_ID_PK' (UNIQUE) (Cost=8 Card=408

Bytes=15504)

 

Statistics

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

          0  recursive calls

          0  db block gets

          6  consistent gets

          0  physical reads

          0  redo size

        787  bytes sent via SQL*Net to client

        503  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         15  rows processed

 

SQL>

 

SQL> select EMPLOYEE_ID,FIRST_NAME,SALARY from new_employees where last_name='Khoo';

 

EMPLOYEE_ID FIRST_NAME                                   SALARY

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

        115 Alexander                                      3100

 

경   과: 00:00:00.01

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=82 Bytes=4264)

   1    0   INDEX (UNIQUE SCAN) OF 'NEW_EMPLOYEES_EMPLOYEE_ID_PK' (UNIQUE) (Cost=2 Card=82

Bytes=4264)

   2    1     INDEX (RANGE SCAN) OF 'LAST_NAME_NEW_EMPLOYEES_IDX' (NON-UNIQUE) (Cost=2

Card=33)

 

 

Statistics

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

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        504  bytes sent via SQL*Net to client

        503  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

SQL>  select EMPLOYEE_ID,FIRST_NAME,SALARY from new_employees where job_id = 'PU_CLERK';

 

EMPLOYEE_ID FIRST_NAME                                   SALARY

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

        115 Alexander                                      3100

        116 Shelli                                         2900

        117 Sigal                                          2800

        118 Guy                                            2600

        119 Karen                                          2500

 

경   과: 00:00:00.01

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=82 Bytes=3690)

   1    0   INDEX (UNIQUE SCAN) OF 'NEW_EMPLOYEES_EMPLOYEE_ID_PK' (UNIQUE) (Cost=3 Card=82

Bytes=3690)

   2    1     BITMAP CONVERSION (TO ROWIDS)

   3    2       BITMAP INDEX (SINGLE VALUE) OF 'JOB_ID_BITMAP_IDX'

 

 

Statistics

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

          0  recursive calls

          0  db block gets

         14  consistent gets

          0  physical reads

          0  redo size

        602  bytes sent via SQL*Net to client

        503  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          5  rows processed