07:05:15 orcl>
07:05:20 orcl>
07:05:20 orcl>
07:05:21 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Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
                                                                                                                                  Â
--------------------------------------------------------------------------------------------Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
| Id | Operation                   | Name       | Rows | Bytes | Cost (%CPU)| Time    |                                      Â
--------------------------------------------------------------------------------------------Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
|Â Â 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Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
                                                                                                                                  Â
----------------------------------------------------------------------------------------------------Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
| Id | Operation                   | Name               | Rows | Bytes | Cost (%CPU)| Time    |                              Â
----------------------------------------------------------------------------------------------------Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
|Â Â 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â€