Gathering Statistics on a Table is Slow When the Table Contains a Virtual Column
(Doc ID 1933412.1)
Last updated on APRIL 12, 2022
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
- 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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document