Here's another Function Based Index example. This is in 19.3 (not to demonstrate any difference between 12.2 and 19.3 here)
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 :
(SYS_C007674 is the Primary Key Index on EMPLOYEE_ID)
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)