What happens when you create a Function Based Index ?
{This demo is in 12.2}
I start with my "large" table.
I then add a Function Based Index on it.
I now look at Table and Column statistics
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 ?
Apparently, the actual number of blocks did not increase.
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 ?
The additional column and the 34 bytes it represents has disappeared from the Table and Column statistics.
{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:
Post a Comment