09 July, 2020

Adding a Function-Based Index

What happens when you create a Function Based Index ?

{This demo is in 12.2}

I start with my "large" table.

SQL> create table my_data_table 
  2  as select * from dba_objects
  3  union all select * from dba_objects;

Table created.

SQL> select count(*) from my_data_table;

  COUNT(*)
----------
    157408

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL> select num_rows, avg_row_len
  2  from user_tables
  3  where table_name = 'MY_DATA_TABLE'
  4  /

  NUM_ROWS AVG_ROW_LEN
---------- -----------
    157408         132

SQL> 


I then add a Function Based Index on it.

SQL> create index my_data_fbi   
  2  on my_data_table(lower(OBJECT_NAME))
  3  /

Index created.

SQL> 


I now look at Table and Column statistics

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL> 
SQL> desc my_data_table
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 OWNER                                                                             VARCHAR2(128)
 OBJECT_NAME                                                                       VARCHAR2(128)
 SUBOBJECT_NAME                                                                    VARCHAR2(128)
 OBJECT_ID                                                                         NUMBER
 DATA_OBJECT_ID                                                                    NUMBER
 OBJECT_TYPE                                                                       VARCHAR2(23)
 CREATED                                                                           DATE
 LAST_DDL_TIME                                                                     DATE
 TIMESTAMP                                                                         VARCHAR2(19)
 STATUS                                                                            VARCHAR2(7)
 TEMPORARY                                                                         VARCHAR2(1)
 GENERATED                                                                         VARCHAR2(1)
 SECONDARY                                                                         VARCHAR2(1)
 NAMESPACE                                                                         NUMBER
 EDITION_NAME                                                                      VARCHAR2(128)
 SHARING                                                                           VARCHAR2(18)
 EDITIONABLE                                                                       VARCHAR2(1)
 ORACLE_MAINTAINED                                                                 VARCHAR2(1)
 APPLICATION                                                                       VARCHAR2(1)
 DEFAULT_COLLATION                                                                 VARCHAR2(100)
 DUPLICATED                                                                        VARCHAR2(1)
 SHARDED                                                                           VARCHAR2(1)
 CREATED_APPID                                                                     NUMBER
 CREATED_VSNID                                                                     NUMBER
 MODIFIED_APPID                                                                    NUMBER
 MODIFIED_VSNID                                                                    NUMBER

SQL> 
SQL> l
  1  select column_name, num_nulls,to_char(last_analyzed,'DD-MON HH24:MI')
  2  from user_tab_columns
  3  where table_name =  'MY_DATA_TABLE'
  4* order by column_id
SQL> /

COLUMN_NAME                    NUM_NULLS  TO_CHAR(LAST_ANALYZED
------------------------------ ---------- ---------------------
OWNER                                   0 09-JUL 22:43
OBJECT_NAME                             0 09-JUL 22:43
SUBOBJECT_NAME                     155954 09-JUL 22:43
OBJECT_ID                               2 09-JUL 22:43
DATA_OBJECT_ID                     139120 09-JUL 22:43
OBJECT_TYPE                             0 09-JUL 22:43
CREATED                                 0 09-JUL 22:43
LAST_DDL_TIME                           2 09-JUL 22:43
TIMESTAMP                               2 09-JUL 22:43
STATUS                                  0 09-JUL 22:43
TEMPORARY                               0 09-JUL 22:43
GENERATED                               0 09-JUL 22:43
SECONDARY                               0 09-JUL 22:43
NAMESPACE                               2 09-JUL 22:43
EDITION_NAME                       157408 09-JUL 22:43
SHARING                                 0 09-JUL 22:43
EDITIONABLE                        101970 09-JUL 22:43
ORACLE_MAINTAINED                       0 09-JUL 22:43
APPLICATION                             0 09-JUL 22:43
DEFAULT_COLLATION                  125494 09-JUL 22:43
DUPLICATED                              0 09-JUL 22:43
SHARDED                                 0 09-JUL 22:43
CREATED_APPID                      157408 09-JUL 22:43
CREATED_VSNID                      157408 09-JUL 22:43
MODIFIED_APPID                     157408 09-JUL 22:43
MODIFIED_VSNID                     157408 09-JUL 22:43

26 rows selected.

SQL> 
SQL> l
  1  select column_name, num_nulls,to_char(last_analyzed,'DD-MON HH24:MI')
  2  from user_tab_cols
  3  where table_name =  'MY_DATA_TABLE'
  4* order by column_id
SQL> /

COLUMN_NAME                    NUM_NULLS  TO_CHAR(LAST_ANALYZED
------------------------------ ---------- ---------------------
OWNER                                   0 09-JUL 22:43
OBJECT_NAME                             0 09-JUL 22:43
SUBOBJECT_NAME                     155954 09-JUL 22:43
OBJECT_ID                               2 09-JUL 22:43
DATA_OBJECT_ID                     139120 09-JUL 22:43
OBJECT_TYPE                             0 09-JUL 22:43
CREATED                                 0 09-JUL 22:43
LAST_DDL_TIME                           2 09-JUL 22:43
TIMESTAMP                               2 09-JUL 22:43
STATUS                                  0 09-JUL 22:43
TEMPORARY                               0 09-JUL 22:43
GENERATED                               0 09-JUL 22:43
SECONDARY                               0 09-JUL 22:43
NAMESPACE                               2 09-JUL 22:43
EDITION_NAME                       157408 09-JUL 22:43
SHARING                                 0 09-JUL 22:43
EDITIONABLE                        101970 09-JUL 22:43
ORACLE_MAINTAINED                       0 09-JUL 22:43
APPLICATION                             0 09-JUL 22:43
DEFAULT_COLLATION                  125494 09-JUL 22:43
DUPLICATED                              0 09-JUL 22:43
SHARDED                                 0 09-JUL 22:43
CREATED_APPID                      157408 09-JUL 22:43
CREATED_VSNID                      157408 09-JUL 22:43
MODIFIED_APPID                     157408 09-JUL 22:43
MODIFIED_VSNID                     157408 09-JUL 22:43
SYS_NC00027$                            0 09-JUL 22:43

27 rows selected.

SQL> 

SQL> l
  1  select column_name, avg_col_len
  2  from user_tab_cols
  3  where table_name = 'MY_DATA_TABLE'
  4* order by column_id
SQL> /

COLUMN_NAME                    AVG_COL_LEN
------------------------------ -----------
OWNER                                    6
OBJECT_NAME                             34
SUBOBJECT_NAME                           2
OBJECT_ID                                5
DATA_OBJECT_ID                           2
OBJECT_TYPE                             10
CREATED                                  8
LAST_DDL_TIME                            8
TIMESTAMP                               20
STATUS                                   7
TEMPORARY                                2
GENERATED                                2
SECONDARY                                2
NAMESPACE                                3
EDITION_NAME                             0
SHARING                                 13
EDITIONABLE                              2
ORACLE_MAINTAINED                        2
APPLICATION                              2
DEFAULT_COLLATION                        4
DUPLICATED                               2
SHARDED                                  2
CREATED_APPID                            0
CREATED_VSNID                            0
MODIFIED_APPID                           0
MODIFIED_VSNID                           0
SYS_NC00027$                            34

27 rows selected.

SQL> 
SQL> select num_rows, avg_row_len
  2  from user_tables
  3  where table_name = 'MY_DATA_TABLE'
  4  /

  NUM_ROWS AVG_ROW_LEN
---------- -----------
    157408         166

SQL> 


A new column "SYS_NC00027$" appears in USER_TAB_COLS but not in USER_TAB_COLUMNS.  The new column is not also not visible when I run a "DESCRIBE" command.

Also, the AVG_ROW_LEN has increased by 34  (seeing as I do not have any objects with long names) to reflect the addition of the new virtual column.
But did all the blocks actually get re-written ?  Are the "lower(OBJECT_NAME)" values written into each table block as well ?

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL> 
SQL> l
  1  select obj#, savtime, rowcnt, blkcnt, avgrln,samplesize,to_char(analyzetime,'DD-MON-RR HH24:MI:SS')
  2  from sys.WRI$_OPTSTAT_TAB_HISTORY
  3  where obj# =
  4  (select object_id
  5   from dba_objects
  6   where owner = 'HEMANT'
  7   and object_type = 'TABLE'
  8   and object_name = 'MY_DATA_TABLE')
  9* order by 1,2
SQL> /

      OBJ# SAVTIME                      ROWCNT     BLKCNT     AVGRLN     SAMPLESIZE TO_CHAR(ANALYZETIME,'DD-MON
---------- ---------------------------- ---------- ---------- ---------- ---------- ---------------------------
     79843 09-JUL-20 10.39.03.789392 PM     157408       3106        132     157408 09-JUL-20 22:37:53
     79843 09-JUL-20 10.43.59.424420 PM     157408       3106        132     157408 09-JUL-20 22:39:03
     79843 09-JUL-20 11.02.35.088733 PM     157408       3106        166     157408 09-JUL-20 22:43:59

SQL> 


Apparently, the actual number of blocks did not increase.

SQL> l
  1  select segment_name, segment_type, bytes/1024
  2  from user_segments
  3  where segment_name like 'MY_DATA%'
  4* order by 1,2
SQL> /

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024
------------------------------ ------------------ ----------
MY_DATA_FBI                    INDEX                    8192
MY_DATA_TABLE                  TABLE                   25600

SQL> 
SQL> 
SQL> l
  1  select index_name, column_name
  2  from user_ind_columns
  3* where index_name = 'MY_DATA_FBI'
SQL> /

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
MY_DATA_FBI                    SYS_NC00027$

SQL> 


So, the Index does take up space as an Index segment but the Virtual Column ("lower(OBJECT_NAME)")  created on the Table does not grow the table.  What seems to happen is that the "computed" value ("lower(OBJECT_NAME)") is stored in the Index segment (leaf blocks) but not in the Table segment (blocks)


What happens if I drop the Function Based Index ?

SQL> drop index my_data_fbi;

Index dropped.

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*)
  2  from user_tab_columns
  3  where table_name = 'MY_DATA_TABLE'
  4  /

  COUNT(*)
----------
        26

SQL> select count(*)
  2  from user_tab_cols
  3  where table_name = 'MY_DATA_TABLE'
  4  /

  COUNT(*)
----------
        26

SQL> select avg_row_len
  2  from user_tables
  3  where table_name = 'MY_DATA_TABLE'
  4  /

AVG_ROW_LEN
-----------
        132

SQL> 


The additional column and the 34 bytes it represents has disappeared from the Table and Column statistics.



No comments: