Space Saving Features in Oracle

Back to: Oracle DBA Tutorials

Space Saving Features in Oracle

In this article, we will try to understand the Space-Saving Features using unusable Indexes in Oracle with Examples. Please read our previous article discussing Deferred Segment Creation in Oracle. In the previous article, we understood how we can benefit from the deferred segment.

Step 1: Check the database name and user name.

Command: show con_name

Let’s check the parameter DEFERRED_SEGMENT_CREATION

This indicates that if we create any table or index, the segment will not be created automatically. When we insert any data, the segment will be allocated.

Step 2: Create a table

Let’s create a table with three columns and the primary key.

create table employees
( emp_id number,
  fname varchar2(100),
  lname varchar2(100),
 constraint employees_pk primary key (emp_id)
 );

So, we are creating the columns emp_id, fname, and lname. We are creating a primary key. In Oracle, if we create a primary key, then a unique index will be created for the primary key. Let’s create the table.

The table is now created. Let’s look at the table details and check whether the indexes have been created.

Statement: select * from user_tables where table_name=’EMPLOYEES’;

So, the table is created, and we can see the default tablespace is USERS and extent details. Here, there is a parameter PCT_FREE, which indicates the percentage of space free in the blocks. So, we have 10% of free space from the size of blocks. Now, let’s look at the indexes.

Statement: SELECT * FROM USER_INDEXES

From the above output, we can see that the index was created for the table employees. Oracle created the index because of the primary key. Oracle will use the index with the same name as the primary key constraint. We use indexes to speed up the query and behave as a pointer. Now, check the segment details for the table and index.

Statement: select * from user_segments where segment_name IN ( ‘EMPLOYEES’,’EMPLOYEES_PK’ );

We can see there are no segments allocated for the index and table because no data has been inserted inside the table. Now, let’s try to insert a record.

insert into employees values (1,'khaled','alkhudari');
commit;

So, one record is inserted into the table. Let’s check the table details once.

Statement: select * from employees;

As the records are inserted, let’s look at the segments and see if there is any change.

Statement: select * from user_segments where segment_name IN ( ‘EMPLOYEES’,’EMPLOYEES_PK’ );

We can see one segment is created for the table EMPLOYEES, with the segment type table, and the other segment is created for EMPLOYEES_PK, with the segment type index.

Step 3: Create an Index on the column fname.

Let’s create an index on the column fname

Statement: create index fname_index on EMPLOYEES(fname) ;

The index is created for the column fname. So, there must be a new segment named FNAME_INDEX. Let’s look at the segment details.

Statement: select * from user_segments where segment_name IN ( ‘EMPLOYEES’,’EMPLOYEES_PK’ ,’FNAME_INDEX’);

So, the segment is created for the index FNAME_INDEX. Let’s try to create an index in the unusable format. The phrase UNUSABLE indicates the index is created as a structure, but Oracle will not allocate any storage for this index. The index will be created, but it will not be assigned to any segment.

Statement: create index lname_index on EMPLOYEES(lname) UNUSABLE ;

The index is created. Now, let’s check the segment details for the newly created index.

select * from user_segments
where segment_name IN ('EMPLOYEES','EMPLOYEES_PK' ,'FNAME_INDEX','LNAME_INDEX');

The index is created, but the segment is not assigned to it. Now, if we need to use the index, there is a phrase called rebuild index.

Statement: alter index LNAME_INDEX REBUILD;

This rebuilding index will make it online and usable.

The index is now altered. As soon as it is usable, Oracle will allocate segments to it. Let’s check the segment details once.

select * from user_segments
where segment_name IN ('EMPLOYEES','EMPLOYEES_PK' ,'FNAME_INDEX','LNAME_INDEX');

We can see the segment was allocated for the index LNAME_INDEX. The segments were also allocated for the employees table, primary key EMPLOYEES_PK, and the indexes FNAME_INDEX and LNAME_INDEX. We can make the index unusable to save space. Sometimes, the DBA makes the indexes unusable to maintain the databases.

In the next article, I will discuss Basic Compression in Oracle. In this article, I explain Space-Saving Features in Oracle with Examples. I hope you enjoy this article on Space-Saving Features in Oracle.