Indexes with NOSEGMENT a.k.a Virtual Index – Keep Learning Keep Living

Hi,

Today, an old trainee has contacted with my on linkedin and we talk about some performance issues about a query. During the conversation I suggest to use “virtual indexes” to simulate the index so I wanted to write about virtual indexes and their benefits in this post.

if you don’t work on a large database (or a small database server) then, you probably don’t get bored while creating an index but if you do then, index creation can be a pain. Let’s say you examined a query and decided to create an index based on some columns could get increase the performance but will it? if your table is a large one then, creating an index could take serious amount of time. During that time, your server will have extra work load and if that index does not work as you assumed then all those time and work load will be for nothing! Also, you will have generated too many archivelogs. Those archivelogs could increase your recovery time in case of a failure.

So what can we do? You can use “virtual index”. Virtual index is an index without a segment which means this index has no data, does not index anything at all! So, what good come out of it? it’s creation time almost instantaneous and no work load on your server, no archivelog generation, no performance impact in any way. So, why do we create it? Because you can check your execution plan and see the cost and other metric values so that you can decide whether this index should be created or not!

This will save you from wasting too much time and give an initial idea.

Here is an example:

select count(8) from dba_objects;

  COUNT(8)
----------
     72544

select count(8) from dba_objects;

COUNT(8)

----------

72544

I have 72.544 rows in my dba_objects view. By using this view, I created a table:

create sequence seq cache 10000;

alter session enable parallel dml;
alter session enable parallel ddl;

create /*+ parallel */ table test1 as 
  select seq.nextval ID, d.*
  from dba_objects d, (select 1 from dba_objects where rownum < 16000)r;

Table TEST1 created.

Elapsed: 00:35:36.25

create sequence seq cache 10000;

alter session enable parallel dml;

alter session enable parallel ddl;

create /*+ parallel */ table test1 as

select seq.nextval ID, d.*

from dba_objects d, (select 1 from dba_objects where rownum < 16000)r;

Table TEST1 created.

Elapsed: 00:35:36.25

I created a BIG table which contains 1.1 Billion rows in it. Those rows are copies of dba_objects view and it takes 35 minutes to create it in parallel (I enabled parallel dml and ddl operations to achieve that). Table size is around 185GB.

Now, Let’s create indexes on it:

create unique index i_test1_1 on test1(id);

Index created.

Elapsed: 00:17:49.45

SQL> create index i_test1_2 on test1(object_id);

Index created.

Elapsed: 00:10:08.74

create unique index i_test1_1 on test1(id);

Index created.

Elapsed: 00:17:49.45

SQL> create index i_test1_2 on test1(object_id);

Index created.

Elapsed: 00:10:08.74

Unique index took almost 18 minutes to complete and non unique took 10 minutes (no parallel). I must say that, my laptop is a monster with 10th gen i9 (8core), 64GB ram and nvme ssds. those timings could be double or triple on a regular db server. As I said, after all this time, if indexes aren’t useful then you drop them.

Instead of that, we can use NOSEGMENT clause while creating the index (virtual index)

Drop index i_test1_1;
Drop index i_test1_2;

SQL> create unique index i_test1_1 on test1(id) NOSEGMENT;

Index created.

Elapsed: 00:00:00.00
SQL> create unique index i_test1_2 on test1(object_id) NOSEGMENT;

Index created.

Elapsed: 00:00:00.00
SQL>

Drop index i_test1_1;

Drop index i_test1_2;

SQL> create unique index i_test1_1 on test1(id) NOSEGMENT;

Index created.

Elapsed: 00:00:00.00

SQL> create unique index i_test1_2 on test1(object_id) NOSEGMENT;

Index created.

Elapsed: 00:00:00.00

SQL>

As you see, it is fast because it just created an index definition into oracle data dictionary. Literally, there is no such index. check dba_indexes:

Select * from dba_indexes where index_name in ('I_TEST1_1', 'I_TEST1_2');

no rows selected

Select * from dba_indexes where index_name in ('I_TEST1_1', 'I_TEST1_2');

no rows selected

How Oracle can use an index if it is not actually created? Answer is STATISTICS. To use virtual index and get a consistent result from it, your statistics must be up to date. Of course, result will not be certain but close enough to give us an idea. By default Oracle will not use virtual indexes in your execution plan so you must set a hidden parameter, then your explained plans will have virtual indexes:

SQL> set autotrace traceonly explain
SQL> select * from mustafa.test1 where id = 56789034;

Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |   139 |  6450K  (1)| 00:04:12 |
|*  1 |  TABLE ACCESS FULL| TEST1 |     1 |   139 |  6450K  (1)| 00:04:12 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=56789034)



SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.


SQL> select * from mustafa.test1 where id = 56789034;

Execution Plan
----------------------------------------------------------
Plan hash value: 2636153387

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |   139 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1     |     1 |   139 |     4   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | I_TEST1_1 |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=56789034)

SQL> set autotrace traceonly explain

SQL> select * from mustafa.test1 where id = 56789034;

Execution Plan

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

Plan hash value: 4122059633

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

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

| 0 | SELECT STATEMENT | | 1 | 139 | 6450K (1)| 00:04:12 |

|* 1 | TABLE ACCESS FULL| TEST1 | 1 | 139 | 6450K (1)| 00:04:12 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID"=56789034)

SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.

SQL> select * from mustafa.test1 where id = 56789034;

Execution Plan

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

Plan hash value: 2636153387

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

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

| 0 | SELECT STATEMENT | | 1 | 139 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 139 | 4 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | I_TEST1_1 | 1 | | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=56789034)

after setting hidden parameter _use_nosegment_indexes optimizer will create an execution plan with considering virtual indexes and I can see that the cost of the query has decreased to 2 from 6.450.000. This saves so much time. after checking your execution plans, if you are ok with them you can actually create the index.

EDIT: There is 2 things that I forgot to mention. after creating virtual index run DBMS_STATS.GENERATE_STATS procedure to generate some statistics of your virtual indexes.

exec dbms_stats.generate_stats(user, 'I_TEST1_1');

1	exec dbms_stats.generate_stats(user, 'I_TEST1_1');

and the second thing is, this is can be used only in enterprise edition not standard edition. Standard edition has no deferred segment creation so you cannot create a data object without data segment.

To create this virtual index as a real one, you must drop it and re-create it without nosegment clause.

Time is the most valuable thing for us. Don’t waste your time carelessly.

Wish you all healthy, beautiful days.

Comments (2)

SERHAT says:

2022-03-30 at 13:00

I have asked this question to my dear instructor. Thank you very much for explaining it perfectly on the same day.

Kind Regards.

1. Mustafa says:
  
  2022-03-30 at 13:58
  
  I thank you very much Serhat. There is always something to share and thank you for our conversation.

Comments (2)

Leave a Reply Cancel reply