2021 – Virtual Column Invisibility, Errors and Indexes

This post covers continues with the Virtual Column discussion including making them invisible, handling errors in the underlying function and creating indexes on them.

Invisibility

First up is invisibility. Virtual columns are treated as just another column in Data Manipulation Language (DML) statements. For example and INSERT statement like the one below assumes that all columns will be given values.

INSERT INTO json_patient
VALUES('{"pname":"Test"}');
-- Raises error ORA-00947: not enough values

But we don’t want to insert values, we just want to query them. To allow this we flag them as invisible like this.

ALTER TABLE json_patient MODIFY unique_os INVISIBLE;

Now the INSERT works.

INSERT INTO json_patient
VALUES('{"pname":"Test"}');

1 row created

Errors In The Function

The innocent looking insert above exposes another problem with virtual columns. Consider this query.

SELECT unique_os
  FROM json_patient
-- Raises ORA-20001: comma-separated list invalid

That error comes from the function behind the virtual column. It doesn’t handle patients with mrns like the test patient we just created. Lets add this simple check to the function.

IF p_os_list IS NULL THEN RETURN 1; END IF;

And now execute the query successfully.

SELECT JSON_VALUE(patient_data,'$.pname') pname,
        unique_os
  FROM json_patient;
PNAME UNIQUE_OS
----- ---------
Test          1

Indexes

The virtual column can be indexed as well.

CREATE INDEX unique_os_ix ON json_patient ( unique_os );

The index usage can be verified with an explain plan.

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 120 | 236K| 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_PATIENT | 120 | 236K| 14 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | UNIQUE_OS_IX | 48 | | 10 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("UNIQUE_OS"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Thanks for reading!

2 thoughts on “2021 – Virtual Column Invisibility, Errors and Indexes”

  1. Hello Darryl,

    The function OS_LIST_UNIQUE still has a problem, it is not able to correctly identify that an array contains UNIQUE elements
    if those elements are ordered differently:

    BEGIN
    — list is unique so return 1
    DBMS_OUTPUT.PUT_LINE(os_list_unique(‘[1,2,3]’));

    — list is not unique so return 0
    DBMS_OUTPUT.PUT_LINE(os_list_unique(‘[1,4,1]’));

    — list is unique so should return 1, but it returns 0
    DBMS_OUTPUT.PUT_LINE(os_list_unique(‘[3,2,1]’));
    END;
    /

    1
    0
    0

    Thanks a lot & Best Regards,
    Iudith Mentzel

    Reply

Leave a Comment