Sparse Index, Function-Based Index and Virtual Column

View All Scripts Login to Run Script

Script Name Sparse Index, Function-Based Index and Virtual Column
Description This Demo usually uses to learn and comprehend 'Sparse Index', 'Function-Based Index' and 'Virtual Column', which is coming from the newest blog note (https://blog.go-faster.co.uk/2020/01/sparse-indexing.html) of David Kurtz. Perhaps that SQL code is running on his TEST environment, but I slightly adjust and make them be able to perfectly run on Live SQL.
Area SQL General / SQL Query
Contributor Quanwen Zhao
Created Thursday January 09, 2020

Statement 1
Usually before we create a table, firstly try to drop it if existed.
The Start Section of 'Sparse Index'
```
DROP TABLE t
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

CREATE TABLE t 
( key    NUMBER NOT NULL 
, status VARCHAR2(1) 
, other  VARCHAR2(1000) 
, CONSTRAINT t_pk PRIMARY KEY(key) 
)

Table created.

Statement 3
Notice: this SQL statement is not completely same as David Kurtz's, (Due to the lines number limit of Live SQL, I just insert '1e5' rows of data; by the way I also insert the value of column 'other'). The following is his original SQL. INSERT /*+APPEND*/ INTO t SELECT rownum , CASE WHEN rownum<=1e6-42 THEN NULL /*common status*/ WHEN rownum<=1e6-10 THEN 'A' ELSE 'R' END CASE FROM dual CONNECT BY level <= 1e6; If you run his SQL you'll shown this error "ORA-00947: Not enough values", which misses the value of column 'other', because table 't' has three columns 'key', 'status' and 'other'.
Using a hint 'Append' to quickly insert some data to table 't'
```
INSERT /*+ APPEND */ INTO t  
SELECT rownum  
, CASE WHEN rownum <= 1e5-42 THEN NULL /*common status*/  
       WHEN rownum <= 1e5-10 THEN 'A'  
       ELSE 'R'  
  END CASE  
, DBMS_RANDOM.string ('p', 6)  
FROM dual  
CONNECT BY level <= 1e5 
```
```
100000 row(s) inserted.
```
Statement 4
Creating a normal index on column 'status' of table 't'.
```
CREATE INDEX t_status ON t (status)
```
```
Index created.
```

Statement 5

Manually collecting the statistics information of table 't'.

EXEC SYS.DBMS_STATS.gather_table_stats(ownname => null, tabname => 'T', method_opt => 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 254 status')

Statement processed.

Statement 6
Querying the data distribution of table 't' by aggregate function 'COUNT()' and sql clause 'GROUP BY'. As you can see there has total '99958' number of nullable value on column 'status'.
```
SELECT status, COUNT(*)  
FROM t  
GROUP BY status 
```
```
STATUS COUNT(*)
A 32
R 10
 - 99958

3 rows selected.
```

STATUS	COUNT(*)
A	32
R	10
-	99958

Statement 7

It's no strange that there has 42 row of data on index 't_status', because B-Tree (normal) index itself doesn't save the 'NULL' value.

SELECT index_name, num_rows, leaf_blocks FROM user_indexes WHERE table_name = 'T'


INDEX_NAME NUM_ROWS LEAF_BLOCKS
T_PK 100000 187
T_STATUS 42 1

2 rows selected.

Statement 8
Creating a composite index 't_status2' on two columns both 'status' and 'other' of table 't'.
```
CREATE INDEX t_status2 ON t (status, other)
```
```
Index created.
```
Statement 9
Querying view 'user_indexes' again, this time you have observed that the index 't_status2' has contained '1e5' row of data, which indicates that composite index is able to index the nullable value in a column.
```
SELECT index_name, num_rows, leaf_blocks FROM user_indexes WHERE table_name = 'T' ORDER BY 1
```
```
INDEX_NAME NUM_ROWS LEAF_BLOCKS
T_PK 100000 187
T_STATUS 42 1
T_STATUS2 100000 266

3 rows selected.
```

INDEX_NAME	NUM_ROWS	LEAF_BLOCKS
T_PK	100000	187
T_STATUS	42	1

INDEX_NAME	NUM_ROWS	LEAF_BLOCKS
T_PK	100000	187
T_STATUS	42	1
T_STATUS2	100000	266

Statement 10

SELECT status, COUNT(*) 
FROM   t  
WHERE  status != 'A' 
GROUP BY status


STATUS COUNT(*)
R 10

STATUS	COUNT(*)
R	10

Statement 11

The End Section of 'Sparse Index'

SELECT status, COUNT(*)  
FROM   t   
WHERE  status != 'A' OR status IS NULL  
GROUP BY status


STATUS COUNT(*)
R 10
 - 99958

2 rows selected.

Statement 12
The Start Section of 'Function-Based Index'
```
DROP TABLE t1
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

STATUS	COUNT(*)
R	10
-	99958

Statement 13

CREATE TABLE t1  
( key NUMBER NOT NULL 
, status VARCHAR2(1) NOT NULL 
, other  VARCHAR2(1000)  
, CONSTRAINT t1_pk PRIMARY KEY(key) 
)

Table created.

Statement 14
When inserting some data into table 't1' all columns will have been inserted the NOT nullable value, at this moment Live SQL will show an error of 'tablespace size exceeded' if table 't' still has been inserted '1e5' rows, hence I just insert the data of '1e4' lines on table 't1' this time.
```
INSERT /*+ APPEND */ INTO t1  
SELECT rownum  
, CASE WHEN rownum <= 1e4-42 THEN 'C'   
       WHEN rownum <= 1e4-10 THEN 'A'   
       ELSE 'R' END CASE  
, DBMS_RANDOM.string ('p', 6)  
FROM dual  
CONNECT BY level <= 1e4 
```
```
10000 row(s) inserted.
```

Statement 15

Manually collecting the statistics information of table 't1'.

EXEC SYS.DBMS_STATS.gather_table_stats(ownname => null, tabname => 'T1', method_opt=>'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 254 status')

Statement processed.

Statement 16
Querying the data distribution of table 't1' by aggregate function 'COUNT()' and sql clause 'GROUP BY'. As you can see there has total '9958' number of *NOT* nullable value on column 'status'.
```
SELECT status, COUNT(*)  
FROM   t1   
GROUP BY status 
```
```
STATUS COUNT(*)
C 9958
A 32
R 10

3 rows selected.
```
Statement 17
Creating a normal index on column 'status' of table 't1'.
```
CREATE INDEX t1_status ON t1 (status)
```
```
Index created.
```
Statement 18
Creating a function-based index 't1_status_fn' on column 'status' of table 't1' (if whose value is 'C' FBI will replace with 'NULL').
```
CREATE INDEX t1_status_fn ON t1 (DECODE(status,'C',NULL,status))
```
```
Index created.
```

STATUS	COUNT(*)
C	9958
A	32
R	10

Statement 19

Querying view 'user_indexes' again, this time you have observed that the index 't1_status_fn' has just contained '42' row of data, which also indicates the function-based index is not able to index the nullable value in a column.

SELECT index_name, index_type, num_rows, leaf_blocks   
from user_indexes WHERE table_name = 'T1' ORDER BY 1


INDEX_NAME INDEX_TYPE NUM_ROWS LEAF_BLOCKS
T1_PK NORMAL 10000 18
T1_STATUS NORMAL 10000 19
T1_STATUS_FN FUNCTION-BASED NORMAL 42 1

3 rows selected.

INDEX_NAME	INDEX_TYPE	NUM_ROWS	LEAF_BLOCKS
T1_PK	NORMAL	10000	18
T1_STATUS	NORMAL	10000	19
T1_STATUS_FN	FUNCTION-BASED NORMAL	42	1

Statement 20

SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE status='C'


COUNT(OTHER)
9958

COUNT(OTHER)
9958

Statement 21

Checking the row source execution plan of previous SQL statement.

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  7q73d4bnfytqp, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE 
status='C'
 
Plan hash value: 3724264953
 
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      27 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      27 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   9958 |   9958 |00:00:00.01 |      27 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("STATUS"='C')
 

20 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 7q73d4bnfytqp, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(other) FROM t1 WHERE
status='C'

Plan hash value: 3724264953

-------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
-------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 27 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 27 \|
\|* 2 \| TABLE ACCESS FULL\| T1 \| 1 \| 9958 \| 9958 \|00:00:00.01 \| 27 \|
-------------------------------------------------------------------------------------

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

2 - filter("STATUS"='C')

Statement 22

SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE status='R'


COUNT(OTHER)
10

COUNT(OTHER)
10

Statement 23

Checking the row source execution plan of previous SQL statement. As you can see the access path of which is 'INDEX RANGE SCAN', and it also means that prior query uses the index 'T1_STATUS'.

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  79yuv5qxzvbg6, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE 
status='R'
 
Plan hash value: 2131837856
 
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |      1 |        |      1 |00:00:00.01 |       3 |      1 |
|   1 |  SORT AGGREGATE                      |           |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1        |      1 |     10 |     10 |00:00:00.01 |       3 |      1 |
|*  3 |    INDEX RANGE SCAN                  | T1_STATUS |      1 |     10 |     10 |00:00:00.01 |       2 |      1 |
---------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("STATUS"='R')
 

21 rows selected.

Statement 24
At this very moment I set the index 'T1_STATUS' invisible.
```
ALTER INDEX t1_status INVISIBLE
```
```
Statement processed.
```

PLAN_TABLE_OUTPUT
SQL_ID 79yuv5qxzvbg6, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(other) FROM t1 WHERE
status='R'

Plan hash value: 2131837856

---------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| Reads \|
---------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 3 \| 1 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 3 \| 1 \|
\| 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| T1 \| 1 \| 10 \| 10 \|00:00:00.01 \| 3 \| 1 \|
\|* 3 \| INDEX RANGE SCAN \| T1_STATUS \| 1 \| 10 \| 10 \|00:00:00.01 \| 2 \| 1 \|
---------------------------------------------------------------------------------------------------------------------

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

3 - access("STATUS"='R')

Statement 25

SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE status='R'


COUNT(OTHER)
10

COUNT(OTHER)
10

Statement 26

Checking the row source execution plan of previous SQL statement again, as you can see the access path of prior SQL query can only be 'TABLE ACCESS FULL', because the index 'T1_STATUS' on table 't1' has been invisible.

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  79yuv5qxzvbg6, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE 
status='R'
 
Plan hash value: 3724264953
 
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      27 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      27 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |     10 |     10 |00:00:00.01 |      27 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("STATUS"='R')
 

20 rows selected.

Statement 27
Reseting the index 'T1_STATUS' visible now.
```
ALTER INDEX t1_status VISIBLE
```
```
Statement processed.
```
Statement 28
The SQL query sets the WHERE clause like this, "DECODE(status,'C',null,status) = 'R'", in order to make the access path of its real execution become 'INDEX RANGE SCAN'.
```
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE DECODE(status,'C',null,status) = 'R'
```
```
COUNT(OTHER)
10
```

PLAN_TABLE_OUTPUT
SQL_ID 79yuv5qxzvbg6, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(other) FROM t1 WHERE
status='R'

Plan hash value: 3724264953

-------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
-------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 27 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 27 \|
\|* 2 \| TABLE ACCESS FULL\| T1 \| 1 \| 10 \| 10 \|00:00:00.01 \| 27 \|
-------------------------------------------------------------------------------------

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

2 - filter("STATUS"='R')

COUNT(OTHER)
10

Statement 29

Checking the row source execution plan of previous SQL statement, as you can see which really uses the *Function-Based Index* 'T1_STATUS_FN'.

The End Section of 'Function-Based Index'

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  8axft6m4xxmxg, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE 
DECODE(status,'C',null,status) = 'R'
 
Plan hash value: 847448750
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE                      |              |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1           |      1 |    100 |     10 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN                  | T1_STATUS_FN |      1 |     40 |     10 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T1"."SYS_NC00004$"='R')
 

21 rows selected.

Statement 30
Checking the extended statistics of table 't1'. In fact column 'EXTENSION_NAME' on view 'user_stat_extensions' is a virtual column.
The Start Section of 'Virtual Column'
```
SELECT * FROM user_stat_extensions WHERE table_name = 'T1'
```
```
TABLE_NAME EXTENSION_NAME EXTENSION CREATOR DROPPABLE
T1 SYS_NC00004$ (DECODE("STATUS",'C',NULL,"STATUS")) SYSTEM NO
```

PLAN_TABLE_OUTPUT
SQL_ID 8axft6m4xxmxg, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(other) FROM t1 WHERE
DECODE(status,'C',null,status) = 'R'

Plan hash value: 847448750

---------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
---------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 2 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 2 \|
\| 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| T1 \| 1 \| 100 \| 10 \|00:00:00.01 \| 2 \|
\|* 3 \| INDEX RANGE SCAN \| T1_STATUS_FN \| 1 \| 40 \| 10 \|00:00:00.01 \| 1 \|
---------------------------------------------------------------------------------------------------------------

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

3 - access("T1"."SYS_NC00004$"='R')

TABLE_NAME	EXTENSION_NAME	EXTENSION	CREATOR	DROPPABLE
T1	SYS_NC00004$	(DECODE("STATUS",'C',NULL,"STATUS"))	SYSTEM	NO

Statement 31

SELECT SYS_NC00004$, COUNT(*) FROM t1 group by SYS_NC00004$


SYS_NC00004$ COUNT(*)
A 32
R 10
 - 9958

3 rows selected.

Statement 32
Because 'DECODE(status, 'C', null, status)' has been set in the Function-Based Index 'T1_STATUS_FN', you, certainly, create the virtual column 'virtual_status' with 'DECODE(status, 'C', null, status)' and Oracle will show this error: 'ORA-54015: Duplicate column expression was specified'.
```
ALTER TABLE t1 ADD virtual_status VARCHAR2(1) INVISIBLE  
GENERATED ALWAYS AS (DECODE(status, 'C', null, status))
```
```
ORA-54015: Duplicate column expression was specified 
```
More Details: https://docs.oracle.com/error-help/db/ora-54015
Statement 33
Manually dropping the previous FBI 'T1_STATUS_FN'.
```
DROP INDEX t1_status_fn
```
```
Index dropped.
```

SYS_NC00004$	COUNT(*)
A	32
R	10
-	9958

Statement 34

Now it has successfully created the virtual column on table 't1'.

ALTER TABLE t1 ADD virtual_status VARCHAR2(1) INVISIBLE  
GENERATED ALWAYS AS (DECODE(status, 'C', null, status))

Table altered.

Statement 35
Creating the index 't1_status_virtual' on virtual column 'virtual_status' of table 't1'.
```
CREATE INDEX t1_status_virtual ON t1 (virtual_status)
```
```
Index created.
```

Statement 36

Using the view 'user_indexes' to check the index information of table 't1', as you can see the index previously created is also a Function-Based Index.

SELECT index_name, index_type, num_rows, leaf_blocks FROM user_indexes WHERE table_name = 'T1' ORDER BY 1


INDEX_NAME INDEX_TYPE NUM_ROWS LEAF_BLOCKS
T1_PK NORMAL 10000 18
T1_STATUS NORMAL 10000 19
T1_STATUS_VIRTUAL FUNCTION-BASED NORMAL 42 1

3 rows selected.

INDEX_NAME	INDEX_TYPE	NUM_ROWS	LEAF_BLOCKS
T1_PK	NORMAL	10000	18
T1_STATUS	NORMAL	10000	19
T1_STATUS_VIRTUAL	FUNCTION-BASED NORMAL	42	1

Statement 37

Directly using the 'virtual column name' and related value to filter as a query condition.

SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE virtual_status = 'R'


COUNT(OTHER)
10

COUNT(OTHER)
10

Statement 38

Checking the row source execution plan of previous SQL statement, as you can see which uses the index 'T1_STATUS_VIRTUAL' created based on the virtual column.

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  759hpfwkrv3z3, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE 
virtual_status = 'R'
 
Plan hash value: 2882731673
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE                      |                   |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1                |      1 |    100 |     10 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN                  | T1_STATUS_VIRTUAL |      1 |     40 |     10 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("VIRTUAL_STATUS"='R')
 

21 rows selected.

Statement 39
Directly using the function expression 'DECODE(status,'C',null,status) = 'R'' to filter as a query condition.
```
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE DECODE(status,'C',null,status) = 'R'
```
```
COUNT(OTHER)
10
```

PLAN_TABLE_OUTPUT
SQL_ID 759hpfwkrv3z3, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(other) FROM t1 WHERE
virtual_status = 'R'

Plan hash value: 2882731673

--------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
--------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 2 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 2 \|
\| 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| T1 \| 1 \| 100 \| 10 \|00:00:00.01 \| 2 \|
\|* 3 \| INDEX RANGE SCAN \| T1_STATUS_VIRTUAL \| 1 \| 40 \| 10 \|00:00:00.01 \| 1 \|
--------------------------------------------------------------------------------------------------------------------

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

3 - access("VIRTUAL_STATUS"='R')

COUNT(OTHER)
10

Statement 40

Checking the row source execution plan of previous SQL statement, as you can also see which uses the index 'T1_STATUS_VIRTUAL' created based on the virtual column.

The End Section of 'Virtual Column'

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  8axft6m4xxmxg, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE 
DECODE(status,'C',null,status) = 'R'
 
Plan hash value: 2882731673
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE                      |                   |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1                |      1 |    100 |     10 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN                  | T1_STATUS_VIRTUAL |      1 |     40 |     10 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T1"."VIRTUAL_STATUS"='R')
 

21 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 8axft6m4xxmxg, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(other) FROM t1 WHERE
DECODE(status,'C',null,status) = 'R'

Plan hash value: 2882731673

--------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
--------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 2 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 2 \|
\| 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| T1 \| 1 \| 100 \| 10 \|00:00:00.01 \| 2 \|
\|* 3 \| INDEX RANGE SCAN \| T1_STATUS_VIRTUAL \| 1 \| 40 \| 10 \|00:00:00.01 \| 1 \|
--------------------------------------------------------------------------------------------------------------------

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

3 - access("T1"."VIRTUAL_STATUS"='R')

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database