07:05:15 orcl>

07:05:20 orcl>

07:05:21 orcl>

07:05:25 orcl> Let’s describe the employees table and review the columns and datatypes.

07:05:25 orcl>

07:05:25 orcl> desc hr.employees;

Name Null? Type

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

EMPLOYEE_ID NUMBER(6)

FIRST_NAME VARCHAR2(20)

LAST_NAME NOT NULL VARCHAR2(25)

EMAIL NOT NULL VARCHAR2(25)

PHONE_NUMBER VARCHAR2(20)

HIRE_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2(10)

SALARY NUMBER(8,2)

COMMISSION_PCT NUMBER(2,2)

MANAGER_ID NUMBER(6)

DEPARTMENT_ID NUMBER(4)

Since I am using this table for other testing, let’s build a copy to use for our virtual index testing.

07:34:55 orcl> create table hr.employees2 as select * from hr.employees;

Table created.

We’ll duplicate a few rows in the table.

07:35:15 orcl> insert into hr.employees2 select * from hr.employees;

107 rows created.

Duplicating rows to pump up the volume in our test table.

07:36:17 orcl> r

1* insert into hr.employees2 select * from hr.employees2

178048 rows created.

Activating SQL*PLUS Autotrace to produce an Explain Plan.

07:50:38 orcl> set autotrace traceonly exp stat

Building a regular B-Tree index on the EMPLOYEEES2 table. Notice that Autotrace
does not produce Explain Plans for DDL statements.

07:52:19 orcl> create index hr.emp2_emp_id on hr.employees2 (employee_id);

Index created.

Running the statement using the Autotrace option to generate the access path. Please note

that the optimizer has chosen the EMP2_EMP_ID index we just built.

07:54:52 orcl> ed

Wrote file afiedt.buf

1 select employee_id, a.department_id, b.department_name

2 from

3 hr.departments b, hr.employees2 a

4 where

5 a.department_id = b.department_id

6* and employee_id = 203

07:55:14 7 /

Execution Plan

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

Plan hash value: 1466647341

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

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

| 0 | SELECT STATEMENT | | 1 | 25 | 5 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 25 | 5 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES2 | 1 | 9 | 4 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | EMP2_EMP_ID | 1 | | 3 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |

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

Dropping the original index.

07:55:17 orcl> drop index hr.emp2_emp_id;

Index dropped.

Building our virtual index using the NOSEGMENT clause.

07:59:12 orcl> create index hr.emp2_emp_id_virtual on hr.employees2(employee_id) nosegment;

Index created.

Setting the hidden startup parameter "_use_nosegment_indexes" to TRUE so that our

session will recognize our new virtual index.

08:00:09 orcl> alter session set "_use_nosegment_indexes" = true;

Running our statement again to see if it will use our new virtual index. Check out
the access path below. The optimizer has chosen our virtual index.

1 select employee_id, a.department_id, b.department_name

2 from

3 hr.departments b, hr.employees2 a

4 where

5 a.department_id = b.department_id

6* and employee_id = 203

Execution Plan

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

Plan hash value: 2516110069

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

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

| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 25 | 3 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES2 | 1 | 9 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | EMP2_EMP_ID_VIRTUAL | 1 | | 1 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |

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

Setting the "_use_nosegment_indexes” to FALSE. Note that the optimizer did NOT
choose the virtual index.

08:01:09 orcl> alter session set "_use_nosegment_indexes" = false;

Session altered.

08:01:33 orcl> select employee_id, a.department_id, b.department_name

08:01:47 2 from

08:01:47 3 hr.departments b, hr.employees2 a

08:01:47 4 where

08:01:47 5 a.department_id = b.department_id

08:01:47 6 and employee_id = 203;

Execution Plan

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

Plan hash value: 2641883601

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

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

| 0 | SELECT STATEMENT | | 1 | 25 | 818 (3)| 00:00:10 |

| 1 | NESTED LOOPS | | 1 | 25 | 818 (3)| 00:00:10 |

|* 2 | TABLE ACCESS FULL | EMPLOYEES2 | 1 | 9 | 817 (3)| 00:00:10 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |

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

Building another virtual index using the NOSEGMENT clause. Note that Oracle returns an

error stating that the column is already indexed.

08:17:23 orcl> create index hr.emp3_emp_id_virtual on hr.employees2(employee_id) nosegment;

create index hr.emp3_emp_id_virtual on hr.employees2(employee_id) nosegment

ERROR at line 1:

ORA-01408: such column list already indexed

Reactivating our session to begin recognizing virtual indexes.

08:19:01 orcl> alter session set "_use_nosegment_indexes" = true;

Session altered.

Building a regular B-TREE index on the same column. Although Oracle didn’t allow us to create two
virtual indexes on the same column, it did allow us to create one standard index and one virtual index
on the same column.

08:19:12 orcl> create index hr.emp2_emp_id_non_virtual on hr.employees2(employee_id);
Index created.

Running the statement again. The optimizer chose the virtual index over the standard index
containing data! Interesting!

1 select employee_id, a.department_id, b.department_name

2 from

3 hr.departments b, hr.employees2 a

4 where

5 a.department_id = b.department_id

6* and employee_id = 203

Execution Plan

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

Plan hash value: 2516110069

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

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

| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 25 | 3 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES2 | 1 | 9 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | EMP2_EMP_ID_VIRTUAL | 1 | | 1 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |

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

Setting the _use_nosegment_indexes parameter to false to turn it off for our session.

08:20:00 orcl> alter session set "_use_nosegment_indexes" =false;

Session altered.

Running the statement again. The optimizer chose the standard index over the virtual index.
It looks like Oracle will use the virtual index if you have the parameter set to TRUE.

08:20:31 orcl> select employee_id, a.department_id, b.department_name

08:20:41 2 from

08:20:41 3 hr.departments b, hr.employees2 a

08:20:41 4 where

08:20:41 5 a.department_id = b.department_id

08:20:41 6 and employee_id = 203;

Execution Plan

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

Plan hash value: 4005350841

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

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

| 0 | SELECT STATEMENT | | 1 | 25 | 5 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 25 | 5 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES2 | 1 | 9 | 4 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | EMP2_EMP_ID_NON_VIRTUAL | 1 | | 3 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |

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

Rebuilding the virtual index. Notice that the error message states that

you can not alter a “fake” index.

08:21:03 orcl> alter index hr.emp2_emp_id_non_virtual rebuild;

Index altered.

08:21:39 orcl> alter index hr.emp2_emp_id_virtual rebuild;

alter index hr.emp2_emp_id_virtual rebuild

ERROR at line 1:

ORA-08114: can not alter a fake index

Executing DBMS_STATS to gather statistics on both the virtual and standard index. I have
run tests with statistics and without and it does seem to affect virtual index access paths.

08:21:55 orcl>

08:21:57 orcl>

08:21:57 orcl> exec dbms_stats.gather_index_stats('HR', 'EMP2_EMP_ID_NON_VIRTUAL');

PL/SQL procedure successfully completed.

08:23:10 orcl> exec dbms_stats.gather_index_stats('HR', 'EMP2_EMP_ID_VIRTUAL');

PL/SQL procedure successfully completed.

Looking for information on indexes built on the EMPLOYEES2 table.
Oracle returns a row for the standard index but not the virtual index.

08:20:31 orcl> select index_name, last_analyzed from dba_indexes where

2* table_name = 'EMPLOYEES2'

INDEX_NAME LAST_ANAL

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

EMP2_EMP_ID_NON_VIRTUAL 31-MAY-07

Determining f we can find the virtual index in DBA_SEGMENTS. No success.

08:26:09 orcl> select segment_name, segment_type from dba_segments where segment_name like 'EMP2%';

SEGMENT_NAME SEGMENT_TYPE

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

EMP2_EMP_ID_NON_VIRT INDEX

UAL

Looking for the the virtual index in DBA_OBJECTS. Finally, we find some sort of evidence that the

virtual index exists in the database!

08:30:21 orcl> col object_name for a30

08:30:29 orcl> r

1 select object_name, object_type, created, status, temporary

2* from dba_objects where object_name like 'EMP2%'

OBJECT_NAME OBJECT_TYPE CREATED STATUS T

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

EMP2_EMP_ID_NON_VIRTUAL INDEX 31-MAY-07 VALID N

EMP2_EMP_ID_VIRTUAL INDEX 31-MAY-07 VALID N

08:31:01 orcl> spool off