A Function Based Index : 2

Here's another Function Based Index example.   This is in 19.3  (not to demonstrate any difference between  12.2 and 19.3 here)

SQL> create table employees
  2  (employee_id number primary key,
  3  first_name varchar2(30),
  4  last_name varchar2(30))
  5  pctfree 0
  6  /

Table created.

SQL>SQL> insert into employees
...
...

10000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES');

PL/SQL procedure successfully completed.

SQL>
SQL> select sample_size, blocks, empty_blocks, avg_row_len
  2  from user_tab_statistics
  3  where table_name  = 'EMPLOYEES'
  4  /

SAMPLE_SIZE     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
----------- ---------- ------------ -----------
      10000         65            0          45

SQL>
SQL> select 45*10000/8192 from dual;

45*10000/8192
-------------
   54.9316406

SQL>
SQL> select column_name, avg_col_len
  2  from user_tab_cols
  3  where table_name = 'EMPLOYEES'
  4  order by column_id
  5  /

COLUMN_NAME                    AVG_COL_LEN
------------------------------ -----------
EMPLOYEE_ID                              4
FIRST_NAME                              19
LAST_NAME                               22

SQL>
SQL> create index employees_full_name_ndx
  2  on employees(first_name||last_name)
  3  /

Index created.

SQL>
SQL> select column_name, avg_col_len
  2  from user_tab_cols
  3  where table_name = 'EMPLOYEES'
  4  order by column_id
  5  /

COLUMN_NAME                    AVG_COL_LEN
------------------------------ -----------
EMPLOYEE_ID                              4
FIRST_NAME                              19
LAST_NAME                               22
SYS_NC00004$

SQL>
SQL> -- no statistics on the new virtual column yet
SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES');

PL/SQL procedure successfully completed.

SQL> select column_name, avg_col_len
  2  from user_tab_cols
  3  where table_name = 'EMPLOYEES'
  4  order by column_id --- for the hidden/virtual column actually use "internal_column_id"
  5  /

COLUMN_NAME                    AVG_COL_LEN
------------------------------ -----------
EMPLOYEE_ID                              4
FIRST_NAME                              19
LAST_NAME                               22
SYS_NC00004$                            40

SQL>
SQL> select data_type, data_length,data_default,column_id,internal_column_id, hidden_column,virtual_column
  2  from user_tab_cols
  3  where table_name = 'EMPLOYEES'
  4* and column_name = 'SYS_NC00004$'
SQL> /

DATA_TYPE        DATA_LENGTH DATA_DEFAULT                COLUMN_ID INTERNAL_COLUMN_ID HID VIR
---------------- ----------- -------------------------- ---------- ------------------ --- ---
VARCHAR2                  60 "FIRST_NAME"||"LAST_NAME"                              4 YES YES

SQL>
SQL> select num_rows, sample_size, blocks, empty_blocks, avg_row_len
  2  from user_tab_statistics
  3  where table_name  = 'EMPLOYEES'
  4  /

  NUM_ROWS SAMPLE_SIZE     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- ----------- ---------- ------------ -----------
     10000       10000         65            0          85

SQL>
SQL> select 85*10000/8192 from dual;

85*10000/8192
-------------
   103.759766

SQL>
SQL> select leaf_blocks
  2  from user_ind_statistics
  3  where index_name = 'EMPLOYEES_FULL_NAME_NDX'
  4  /

LEAF_BLOCKS
-----------
         72

SQL>

After creation of the Function Based Index, the new virtual column in the table has an average length of 40 bytes.  Surely, the table should now have taken up more blocks with the average row length increasing from 45 to 85 ?  The number of table blocks should have increased by about 50 or more blocks ? And, yet, the table still consumes only 65 blocks.  It is the Index that reports 72 leaf blocks.

So, adding a Function Based Index increases the reported "AVG_ROW_LEN" for the Table without actually increasing the space consumed by the table.  The computed values are not stored in the Table blocks but are actually only in the Index leaf blocks.

Conversely, this can also mean that when you come across a table with a low BLOCKS versus AVG_ROW_LEN*NUM_ROWS, you might want to look for a Function Based Index :

SQL> select index_name, index_type, uniqueness
  2  from user_indexes
  3  where table_name =  'EMPLOYEES'
  4  /

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
SYS_C007674                    NORMAL                      UNIQUE
EMPLOYEES_FULL_NAME_NDX        FUNCTION-BASED NORMAL       NONUNIQUE

SQL> select column_position, column_expression
  2  from user_ind_expressions
  3  where table_name =  'EMPLOYEES'
  4  and index_name = 'EMPLOYEES_FULL_NAME_NDX'
  5  /

COLUMN_POSITION COLUMN_EXPRESSION
--------------- ------------------------------
              1 "FIRST_NAME"||"LAST_NAME"

SQL>

(SYS_C007674 is the Primary Key Index on EMPLOYEE_ID)