Sometimes creation of an index can take a lot of time. It’s especially true for warehouses. In Oracle there is nice old trick to create definition of index in dictionary without allocating physical space. It can help you to quickly determine what columns should be indexed.

Virtual index – it’s dictionary definition of index which is not allocating physical space .

Let’s create test data

create table test_tbl
(id number);

begin
  for i in 1..10000
  loop
    insert into test_tbl values(i);
  end loop;
  commit;
end;
/

exec dbms_stats.gather_table_stats(user, 'TEST_TBL');

so our table has got 10k rows in it and following query is not optimal

explain plan for
select * from test_tbl
where id=5;

select * from table(dbms_xplan.display(format=>'BASIC'));

Plan hash value: 602094504

----------------------------------------------
| Id  | Operation                 | Name     |
----------------------------------------------
|   0 | SELECT STATEMENT          |          |
|   1 |  TABLE ACCESS STORAGE FULL| TEST_TBL |
----------------------------------------------

It makes sense to add index on column id. Let’s test it with virtual index first.

1. create virtual index on database – the index won’t consume storage space

create index test_tbl_idx on test_tbl(id) nosegment;

select object_name, object_type 
from user_objects 
where object_name='TEST_TBL_IDX';

OBJECT_NAME         OBJECT_TYPE 
------------------- ------------
TEST_TBL_IDX        INDEX

select * 
from user_segments 
where segment_name='TEST_TBL_IDX';

no rows selected

2. change session settings to consider virtual index in explain plan

alter session set "_use_nosegment_indexes" = true;

3. check explain plan

explain plan for
select * from test_tbl
where id=5;

select * from table(dbms_xplan.display(format=>'BASIC'));

Plan hash value: 2543399553

-----------------------------------------
| Id  | Operation        | Name         |
-----------------------------------------
|   0 | SELECT STATEMENT |              |
|   1 |  INDEX RANGE SCAN| TEST_TBL_IDX |
-----------------------------------------

4. If you are sure all is fine you can create real index

drop index test_tbl_idx;

create index test_tbl_idx on test_tbl(id);

Have a fun 🙂

Tomasz