92 - [Oracle 19C] Space-Saving Features (UNUSABLE index)

 

Space-Saving Features (UNUSABLE index)

show user;
show con_name;
/*
USER is "PDB10_ADMIN"
CON_NAME 
------------------------------
PDB10
*/

 

 

 

show parameter DEFERRED_SEGMENT_CREATION
/*
NAME                      TYPE    VALUE 
------------------------- ------- ----- 
deferred_segment_creation boolean TRUE  
*/


create table employees
( emp_id number, fname varchar2(100), lname varchar2(100),
 constraint employees_pk primary key (emp_id)
 );
/*
Table EMPLOYEES created.
*/


# PCTFREE: 사용 가능한 공간에서 데이터 행의 갱신을 대비하여 확보해 놓는 블록 값(%) 
# ex) PCTFREE 10 = 데이터 블록의 10%를 사용 가능한 공간으로 유지하여 각 블록에 있는 행을 갱신한다.

select table_name, tablespace_name, status, pct_free from user_tables
where table_name='EMPLOYEES';
/*
TABLE_NAME      TABLESPACE_NAME STATUS     PCT_FREE
--------------- --------------- -------- ----------
EMPLOYEES       USERS           VALID            10
*/

 

 

 

SELECT table_owner, index_name, index_type, table_name, uniqueness, constraint_index FROM USER_INDEXES;
/*
TABLE_OWNER     INDEX_NAME      INDEX_TYPE   TABLE_NAME      UNIQUENES CON
--------------- --------------- ------------ --------------- --------- ---
PDB10_ADMIN     DEPT_INX        NORMAL       DEPT            UNIQUE    NO 
PDB10_ADMIN     EMPLOYEES_PK    NORMAL       EMPLOYEES       UNIQUE    YES
*/


# 데이터가 없으므로 해당 테이블 및 인덱스에 대한 segment 정보는 아직 생성이 되지 않은 상태임

SELECT segment_name, bytes, blocks, extents, tablespace_name FROM USER_SEGMENTS
where segment_name IN ( 'EMPLOYEES','EMPLOYEES_PK' );
/*
no rows selected
*/


insert into employees values (1,'GILDONG','HONG');
commit;
/*
1 row inserted.
Commit complete.
*/


select * from employees
/*
    EMP_ID FNAME           LNAME          
---------- --------------- ---------------
         1 GILDONG         HONG 
*/


# 데이터가 삽입된 후, 테이블 및 인덱스에 대한 segment 정보가 생성된것을 확인한다.

SELECT segment_name, segment_type, bytes, blocks, extents, tablespace_name FROM USER_SEGMENTS
where segment_name IN ( 'EMPLOYEES','EMPLOYEES_PK' );
/*
SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS    EXTENTS TABLESPACE_NAME
--------------- ------------------ ---------- ---------- ---------- ---------------
EMPLOYEES       TABLE                   65536          8          1 USERS          
EMPLOYEES_PK    INDEX                   65536          8          1 USERS 
*/

 

 

 

create index fname_index on EMPLOYEES(fname) ;
/*
Index FNAME_INDEX created.
*/


# 새로 생성된 fname_index 인덱스에 대한 segment 정보가 추가된다.

SELECT segment_name, segment_type, bytes, blocks, extents, tablespace_name FROM USER_SEGMENTS
where segment_name IN ( 'EMPLOYEES','EMPLOYEES_PK' ,'FNAME_INDEX');
/*
SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS    EXTENTS TABLESPACE_NAME
--------------- ------------------ ---------- ---------- ---------- ---------------
EMPLOYEES       TABLE                   65536          8          1 USERS          
EMPLOYEES_PK    INDEX                   65536          8          1 USERS          
FNAME_INDEX     INDEX                   65536          8          1 USERS 
*/



#This means that I will create index, but this index only will be in a structure 
# and it will be exist in the dictionary.
# But.
# Oracle will not create a segment for it.

create index lname_index on EMPLOYEES(lname) UNUSABLE ;
/*
Index LNAME_INDEX created.
*/


# LNAME_INDEX 인덱스의 segment 가 조회되지 않는 이유는 인덱스 생성시 "UNUSABLE" 옵션을 선택했기 때문임.

SELECT segment_name, segment_type, bytes, blocks, extents, tablespace_name FROM USER_SEGMENTS
where segment_name IN ( 'EMPLOYEES','EMPLOYEES_PK' ,'FNAME_INDEX','LNAME_INDEX');
/*
SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS    EXTENTS TABLESPACE_NAME
--------------- ------------------ ---------- ---------- ---------- ---------------
EMPLOYEES       TABLE                   65536          8          1 USERS          
EMPLOYEES_PK    INDEX                   65536          8          1 USERS          
FNAME_INDEX     INDEX                   65536          8          1 USERS 
*/


alter index LNAME_INDEX REBUILD
/*
Index LNAME_INDEX altered.
*/


# 인덱스를 REBUILD 하면 segment 정보가 생성된다.

SELECT segment_name, segment_type, bytes, blocks, extents, tablespace_name FROM USER_SEGMENTS
where segment_name IN ( 'EMPLOYEES','EMPLOYEES_PK' ,'FNAME_INDEX','LNAME_INDEX');
/*
SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS    EXTENTS TABLESPACE_NAME
--------------- ------------------ ---------- ---------- ---------- ---------------
EMPLOYEES       TABLE                   65536          8          1 USERS          
EMPLOYEES_PK    INDEX                   65536          8          1 USERS          
FNAME_INDEX     INDEX                   65536          8          1 USERS          
LNAME_INDEX     INDEX                   65536          8          1 USERS 
*/


/*
Actually we have many cases that we create index and usable and this is for example in development
and sometimes the database administrator make some indexes unusable to make some maintenance.
*/


SELECT table_owner, index_name, index_type, table_name, uniqueness, constraint_index FROM USER_INDEXES;
/*
TABLE_OWNER     INDEX_NAME      INDEX_TYPE   TABLE_NAME      UNIQUENES CONSTRAINT
--------------- --------------- ------------ --------------- --------- ----------
PDB10_ADMIN     DEPT_INX        NORMAL       DEPT            UNIQUE    NO        
PDB10_ADMIN     EMPLOYEES_PK    NORMAL       EMPLOYEES       UNIQUE    YES       
PDB10_ADMIN     FNAME_INDEX     NORMAL       EMPLOYEES       NONUNIQUE NO        
PDB10_ADMIN     LNAME_INDEX     NORMAL       EMPLOYEES       NONUNIQUE NO 
*/