Gathering Statistics on a Table is Slow When the Table Contains a Virtual Column
Last updated on AUGUST 05, 2016
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 and later
Information in this document applies to any platform.
- A large table with millions of rows contains a virtual column, whose contents are generated by a function call.
- The following is a simple example with a virtual column called TEXT, whose value is determined by a call to a function called SET_TEXT:
1. Here is an example of the function definition (for education purposes only): (Since this is a simple test without rows, the timing difference is not that big. With a large volume of data, the timing could take much longer -- even hours.)
- When DBMS_STATS.GATHER_TABLE_STATS is executed against all columns in this table, it runs very slowly.
- While stats are being gathered, you can see the SET_TEXT function in V$ACCESS.
- If a copy of the same table is created, but without the virtual column, then GATHER_TABLE_STATS completes much more quickly.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms